Automation:Selecting a different worksheet from another excel work

[mattieflo]

Hello,

I have written the following code:

Dim appExcel as Excel.Application
Dim xlwb as Excel.Workbook
Set appExcel = New Excel.Application
Set xlwb =
appExcel.Workbooks.Open("\\F0862P1\share\compliance\orderedbed\issues
log.xls")
intTrackingNumber = xlwb.Worksheets(5).Range("A2").Value
xlwb.Worksheets(3).Range("A1").Select

Currently, the active worksheet in issues log.xls is the first worksheet. I
need it to select the 3 worksheet but it errors out on the last line of code
listed above giving me the "Select Method of range class failed.". If I go
into the issues log.xls workbook and save it when the 3rd worksheet is the
active sheet, the above code works fine. Does anyone have an idea what im
doing wrong?

Thanks!

 

[Dave Peterson]

You can only select a range on a worksheet that is selected.
You can only select a worksheet if the workbook is active.

You could use
xlwb.activate
xlwb.worksheets(3).select
xlwb.worksheets(3).range("a1").select

or

appexcel.goto xlwb.worksheets(3).range("a1") ', scroll:=true 'or false

But for most things, you don't need to select the range to work with it.

xlwb.worksheets(3).range("a1").value = "hi"

 

[mattieflo]

Thanks Dave,

appexcel.goto xlwb.worksheets(3).range("a1") ', scroll:=true 'or false

Worked like a charm.