Newbie question on accessing data in xls

[Ernesto]

I have a series of manipulations in VBA.
I need to run this on data which is in ms-excel files I receive daily.
These files have multiple sheets (of different names) but the VBA applies to
them all the same.

How can I have the VBA access the cells in another ms-excel file?

I tried for example:
Worksheets(SheetName).Rows(1).Cells(1).Value

where I'd haveopen both teh xls files (one with data and the other with the
code),
and set the SheetName to the name of the target sheet.

But I get a "subscript out of range" error, which probably means the
sheetName was not found.

What is the best way of reading or accessing data on other xls sheets ?

Thanks in adv.
Tim

 

[marty]

I hope you'll forgive the question but in your VBA code
example you did not put the sheetname in quotes. Did you
put the sheetname in quotes?

Marty

 

[Mark Thorpe]

Hi Ernesto -

Using Worksheets(SheetName) assumes that the worksheet is within the
currently-active workbook. If you're working with macros in one Excel file
(aka workbook), and data in another, you'll have to specify the workbook as
well as the worksheet:

Try:

Workbooks("DataSpreadsheet.xls").Worksheets(SheetName).Cells(1,1).Value

 

[Ernesto]

Thanks that trick worked.