Jump to content
Macro Express Forums

Ascii File Process Command And "open" (?) File


cuppanews
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...