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
|