Macro - send a file to a specific location

**Bob1955** · 12-27-2006, 04:07 PM

I am creating a workbook of multiple worksheets. Each worksheet needs to be manipulated by different people away from the workbook and then returned to the workbook with new data.

In the past I have done this with a macro that saves the worksheet as a new file to a floppy disk in "a:/: drive because of the consistent nomenclature.

However, floppy disks are now defunct.

I need the macro to go to send the worksheet to a new specified location.

If there any way of having a user specify where to send the file eg to a data stick on f:/?

**VBA Noob** · 12-27-2006, 05:00 PM

Hi,

This code might help. It saves the activesheet to a drive. Change the path as required.

Please Login or Register  to view this content.

VBA Noob

**Bob1955** · 12-27-2006, 05:44 PM

That's great. It enables user to input file name. Is there any way they can also input the drive and folder location. Eg. memory sticks in USB ports can be anything from d to z?

**VBA Noob** · 12-27-2006, 06:27 PM

Hi,

This is the best I can give you. Allows you to name the file and pick the drive. Drive code from John Walkenback site

http://j-walk.com/ss/excel/tips/tip29.htm

Please Login or Register  to view this content.

VBA Noob

**Bob1955** · 12-27-2006, 07:22 PM

Thank you for your help. You have spent much more time than I expected with me.

When I load the code and run the macro I get the following error message.

Compile error.
Cannot name a Public User defined type within an object module.

I there something I have done/not done. I checked out the website you mentioned and added the first line that was missing but the same message appeared.

Thank you

Bob

**Bryan Hessey** · 12-27-2006, 08:50 PM

Originally Posted by Bob1955

Thank you for your help. You have spent much more time than I expected with me.

When I load the code and run the macro I get the following error message.

Compile error.
Cannot name a Public User defined type within an object module.

I there something I have done/not done. I checked out the website you mentioned and added the first line that was missing but the same message appeared.

Thank you

Bob

Hi,

which Code sheet did you put the code into?

---added,

from the site:
The GetDirectory function
The GetDirectory function is listed below. To use this function, copy the text and paste it to a VBA module. isn't too specific about the word MODULE which is probably where you need to put that, not in a Sheet code.