Help debugging code to automatically save workbook on server

javajoe

Board Regular
Joined
Nov 7, 2005
Messages
78
Hey folks - I have the following code (thanks to some previous posts) but when I run this code, it runs without any errors but doesn't actually save the file in the folder I specified. The path is correct so I'm not sure what's going on. Any help would be appreciated!

<code>
Private Sub SaveOnServerButton_Click()
On Error Resume Next

Dim strInitPath As String
Dim strFileName As String

'rename file
strInitPath = "\\SERVER01\Company\eCommissions" & "\" & ActiveSheet.Name & ".xls"
strFileName = Application.GetSaveAsFilename(strInitPath, "Microsoft Excel Workbook (*.xls), *.xls")

'if cancel selected exit without saving
If strFileName = False Then Exit Sub
ActiveWorkbook.SaveAs (strFileName)
End Sub
</code>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You aren't getting an error because the error is supressed by the "On Error Resume Next". However, the error that is being generated is because strFileName is a string variable not a boolean variable. Therefore you need to check strFileName for being "False" not False. Otherwise a type mismatch error will occur.
When an error is generated with "On Error Resume Next" invoked, your code will automatically fall though to the next line of code. It doesn't look like it, but the next line of code is actually "Exit Sub". So everytime this code runs, it is generating an error you don't see and falling through to the "exit sub". Put quotes around your False and that should fix it :-)
<hr>
<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> SaveOnServerButton_Click()
On <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strInitPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'rename file</SPAN>
strInitPath = "\\SERVER01\Company\eCommissions" & "\" & ActiveSheet.Name & ".xls"
strFileName = Application.GetSaveAsFilename(strInitPath, "Microsoft Excel Workbook (*.xls), *.xls")
<SPAN style="color:#007F00">'if cancel selected exit without saving</SPAN>
<SPAN style="color:#00007F">If</SPAN> strFileName = <SPAN style="color:#FF0000">"</SPAN>False<SPAN style="color:#FF0000">"</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
ActiveWorkbook.SaveAs strFileName
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,648
Members
452,344
Latest member
LarryRSch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top