VBA SaveAs "Run-time error 1004 method 'saveas' of object '_workbook' failed"

CJBills

New Member
Joined
Apr 27, 2015
Messages
13
I want to save an excel file.

-The file’s name and folders’s names will change each time a file is saved.
-I recorded a macro to save the current file, then added variables defined by InputBoxes so a user could name and save each file to the correct folder.</SPAN>

I get the error message when using the variables. I'm not seeing what I'm doing incorrectly, or if I've used too many variables, or if the script can be edited further to include additional functions which would strengthen it.

Thanks in advance!

Code:
Dim WkMnth As String</SPAN>
Dim WkNumber As Integer</SPAN>
Dim WkFull As String</SPAN>
 
WkMnth = InputBox(Prompt:="What is the MONTH?, Title:="Week Month")</SPAN>
WkNumber = InputBox(Prompt:="What is the WEEK?, Title:="Week Number")</SPAN>
WkFull = WkMnth & " Week " & WkNum</SPAN>
 
Dim WeekYr As Integer</SPAN>
Dim xSystem As String</SPAN>
 
WeekYr = InputBox(Prompt:="What is the year?, Title:="Name the Year")</SPAN>
xSystem = InputBox(Prompt:="What is the System?", Title:="Name of System")</SPAN>
 
Dim WkMonthNum As Integer</SPAN>
 
   If WkMonth = "Jan" Then</SPAN>
      WkMonthNum = "01"</SPAN>
   ElseIf WkMonth = "Feb" Then</SPAN>
     WkMonthNum = "02"</SPAN>
  End If</SPAN>

ActiveWorkbook.SaveAs FileName:="Z:\ParentFolder\SubFolderNameOne " & WeekYr & " " & WkMonthNum & _</SPAN>
"\SubFolderNameTwo " & xSystem & " " & WeekYr & " " & WkMonthNum & _</SPAN>
"\FileName Complete_" & xSystem & " " & WeekYr & " " & WkMonth & " Week " & WkNumber & ".xlsx" _</SPAN>
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

QUESTION
Is there a limit to the number of variables (DIM) set?</SPAN>
When saving files to folders whose names for each change, does using variables (DIM) cause issues? Can the issue be resolved?</SPAN>
 
Last edited:

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
You have made an error that we all make when using variables.

WkMnth is not the same as WkMonth
 

CJBills

New Member
Joined
Apr 27, 2015
Messages
13
Thanks. Spelling correction made. The Run-time error highlights the ActiveWorkbook.SaveAs part of the script. In theory, this should work. Variables A to A, B to B, C to C should return the same results always, correct?
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,216
Office Version
  1. 365
Platform
  1. Windows
The 1004 error usually means that the system cant find the folder/folders you are asking it to put the file into. Verify that the folders exist.
 

CJBills

New Member
Joined
Apr 27, 2015
Messages
13
Ok. I'm using variables to save the file to the correct folder, based on the input of the user via InputBoxes. I'll check my naming application of the variables.

Curious: Does using variables (when used correctly) to define folders and files in a saveas code cause these kind of errors as well?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,190
Messages
5,594,761
Members
413,931
Latest member
acrato

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
Top