Excel Macro Help
 

I am trying to create a macro that allows me to open up another excel
file and then specify which tab on the opened work book I want to pull
information from.

Does anyone know how to do this?

Thanks in advance for your help.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

vVal = Activeworkbooks.Worksheets("Sheet9").Range("B10"). Value

--
Regards,
Tom Ogilvy

"Oli Oshiz" wrote in message
...
I am trying to 9a macro that allows me to open up another excel
file and then specify which tab on the opened work book I want to pull
information from.

Does anyone know how to do this?

Thanks in advance for your help.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

Whoops, typo:
ActiveWorkbooks should be ActiveWorkbook

--
Regards,
Tom Ogilvy

"Oli Oshiz" wrote in message
...
I am trying to create a macro that allows me to open up another excel
file and then specify which tab on the opened work book I want to pull
information from.

Does anyone know how to do this?

Thanks in advance for your help.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

Thanks Tom,

Here is what I end up with but it does not work. What am i doing wrong.

Sub ProjectMacro()
'
' ProjectMacro Macro
' Macro recorded 8/5/2004 by Oli_Oshiz
'

'
Dim sName As String
Dim sh As Worksheet
Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
vVal = Activeworkbooks.Worksheets("Sheet9").Range("B10"). Value
Workbooks.Open myFile
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").Select
ActiveCell.FormulaR1C1 = "='[sName]sh'!R4C4"
Range("B2").Select
ActiveCell.FormulaR1C1 = "='[& sName &_]D0023'!R6C4"
Range("C2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C10"
Range("D2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C14"
Range("E2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C11"
Range("F2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C4"
Range("G2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C11"
Range("H2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C14"
Range("I2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C14"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

It looks like you have thrown a bunch of stuff together without
understanding what you are doing.

You have an active sheet, then open another workbook. Do you then want to
put formulas on the original sheet (reference is held in object sh) that
refers to the newly opened workbook? If so, see below, but it is unclear
if you want to dynamically determine a sheet in that workbook or it is
always going to be a sheet named D0023. If not D0023, then is it the first
sheet in that workbook. If not, then how to determine which sheet?


Dim sName As String
Dim sh As Worksheet
Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
Workbooks.Open myFile
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").FormulaR1C1 = "='[" & sName & "]D0023'!R4C4"

' more of the same

Range(Range("A2"), Range("A2").End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

--
Regards,
Tom Ogilvy


"Oli Oshiz" wrote in message
...
Thanks Tom,

Here is what I end up with but it does not work. What am i doing wrong.

Sub ProjectMacro()
'
' ProjectMacro Macro
' Macro recorded 8/5/2004 by Oli_Oshiz
'

'
Dim sName As String
Dim sh As Worksheet
Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
vVal = Activeworkbooks.Worksheets("Sheet9").Range("B10"). Value
Workbooks.Open myFile
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").Select
ActiveCell.FormulaR1C1 = "='[sName]sh'!R4C4"
Range("B2").Select
ActiveCell.FormulaR1C1 = "='[& sName &_]D0023'!R6C4"
Range("C2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C10"
Range("D2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R6C14"
Range("E2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C11"
Range("F2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C4"
Range("G2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C11"
Range("H2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R8C14"
Range("I2").Select
ActiveCell.FormulaR1C1 = "='[IE MMAR 6-04.xls]D0023'!R7C14"
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

Hi Tom,

I do have an active sheet, that opens another workbook. I then want to
put formulas on the original sheet that refers to the newly opened
workbook? If possible, determine the sheet in the opened workbook.

Thanks.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

If it the opened workbook only has one sheet, then you can determine the
name with

Dim myFile As String
Set sh = ActiveSheet
myFile = Application.GetOpenFilename("Excel Files, *.xls")
Workbooks.Open myFile
sShName = ActiveWorkbook.Worksheets(1).Name
sName = ActiveWorkbook.Name
sh.Parent.Activate
sh.Activate

Range("A2").FormulaR1C1 = "='[" & sName & "]" & sShName & _
"'!R4C4"

' more of the same

Range(Range("A2"), Range("A2").End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub

--
Regards,
Tom Ogilvy

"Oli Oshiz" wrote in message
...
Hi Tom,

I do have an active sheet, that opens another workbook. I then want to
put formulas on the original sheet that refers to the newly opened
workbook? If possible, determine the sheet in the opened workbook.

Thanks.






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Excel Macro Help
 

Hi Tom,

The excel file has multiple sheets with data in multiple cells (however,
the cell locations stay the same for all sheets).

Thanks.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!