Referencing worksheet in another workbook

[jbfluor]

hi all,

Ok i think this should be a fairly simple question for you lot.

I need to know how to reference another workbook

ie WorkbookA - SheetA = WorkbookB - SheetB so far i have this:

([WorkbooknameA.xls]. Worksheets ("WorksheetnameA")=([WorkbooknameB.xls]. Worksheets ("WorksheetnameB")

I hope i have made this clear as to what im looking for here.Heres my actual code

LastRow = Workbooks("Prelim 26 Sept 2006.xls").Worksheets("Prelim 19 Sept 2006").UsedRange.Rows.Count

 

[Harvey]

the format is
[workbookname]sheetname!A1:B1

workbookname can be anything from "Workbook2" (even an unsaved workbook works) to 'C:\test.xls', if the workbook is already open you don't have to add the .xls but you can use the name shown in the title bar instead.

 

[jbfluor]

thats what i thought it was but its coming up with a syntax error.

I have tried:

LastRow = [Prelim 19 Sept 2006.xls]"Prelim 19 Sept 2006"!UsedRange.Rows.Count

and

LastRow = [Prelim 19 Sept 2006.xls]Prelim 19 Sept 2006!UsedRange.Rows.Count

and neither works

 

[Darren Bartrup]

Hi jbfluor

Try:

LastRow = Workbooks("Book4").Worksheets("Sheet1").Cells. _
          Find(What:="*", _
          Searchdirection:=xlPrevious, _
          searchorder:=xlByRows).Row

Edit: Not sure, but this may only work if the workbook is open.
It should return the last row containing data.

 

[Harvey]

thats what i thought it was but its coming up with a syntax error.

I have tried:

LastRow = [Prelim 19 Sept 2006.xls]"Prelim 19 Sept 2006"!UsedRange.Rows.Count

and

LastRow = [Prelim 19 Sept 2006.xls]Prelim 19 Sept 2006!UsedRange.Rows.Count

and neither works

what you are doing right here is combining a worksheet formula with VBA code. If you want to use it in your VBA, use:

LastRow = Workbooks("Prelim 19 Sept 2006.xls").Sheets("Prelim 19 Sept 2006").UsedRange.Rows.Count

 

[jbfluor]

Thanks for both your suggestions

Delmar yours seems to work on th efirst bit of my code but then not on the next bits

Harvey - Yours is more along the line si was looking at but when i compile it it highlights yellow and says "runtime error 9 - Subscript out of range"

I cant understand why, any ideas?

 

[Harvey]

It usually means either the workbook or the worksheet does not exist (ie. the name is typed wrong)

 

[jbfluor]

I just changed them all and tried it again and no joy. I have no idea whats going on but its annoying me and im sure it will be something so simple aswell.

Would it help if i put the whole path in for the work book and put active sheet for the sheet as theres only one sheet in the workbook in question??? and what would the code be?

Workbooks (H:\250_Pipe\253 - Material Control\Mat'l Tracking\James MTO Report\Weeks1-2). Activeworksheet. Cells (CurrentPos, "A")

 

[Harvey]

it is also possible to use workbooks(1) and workbooks(2), but you have to be sure that you are using the right workbook.

something that can also help is printing the workbook name and then copying it between the parentheses:
debug.print activeworkbook.name
say it says: myfile.xls
then you type:
workbooks("myfile.xls").activate
and execute to see what happens. If no error, put it into the rest of the code and it should work.

 

[jbfluor]

there does appear to be a problem with the file names but i cant understand it i have checke dthem even renamed them to w1 and w2 and it still wont have it? Have no idea whats wrong now. Thanks for your help though