Is it possible?

[Lazarus416]

I use the "ActiveWorkbook.SaveAs Filename:=" code to auto save my workbook to a share drive whenever I run a macro. However, I'd like to export my macro out so that other people can use it and since they all save their docs to different places on the share drive, I was wondering if there is a way to make the macro prompt for the saving location every time the macro is run instead of including the file path in the script?

Alternatively, is it possible to make a macro that you run ONCE asking for a save/file path and have that one time only macro modify the VBA code in the primary one to reflect the desired path?

Thanks in advance,

Laz

 

[mole999]

ENVIRONS() can give you various paths that are in the system

as your file might share the same names, maybe you should get people to save archives to their local desktop, then copy them over at end of day

OR

you put a path statement onto a sheet and use that

 

[Lazarus416]

as your file might share the same names, maybe you should get people to save archives to their local desktop, then copy them over at end of day

The whole point behind the macro is to let people open up a downloaded, raw (mostly unreadable) CSV document, reformat it into something usable and auto save into the same place with the same name every time. It minimizes any possible confusion and/or mistakes when linking to the new spreadsheet. (These are not shared copies btw, each user will have their own that refers to their own unit.)

you put a path statement onto a sheet and use that

Not sure what you mean by this? Do you mean put the path into a separate cell/sheet and then have the VBA code refer to that cell? Is that even possible with the auto-save code?

 

[mole999]

last bit yes, everybody will have their own path, and that text cab be used

if they delete it, then create an alternate that is hard coded as a back up

 

[Lazarus416]

Cool, I appreciate the info. I didn't know a cell could be referenced for the auto save. That's probably the easiest way to do it for people with questionable Excel or computer skills.