0

I am trying to write a macro in VBA that saves the original workbook as a macro free workbook and then re-open the original macro enabled file, but ehat I have written is bugging out when it tries to open code (which is the last line of code). The error I get is a dialogue box that states that the file is unable to be found, however the file path provided is correct. Is the issue that I am trying to finish running the macro, while the workbook that is open is a generic Excel workbook (not macro enabled)? Code is below:

Sub SaveReport()

' SaveReport Macro

    Dim strFileName As String, strOpen As String

    strFileName = Application.Range("Attach").Value
    strOpen = Application.Range("Open").Value

    ActiveWorkbook.Save
    Sheets("Email").Visible = False
    ActiveWorkbook.SaveAs (ThisWorkbook.Path & "\" & strFileName & ".xlsx"), FileFormat:=xlOpenXMLWorkbook
    Workbooks.Open strOpen
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
NACwench
  • 1
  • 1
  • 1
    Wouldn't your task be much simpler if you were to use `SaveCopyAs` instead of `SaveAs`? https://learn.microsoft.com/en-us/office/vba/api/Excel.Workbook.SaveCopyAs – Variatus Mar 31 '21 at 02:08
  • https://stackoverflow.com/questions/18899824/how-to-use-vba-saveas-without-closing-calling-workbook – Siddharth Rout Mar 31 '21 at 02:42

0 Answers0