save to file dependent on cell

Hi, I have included an example below which checks the C: drive for the folder name you enter in cell A1 and then saves the file as 'filename.xls'. Of course, you can change the path and filename to whatever you want.

Please Login or Register  to view this content.

By the way, you need to add a reference to the 'Microsoft Scripting Runtime' in VBA for this to work.

Hope this helps.

Originally Posted by sdts

hi treacle
im getting an error with this line

Please Login or Register  to view this content.

As I said in my previous post, you have to add a reference to the Scripting Runtime DLL in order to use the FileSystemObject. To do this, in VBA go to the 'Tools' menu and select 'Reference...', then scroll down to 'Microsoft Scripting Runtime' and tick it then click Ok.

It should work fine then. (Crosses fingers)

That's ok

The 'Me' keyword is a quick way of referencing the object where the code is located. I placed my code on the sheet with the cell containing the folder name so the 'Me' keyword can be used.

If your code is anywhere else (in a separate code module maybe), then you need to replace 'Me' with an explict reference to the workbook and sheet. Assuming the sheet you want to pick up the folder name from is called "Sheet1", I have modified the code below:

Please Login or Register  to view this content.