CODEWITHSUNDEEP
excelvba
user3457548
user3457548

Reputation: 61

VBA how do i open another workbook?

I am trying to open another workbook, using the code below 

Sheets("Range").Activate
   Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Workbooks.Open ("AvgStdev.xlsm")

And it was working before, but now excel is prompting the file cant be found. Help please :/

Upvotes: 4

Views: 74998

Answers (2)

L42
L42

Reputation: 19727

You can do what you want easily if you declare your variable as discussed HERE.
So if we are to apply it, you can open your workbook like this:

    Dim wb As Workbook
    Dim myfilename As String

    myfilename = "C:\Users\Ayaz\Desktop\Analysis\AvgStdev.xlsm"
    '~~> open the workbook and pass it to workbook object variable
    Set wb = Workbooks.Open(myfilename) 

    '~~> More codes here

Now later in your code if you are saving the same file:

    wb.Save '~~> save
    wb.Close '~~> close

Or you can do it using Close method only:

    wb.Close True '~~> explicit SaveChanges argument to true

Now if however you like to save it as another file:

    Dim newfilename As String
    newfilename = "C:\Users\Ayaz\Desktop\Analysis\Another.xlsm"
    '~~> If you are saving it in a format other than .xlsx,
    '~~> you have to be explicit in the FileFormat argument
    wb.SaveAs newfilename, xlOpenXMLWorkbookMacroEnabled
    wb.Close

HTH.

Upvotes: 11

tmit
tmit

Reputation: 83

Right click on your excel file and obtain the filepath from the properties, then substitute the actual filepath into the below:

Workbooks.Open ("filepath\AvgStdev.xlsm")

Upvotes: 2

Related Questions