Filename as a parameter causing run-time 1004 error

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:36 PM GMT

Question

I've inherited the following code, which fails in Sub 'Save_File' at the line asterixed. The error is Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed

Can you help at all?

I can't see where FileName is being set in this code at all but I know it used to work!

Sub CACI_Generate_All_Junit_Files()

Dim WorkbookName As String WorkbookName = Application.ActiveWorkbook.FullName Dim LastRow As Long Dim i As Integer

LastRow = Sheets("Test Case index").Range("A65536").End(xlUp).Row

For i = 1 To LastRow

   Call Run_Test_Case(WorkbookName, i)
   Call Save_Input_File(i, "D:TaxCalcInput\" & i)
   Call Save_Interim_File(i, "D:TaxCalcInterim\" & i)
   Call Save_Output_File(i, "D:TaxCalcOutput\" & i)
   Next i

End Sub

Sub Generate_Input_Files()

Dim WorkbookName As String WorkbookName = Application.ActiveWorkbook.FullName

For i = 1 To LastRow

   Call Run_Test_Case(WorkbookName, i)
   Call Save_Input_File(i, "D:TaxCalcInput\" & i)
   Next i

End Sub

Sub Generate_Interim_Files()

Dim WorkbookName As String WorkbookName = Application.ActiveWorkbook.FullName

For i = 1 To LastRow

   Call Run_Test_Case(WorkbookName, i)
   Call Save_Interim_File("D:TaxCalcInterim\" & i)
   Next i

End Sub

Sub Generate_Output_Files()

Dim WorkbookName As String WorkbookName = Application.ActiveWorkbook.FullName

For i = 1 To LastRow

   Call Run_Test_Case(WorkbookName, i)
   Call Save_Output_File(i, "D:TaxCalcOutput\" & i)
   Next i

End Sub

Sub Run_Test_Case(WorkbookName, TestCaseNumber)

   Sheets("Test Case index").Select
   Range("C3").Select
   ActiveCell.FormulaR1C1 = TestCaseNumber
   Range("D3").Select
   Application.Run "'" & WorkbookName & "'!Replay_test_case"

End Sub

Sub Save_Input_File(TestCaseNumber, FileName) ' Input Test Case

   Call Save_File(TestCaseNumber, "TaxCalc_Input_JUNIT-2", FileName)

End Sub

Sub Save_Interim_File(TestCaseNumber, FileName) ' Interim Test Case

   Call Save_File(TestCaseNumber, "TaxCalc_InterimCalcs_JUNIT-1", FileName)

End Sub

Sub Save_Output_File(TestCaseNumber, FileName) ' Output Test Case

   Call Save_File(TestCaseNumber, "TaxCalc_FinalOutput_JUNIT-1", FileName)

End Sub

Sub Save_File(TestCaseNumber, Sheet, FileName)

   Sheets(Sheet).Select
   Application.CutCopyMode = False
   Sheets(Sheet).Copy
   Sheets(Sheet).Select
   Sheets(Sheet).Name = TestCaseNumber
   Application.DisplayAlerts = False
            • ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=6
   ActiveWindow.Close
   Application.DisplayAlerts = True

End Sub

Answer

You have four routines that call routines that call Save_File. Each of the routines that call Save_File receives a filename in their argument list and passes that name to Save_File in the argument list to the variable Filename.

Here is some of the code: Call Save_Input_File(i, "D:TaxCalcInput\" & i) Call Save_Interim_File(i, "D:TaxCalcInterim\" & i) Call Save_Output_File(i, "D:TaxCalcOutput\" & i) but similar code is found in your generate sub routines.


If I were going to change something, I would change D:TaxCalc...\

to

D:\TaxCalc...\

so that the colon is followed by a left leaning slash.

That is about all I saw that was wrong with the code.

Advertisement

©2021 eLuminary LLC. All rights reserved.