Saving an Excel worksheet to a specified folder

[bxgti4x4]

I receive an Excel worksheet by email every day. I open this file, print it and then save it to a folder "N:\VBR\Current Month" with the file name being the date of receipt.

I can use "GetSaveAsFilename" but this requires me to specify the file name. I would be most grateful if someone could assist with a code that will open the "File SaveAs" dialog box with the required folder shown.

Thanks a lot
John

 

[PHV]

Take a look at the Application.GetSaveAsFilename method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

This has been answered previously many tims.

Please search the forums for save and excel and you will find plenty of examples.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[bxgti4x4]

PHV,

I looked at that method but could not see how it would work without the full file name, which is what I don't need. A little more help would be much appreciated.

Best Regards
John

 

[RoyVidar]

I think you can pass only the directory

[tt]dim strCurr as string
strCurr = curdir()
strFilename = application.getsaveasfilename("c:\test\")
if strFilename = "" or strFileName = "False" then
' cancelled or no selection
else
' perform intersting stuff
end if

chdir strCurr ' reset to original path if changed[/tt]

I think it also resets the current directory, if a selection is done, the above should "keep" the current directory.

Roy-Vidar

 

[bxgti4x4]

Roy,

I think you are right, I ran the code on a blank workbook and the correct folder opened. However, when I then gave the file a name and pressed OK, nothing happened. I then opened the file I wanted to save from within outlook and ran the macro. It came up with a message saying that "book 1" cannot be found.

Seems strange that you can do the same thing in Word but not in Excel. Are we stuck, or is there a way forward?

Best Regards
John

 

[bxgti4x4]

Roy,

I took a look at the Show method again and came up with the following code:

Dim dlg As Dialog
Set dlg = Application.Dialogs(xlDialogSaveAs)
With dlg
.Name = "N:\Access\VBR\Current Month\"
.Show
End With

If I just run the "Application.Dialogs(xlDialogSaveAs).show"
the dialog opens. However, if I then try to add the name function, I get a run time error message saying "object doesn't support this property or method".

I can't see why this method works in Word but not in Excel. Could you please see if there is a way around this.

Thanks a lot,
John

 

[PHV]

Why not using the Application.GetSaveAsFilename method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,

I tried this earlier on as you suggested and replied to you on Friday afternoon. Roy Vidar replied on Friday as well, with an idea which I also tried. This got me as far as the Save As dialog box but when I entered a file name, nothing happened. This was when I tried the "Show" method, which had solved my problem in Word.

I have been unable to find a way that clicking on OK in the Save As dialog box will carry out the save action, rather than just closing the box.

Best Regards
John

 

[PHV]

Any chance you could post the code you tried ?
Have you even read the VBA help about this method ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

MHV,

Here is the code:

Dim strCurr As String
strCurr = CurDir()

strFilename = Application.GetSaveAsFilename("N:\Access\VBR\Current Month")
If strFilename = "" Or strFilename = "False" Then
MsgBox "Save as" & fileSaveName
End If

I have looked everywhere that I can think of in the VBA help but can find nothing to get me beyond this point. Any advice would be much appreciated.

Best Regards
John

 

[PHV]

With the cursor inside GetSaveAsFilename in your VBA code press the F1 key.
strFilename = Application.GetSaveAsFilename("N:\Access\VBR\Current Month")
If strFilename <> False Then
MsgBox "Save as" & strFilename
ActiveWorkbook.SaveAs strFilename
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[bxgti4x4]

PHV,

The code worked fine the first time. Then, when I closed Excel and re-opened it, I ran the macro again and it tried to find the file under which I had previously saved the workbook. As long as that file existed, it would save the current workbook under the new file name I specified but it opened the original file as well. As soon as I deleted the original file the macro stalled because it could not fine that file.

I have followed the help exactly but I cannot see why the code is doing this.

Best regards
John