VBA: Save a file and open a new file

sonyaltec

New Member
Joined
Feb 10, 2023
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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")
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I'm guessing, but maybe something like this:
VBA Code:
    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.)
 
Upvote 0
I'm guessing, but maybe something like this:
VBA Code:
    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.
 
Upvote 0
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.
VBA Code:
    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
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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