Referencing an External Workbook

Hi All

I am having a little difficulty in correctly formatting a macro statement which I want to use to insert a formula into a specific cell. The source cell will change on a weekly basis but will be consistently filed and formatted.

This is the statement I am trying to use

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Weekly!$I$37"

where myPath is the full location of the RemoteFile and Weekly is the Sheet Name

I have tried all manner of combinations of "s and 's but I either come up with an error message or I get the text of the formula in the target cell.

Incidentally, is it possible to refer to the sheet by Index number, rather than by name?

Thanks

Robert

Re: Referencing an External Workbook

Thanks Roy

In the example I posted :

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Weekly!$I$37"

given that "Weekly" is Sheet 1, would the format be

ActiveCell.Offset(0, 2).Formula = "= myPath & " [RemoteFile.xls ] Sheets(1)!$I$37"?

Robert

Re: Referencing an External Workbook

Thank you both for your help. Whenever I try to replace the sheet name with 'Sheet1' I get an error, either a "compile error Expected: end of statement" or Object required if I stick a ! in.

Whilst the version with the full sheet name works perfectly, I would really like to use the sheet number as people seem to have the habit of, very subtly, changing the names on the sheets of their workbooks, e.g. an extra space or the omission of a period.

Thanks again for your interest

Robert

Re: Referencing an External Workbook

Hi Roy

the workbook I am trying to access has 12 sheets, each of which is named, however, for the reasons mentioned earlier, I would like to access them by their index. If I had the workbook in question open and active I could refer to the sheets by "Sheets(1)" and this is what I want to do when it is inactive and closed. There is not a sheet tabbed "Sheet1"

Apologies for the confusion

Robert

Re: Referencing an External Workbook

Hi Roy

Sorry, missed the point. Yes, there is a Sheet1 in the target workbook

Robert