4

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 :/

user3457548
  • 61
  • 1
  • 2
  • 8
  • 3
    `Workbooks.Open ("AvgStdev.xlsm")` - use full path: `Workbooks.Open ("C:\temp\AvgStdev.xlsm")` – Dmitry Pavliv Jun 28 '14 at 19:49
  • @simoco Post this as an answer ;) – Barranka Jun 28 '14 at 20:02
  • Thanks ! quick thing i also need to save and close it but this code isnt working: Workbooks("C:\Users\Ayaz\Desktop\Analysis\AvgStdev.xlsm").Save Workbooks("C:\Users\Ayaz\Desktop\Analysis\AvgStdev.xlsm").Close – user3457548 Jun 28 '14 at 20:12

2 Answers2

10

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.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
2

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")
tmit
  • 83
  • 6