How to activate a workbook in a different directory?

[DarrellK]

Why does the following code produce a "subscript out of range error"?

Workbooks("C:\Darrell\Cash Flow Reports\recovered jan 2006\2006 Jan-Sep
CAPEX Loadsheet Actuals.xls").Worksheets("Worksheet Totals").Activate

To assist with your answer to this question:
The code is written in a file in the directory C:\Darrell\Cash Flow
Reports\TestVB.
The path and file name were copied directly from explorer.
If I place this file in the same directory as the file which contains the
code and do not use the directory path "C:\Darrell\Cash Flow
Reports\recovered jan 2006\", I get no error.

I would appreciate any insight you may have to solving my problem.

Thanks.

 

[Michael Bauer [MVP - Outlook]]

That has nothign to do with the path of the file.

You can access a file with Workbooks('name') only if the file is already
opened. In that case 'name' is the file's name without its path.

If it's not opened yet you must open it first with Workbooks.Open(...). In
that case it's ok to pass only the name if the workbook is in the same
directory, else you'd have to pass the path & filename.

BTW: For Excel questions it's better to visit an Excel related newsgroup
like microsoft.public.excel.programming

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Fri, 26 Jan 2007 16:13:44 -0800 schrieb DarrellK:

 

[DarrellK]

Hi Michael,
Thanks for your help. And next time I will go to the Excel related
discussion group. However, can we finish off this problem here?

I did as you recommended and opened the file first with the line:
Workbooks.Open ("C:\Darrell\Cash Flow Reports\recovered jan 2006\2006
Jan-Sep CAPEX Loadsheet Actuals.xls")

But then when I use Vlookup to look up a value in a sheet within the
workbook with the following code I get an "Application-defined or
object-defined runtime error". Can you tell me what is now wrong?

Cells(382, 1) = Application.WorksheetFunction.VLookup("X28.01",
Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet
Totals").Range("F:CO"), 11 + j - 15, False)

Thank you again for your help.

Darrell

 

[Michael Bauer [MVP - Outlook]]

First I'd recommend to use more variables. Set one for the workbook, then
one for the worksheet, then one for the range. In one of that line fails
then you will know what it is.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)

Am Mon, 29 Jan 2007 15:26:00 -0800 schrieb DarrellK:

 

[DarrellK]

I am not sure if this is what you mean, but I created 3 variables:
tempwb = "2006 Jan-Sep CAPEX Loadsheet Actuals.xls"
tempwsh = "Worksheet Totals"
temprg = Cells(1, 6)
and replaced each reference in the following with these variables, but it
made no difference. I still get the same error ("Application-defined or
object-defined runtime error"). Note that I am now not even using the vlookup
function, but simply trying to assign a value to a cell to make the debuggin
easier.

Cells(382, 1) = Workbooks("2006 Jan-Sep CAPEX Loadsheet
Actuals.xls").Worksheets("Worksheet Totals").Cells(1, 6)

Note that I have opened the file I want to read values from (2006 Jan-Sep
CAPEX Loadsheet Actuals.xls) with the command
Workbooks.Open ("C:\Darrell\Cash Flow Reports\TestVB\2006 Jan-Sep CAPEX
Loadsheet Actuals.xls")

and I have activated the file into which I want these values placed with the
comand:
Workbooks("2006 Jan-Oct CAPEX Loadsheet Actuals.xls").Worksheets("Worksheet
Totals").Activate

So that values in the "Sep" file get placed in the "Oct" file.

Again, thank you for your assistance. Also, please let me know if you would
rather that I continue with these questions in the Excel discussion group
since you may prefer to spend your time answering questions in your specific
discussion group.

Darrell

 

[Michael Bauer [MVP - Outlook]]

No, that's not wha I meant Please place your cursor within your code
and call 'QuickInfo' from the context menu. It tells you something about the
method, variable or whatever the cursor is placed into. Play with it, e.g.
call QuickInfo for the Open function of Workbooks. It then tells you what
data type is returned (after the closing bracket), that you should store in
variable of the proper type.

In your sampel you have a workbook, a worksheet and a range:

Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim rn as Excel.Range

' First set a ref to the workbook. If it fails you will get an error and
know where to look for it...
Set wb=Workbooks("2006 Jan-Sep CAPEX Loadsheet Actuals.xls")

' ... and do so for the other objects
Set ws=wb.Sheets("Worksheet Totals")
Set rn=ws.Range("F:CO")

I don't know the VLookup function but I assume that you don't want to pass
the Range, ie. the object itself, to it, but its content. See please write
that:

Cells(382, 1).Value = WorksheetFunction.VLookup("X28.01", rn.Value, 11 + j -
15, False)

If that doesn't solve the problem then the passed values probably are wrong.
For instance, if VLookup expects a numeric value in the second parameter and
you pass "abc" to it then that could cause an error. Or maybe (11 + j - 15)
is less than 0, which might be not allowed.

--
Viele Gruesse / Best regards
Michael Bauer - MVP Outlook
Keep your Outlook categories organized!
http://www.shareit.com/product.html?productid=300120654&languageid=1
(German: http://www.VBOffice.net/product.html?pub=6)


Am Tue, 30 Jan 2007 11:21:11 -0800 schrieb DarrellK: