cuppanews Posted July 26, 2005 Report Share Posted July 26, 2005 I'm using a CSV file with the ASCII File Process command for a lookup table. Everything is working really well so far, with one wrinkle. I'm adding new rows to the table in Excel every day, and I often have it open while I'm using the ME macro that processes the data. When the file's open in Excel, the macro fails. I'm puzzled by this. Why is the file not available to other applications while it's being edited? Obviously the saved version on the disk is the only one the macro can see, but if I save after every change in Excel, I'd have thought that ME would be able to use it even though it was still open in Excel. Is that not the case? Thanks. Quote Link to comment Share on other sites More sharing options...
randallc Posted July 26, 2005 Report Share Posted July 26, 2005 Hi, No, I believe that is expected behaviour for Excel spreadsheets; locks out other use while open. Maybe a "save" macro that makes a copy as well with another name; which does not stay open? Best, Randall. PS I did not know you could process spreadsheets with "csv" process, anyway! - I thought you had to "saveAs" csv; are you able to post the snippet of how you do that? ThanksIA. Quote Link to comment Share on other sites More sharing options...
Cory Posted July 26, 2005 Report Share Posted July 26, 2005 This is by design. Excel locks all open spreadsheets. If I were you I would create a macro to run while you're in Excel to save as a different name periodically on on command. IOW keep a working copy and the macro's copy. Also you could write a maro in Excel and attach it to that file to do the same thing. Randall: Excel can edit CSV files directly and I assume this is what he is doing. Quote Link to comment Share on other sites More sharing options...
Cory Posted July 26, 2005 Report Share Posted July 26, 2005 I forgot... In the macro add an "If file ready" before it starts to process with a pause that says "Close the file you idiot!". Then you have an oportunity to close it and continue the macro. Or better yet do the same "If" above but only open a text box, not a pause, with the same message. Then put in a "Wait for file ready" followed by a "Close text box". This saves you an extra click and as soon as you close the spreadsheed in Excel it will continue. Quote Link to comment Share on other sites More sharing options...
cuppanews Posted July 26, 2005 Author Report Share Posted July 26, 2005 I did not know you could process spreadsheets with "csv" process, anyway! - I thought you had to "saveAs" csv; are you able to post the snippet of how you do that? Thanks, Randall. Yes, it is a "saved as" CSV file maintained in Excel and not a native Excel spreadsheet. Quote Link to comment Share on other sites More sharing options...
cuppanews Posted July 26, 2005 Author Report Share Posted July 26, 2005 This is by design. Excel locks all open spreadsheets. If I were you I would create a macro to run while you're in Excel to save as a different name periodically on on command. IOW keep a working copy and the macro's copy. Also you could write a maro in Excel and attach it to that file to do the same thing. Randall: Excel can edit CSV files directly and I assume this is what he is doing. Thanks. I guess it makes sense that Excel would behave this way. Interesting. Your suggestion of a macro to get around this is good. I'll give it a shot. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.