CODEWITHSUNDEEP
excelvba
Xavi
Xavi

Reputation: 207

save and close a workbook with its existing name and reopen it with VBA

I have my VBA code below, I would like that my macro saves and closes on mydesktop my activework with its existing name, then I would like that my macro reopens it. When I execute my macro, it saves and closes my activeworkbook with its existing name on my desktop but it does not reopen the excel file. It seems that the part of the code Workbooks.Open ("\\C:\users\ing\users3\Xavi\Desktop\" & ActiveWorkbook.Name) does not work in my macro.

If someone know the solution, that would be great. Many thanks Xavi

Sub openandsaveactiveworkbookandreopenit ()
Dim myactiveworkbook As Workbook
Set myactiveworkbook = ActiveWorkbook
ActiveWorkbook.SaveAs FileName:="\\xxxyyyy\users\ing\users3\Xavi\Desktop\" & ActiveWorkbook.Name _
 , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWorkbook.Close

Workbooks.Open ("\\xxxyyyy\users\ing\users3\Xavi\Desktop\" & ActiveWorkbook.Name)

 End Sub

Upvotes: 0

Views: 2035

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

  • You can't run this macro from the ActiveWorkbook -- it needs to run from a different workbook. See @FoxfireAndBurnsAndBurns comment.

  • You've declared (good practice) myactiveworkbook but you never use it!

  • Once you've closed ActiveWorkbook, you can no longer access it's Name property because it doesn't exist. Save that property in a string variable.

Try this variation, with the macro in a workbook that is not the Active Workbook. It works OK here.

Option Explicit

Sub openandsaveactiveworkbookandreopenit()
Dim myactiveworkbook As Workbook
Dim myactiveworkbookname As String

Set myactiveworkbook = ActiveWorkbook

myactiveworkbook.SaveAs Filename:="c:\users\ron\desktop\" & ActiveWorkbook.Name _
 , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 myactiveworkbookname = myactiveworkbook.Name

myactiveworkbook.Close

Workbooks.Open ("c:\users\ron\Desktop\" & myactiveworkbookname)
 End Sub

Upvotes: 3

Related Questions