I have a list of workbooks and I want to get some data off of the first
worksheet. I plan to open the file and use an indirect formula similar to
what I'm showing below:

Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]SHEET1'!"""
Range("I" & i).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1]&""$B$33"")"


UNFORTUNATELY, the first worksheet has different names in each workbook.
It's not SHEET1. How would I identify the sheetname and include it in my
formula?

Thanks,
Barb Reinhardt

sName = Activeworkbook.Worksheets(1).Name

or if your linking to a different workbook

sName = "'[" & activeworkbook.name & "]" & activeworkbook.worksheets(1).Name
& "'!R1C1"



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have a list of workbooks and I want to get some data off of the first
worksheet. I plan to open the file and use an indirect formula similar to
what I'm showing below:

Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]SHEET1'!"""
Range("I" & i).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1]&""$B$33"")"


UNFORTUNATELY, the first worksheet has different names in each workbook.
It's not SHEET1. How would I identify the sheetname and include it in my
formula?

Thanks,
Barb Reinhardt

I have this now:

Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
= False)
aWB.Activate
aWS.Activate
sName = oWB.Worksheets(1).Name

Cells(i, "H").FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]""&sName&""'!"""

I get an error because it doesn't know what sName is. I have dimensioned
sName as a string.

What next?

Thanks,
Barb Reinhardt

"Tom Ogilvy" wrote:

sName = Activeworkbook.Worksheets(1).Name

or if your linking to a different workbook

sName = "'[" & activeworkbook.name & "]" & activeworkbook.worksheets(1).Name
& "'!R1C1"



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have a list of workbooks and I want to get some data off of the first
worksheet. I plan to open the file and use an indirect formula similar to
what I'm showing below:

Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]SHEET1'!"""
Range("I" & i).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1]&""$B$33"")"


UNFORTUNATELY, the first worksheet has different names in each workbook.
It's not SHEET1. How would I identify the sheetname and include it in my
formula?

Thanks,
Barb Reinhardt

The indirect formula will break when you close the sending workbook.

Why not just retrieve the value?

somesheet.range("H" & i).value = oWB.worksheets(1).range("B33").value

or

owb.worksheets(1).range("b33").copy _
destination:=somesheet.range("h" & i)

========
If you were building a formula to just retrieve that value (after the workbook
is closed), you could do something like:

somesheet.range("H" & i).formula _
= "=" & owb.worksheets(1).range("b33").address(external:=t rue)

========

But I'm not sure what you're doing...



Barb Reinhardt wrote:

I have this now:

Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
= False)
aWB.Activate
aWS.Activate
sName = oWB.Worksheets(1).Name

Cells(i, "H").FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]""&sName&""'!"""

I get an error because it doesn't know what sName is. I have dimensioned
sName as a string.

What next?

Thanks,
Barb Reinhardt

"Tom Ogilvy" wrote:

sName = Activeworkbook.Worksheets(1).Name

or if your linking to a different workbook

sName = "'[" & activeworkbook.name & "]" & activeworkbook.worksheets(1).Name
& "'!R1C1"



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have a list of workbooks and I want to get some data off of the first
worksheet. I plan to open the file and use an indirect formula similar to
what I'm showing below:

Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]SHEET1'!"""
Range("I" & i).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1]&""$B$33"")"


UNFORTUNATELY, the first worksheet has different names in each workbook.
It's not SHEET1. How would I identify the sheetname and include it in my
formula?

Thanks,
Barb Reinhardt


--

Dave Peterson

Or paste|special|Values

(whatever you need???)

Dave Peterson wrote:

The indirect formula will break when you close the sending workbook.

Why not just retrieve the value?

somesheet.range("H" & i).value = oWB.worksheets(1).range("B33").value

or

owb.worksheets(1).range("b33").copy _
destination:=somesheet.range("h" & i)

========
If you were building a formula to just retrieve that value (after the workbook
is closed), you could do something like:

somesheet.range("H" & i).formula _
= "=" & owb.worksheets(1).range("b33").address(external:=t rue)

========

But I'm not sure what you're doing...

Barb Reinhardt wrote:

I have this now:

Set oWB = Workbooks.Open(Cells(i, "B"), ReadOnly = True, UpdateLinks
= False)
aWB.Activate
aWS.Activate
sName = oWB.Worksheets(1).Name

Cells(i, "H").FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]""&sName&""'!"""

I get an error because it doesn't know what sName is. I have dimensioned
sName as a string.

What next?

Thanks,
Barb Reinhardt

"Tom Ogilvy" wrote:

sName = Activeworkbook.Worksheets(1).Name

or if your linking to a different workbook

sName = "'[" & activeworkbook.name & "]" & activeworkbook.worksheets(1).Name
& "'!R1C1"



--
Regards,
Tom Ogilvy


"Barb Reinhardt" wrote:

I have a list of workbooks and I want to get some data off of the first
worksheet. I plan to open the file and use an indirect formula similar to
what I'm showing below:

Range("H" & i).Select
ActiveCell.FormulaR1C1 = "=""'""&RC1&""\[""&RC7&""]SHEET1'!"""
Range("I" & i).Select
ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1]&""$B$33"")"


UNFORTUNATELY, the first worksheet has different names in each workbook.
It's not SHEET1. How would I identify the sheetname and include it in my
formula?

Thanks,
Barb Reinhardt


--

Dave Peterson

--

Dave Peterson