CODEWITHSUNDEEP
vbaexcel
Shrikant Salunke
Shrikant Salunke

Reputation: 81

Open, save and close any file

I am writing a simple macro which will open, save and close an Excel file say myworkbook.xlsx but I am unable to do this. My file myworkbook.xlsx is at location:

C:\Users\Administrator\Desktop\Temp\myworkbook.xlsx

I have written code as 

Sub openSaveClose()
  Workbooks.Open "C:\Users\Administrator\Desktop\Temp\files\myworkbook.xlsx" // This is working fine
  Workbooks("C:\Users\Administrator\Desktop\Temp\myworkbook.xlsx").Save 
End sub

The error is:

Run time error 9. Subscript out of range

Upvotes: 3

Views: 6452

Answers (1)

David
David

Reputation: 1232

You only need to specify the path when opening the file:

Sub openSaveClose()
  Workbooks.Open "C:\Users\Administrator\Desktop\Temp\files\myworkbook.xlsx"
  Workbooks("myworkbook.xlsx").Save
  Workbooks("myworkbook.xlsx").Close
End sub

Using an object variable, you can do the following (then no need to use the workbook's name in the Save/Close statements):

Sub openSaveClose()
Dim wb as Workbook
Set wb = Workbooks.Open("C:\Users\Administrator\Desktop\Temp\files\myworkbook.xlsx")
  wb.Save
  wb.Close
End sub

Upvotes: 7

Related Questions