[RESOLVED] Browse for workbook, set path as variable

[bheltzel]

I have an activex button that browses for a workbook and sets the path as a string (myFilePath).

In another sub I dim DD as worksheet, and try to set it as myFilePath like so:

Set DD = Workbooks(myFilePath).Worksheets("2012")

Any idea why this does not work and/or how I can make it work?

Let me know if this isnt clear. Thanks!

[vbfbryce]

What exactly is stored in the "myFilePath" variable?

From MS:

"Use Workbooks(index), where index is the workbook name or index number, to return a single Workbook object."

[bheltzel]

I actually figured it out. thank you though.

[sirjoem]

hi bheltzel
I have exactly the same problem... can you share how you solved this?
thanks!
Sir

[Zvoni]

You have to use the Add-method of the Workbooks-Collection first to add the Workbook you're trying to access.
Or you can use the Open-Method of the Workbook-Object.

[sirjoem]

You have to use the Add-method of the Workbooks-Collection first to add the Workbook you're trying to access.
Or you can use the Open-Method of the Workbook-Object.

Is there a method to do this without opening the file? I am using this for several files inside an IF cycle... moreover the files are on a network folder...


By the way, I tried this:

 Workbooks.Add (Path)
 Set wb = Workbooks(Path).Worksheets(SheetName)

but I get "runtime error '9' - subscript out of range", which is the same error I got before adding "workbooks.add"

what am I missing?

[Zvoni]

Workbooks(Path) is wrong.
You have to use either the numerical Index of your Workbook, or the Name of your Workbook.

Try the following:

For Each wb in Workbooks
Debug.Print wb.name
Next

You'll see what i mean

EDIT: The Add-Method is BullS***. Sorry.
The Add-Method is for adding a new blank Workbook based on a template.
The Open-Method is the Correct one

[sirjoem]

Workbooks(Path) is wrong.
You have to use either the numerical Index of your Workbook, or the Name of your Workbook.

this works now! thanks!


Sir