VBA: Save a file and open a new file

[sonyaltec]

Please help rectify my code - looking to run a code to split my files into multiple different copies, managed to save the file but can't open the original file.

Dim strName As String
Dim FileName As String
Const Path As String = "C:\Users\PathName\"
strName = ActiveWorkbook.FullName
Application.DisplayAlerts = False
FileName = Path & "as of " & Format(Now() - 1, "dd mmmm yyyy") & " - A" & ".xlsx"
ActiveWorkbook.SaveAs FileName, xlOpenXMLWorkbook
Application.DisplayAlerts = True
ActiveWorkbook.Close

Workbooks.Open("C:\Users\PathName\Spare.xlsm")

 

[rlv01]

I'm guessing, but maybe something like this:

    Dim strName As String
    Dim FileName As String
    Dim SaveWB As Workbook
    Const Path As String = "C:\Users\PathName\"
    
    strName = "C:\Users\PathName\Temp$.xlsm"
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs (strName)
    Set SaveWB = Application.Workbooks.Open(FileName:=strName)
    
    FileName = Path & "as of " & Format(Now() - 1, "dd mmmm yyyy") & " - A" & ".xlsx"
    SaveWB.SaveAs FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    SaveWB.Close False
    Kill strName

(not tested).


(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

 

[sonyaltec]

I'm guessing, but maybe something like this:

    Dim strName As String
    Dim FileName As String
    Dim SaveWB As Workbook
    Const Path As String = "C:\Users\PathName\"
   
    strName = "C:\Users\PathName\Temp$.xlsm"
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveCopyAs (strName)
    Set SaveWB = Application.Workbooks.Open(FileName:=strName)
   
    FileName = Path & "as of " & Format(Now() - 1, "dd mmmm yyyy") & " - A" & ".xlsx"
    SaveWB.SaveAs FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    SaveWB.Close False
    Kill strName

(not tested).


(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

as it makes the code easier to read.)

Apologies, will take note on code tags moving forward. Tried the code it is now able to perform its task close the old macro file but it does not save the processed file.

 

[rlv01]

If that is the case, then I think you need to explain in more detail which file is which, and which one gets copied. Here is some debug code that may help you identify where the problem lies.

    Dim strName As String
    Dim FileName As String
    Dim SaveWB As Workbook
    Dim S As String
    
    Const Path As String = "C:\Users\PathName\"
    
    strName = "C:\Users\PathName\Temp$.xlsm"
    FileName = Path & "as of " & Format(Now() - 1, "dd mmmm yyyy") & " - A" & ".xlsx"
    
    S = "This is the name of the processed file containing the source data to be copied:" & vbCr
    S = S & "   '" & ActiveWorkbook.FullName & "'" & vbCr & vbCr
    
    S = S & "This is the name of the new file the data will be saved to:" & vbCr
    S = S & "   '" & FileName & "'" & vbCr & vbCr & "Continue?"
    
    Select Case MsgBox(S, vbYesNo Or vbQuestion, Application.Name)
        Case vbNo
            Exit Sub
    End Select
    
    Application.DisplayAlerts = False
    On Error Resume Next
      ActiveWorkbook.SaveCopyAs (strName)
      Set SaveWB = Application.Workbooks.Open(FileName:=strName)
    On Error GoTo 0
    
    If SaveWB Is Nothing Then
        S = "Cannot find temporary file :" & vbCr
        S = S & "   '" & strName & "'" & vbCr & vbCr
        MsgBox S
        Exit Sub
    End If
    SaveWB.SaveAs FileName, xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    SaveWB.Close False
    Kill strName