Programmatically selecting first worksheet
 

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

Programmatically selecting first worksheet
 

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

Programmatically selecting first worksheet
 

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

Programmatically selecting first worksheet
 

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

Programmatically selecting first worksheet
 

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