[TC[_3_]]

(JMCN) wrote in message . com...
hello

i am trying to open an excel worksheet and excute a macro. i am able
to launch excel but i cannot execute the macro(s). why? what did i
leave out? should i create a getobject function? suggestions would be
appreciated:)
thanks jung

here is the code.

Public Sub LaunchExcel()
Dim appExcel As Excel.Application, strwks As String
Set appExcel = CreateObject("Excel.Application")
Msg "Excel is running"
appExcel.Visible = True
strwks = "c:\Program Files\Microsoft Office\Office\"
strwks = strwks & "macro-test.xls"
appExcel.ExecuteExcel4Macro "Macro1"
Set appExcel = Nothing
End Sub


You put the worksheet filename into strwks, but then, you don't do
anything with that variable! Excell will not see it by magic. So
Excell has no idea what worksheet you're talking about.

HTH,
TC

[Myrna Larson[_2_]]

Also, he didn't open a workbook -- either the one specified in strwks or any other workbook.


On 13 Jul 2003 19:18:00 -0700, (TC) wrote:

(JMCN) wrote in message
. com...
hello

i am trying to open an excel worksheet and excute a macro. i am able
to launch excel but i cannot execute the macro(s). why? what did i
leave out? should i create a getobject function? suggestions would be
appreciated:)
thanks jung

here is the code.

Public Sub LaunchExcel()
Dim appExcel As Excel.Application, strwks As String
Set appExcel = CreateObject("Excel.Application")
Msg "Excel is running"
appExcel.Visible = True
strwks = "c:\Program Files\Microsoft Office\Office\"
strwks = strwks & "macro-test.xls"
appExcel.ExecuteExcel4Macro "Macro1"
Set appExcel = Nothing
End Sub


You put the worksheet filename into strwks, but then, you don't do
anything with that variable! Excell will not see it by magic. So
Excell has no idea what worksheet you're talking about.

HTH,
TC

[JMCN]

(TC) wrote in message . com...
(JMCN) wrote in message . com...
(TC) wrote in message . com...
(JMCN) wrote in message . com...
hello

i am trying to open an excel worksheet and excute a macro. i am able
to launch excel but i cannot execute the macro(s). why? what did i
leave out? should i create a getobject function? suggestions would be
appreciated:)
thanks jung

here is the code.

Public Sub LaunchExcel()
Dim appExcel As Excel.Application, strwks As String
Set appExcel = CreateObject("Excel.Application")
Msg "Excel is running"
appExcel.Visible = True
strwks = "c:\Program Files\Microsoft Office\Office\"
strwks = strwks & "macro-test.xls"
appExcel.ExecuteExcel4Macro "Macro1"
Set appExcel = Nothing
End Sub


You put the worksheet filename into strwks, but then, you don't do
anything with that variable! Excell will not see it by magic. So
Excell has no idea what worksheet you're talking about.

HTH,
TC


thanks you all for your suggestions!!!

i was able to open up the excel worksheet and run the code perfectly
fine.

i have another question. is it possible to select different files in
the
"C:\temp" folder? one solution would be to create an input box where
i could type in the different files to open up, ie. C:\Temp\Arizona
Macro.xls???

ie. strDatabase = InputBox("Please enter Database ")

Private Sub btnRunMacro_Click()

Dim objXL As Object, x
On Error Resume Next
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True

'open the workbook
.workbooks.Open "C:\Temp\Denver Macro.xls" 'possible to change
it?
.ActiveWorkbook.RunAutoMacros 1

End With
Set objXL = Nothing

End Sub

okay, thanks again, jung


Hi jung

Glad you got it going.

Sure, you could enter the worksheet filename into the input box, then
use that value to open the worksheet. Personally, I would change the
word "database" to "worksheet" in your variable name & inputbox
prompt, because you're really talking about an Excell worksheet - not
an Access database.

So, change:

strDatabase = InputBox("Please enter Database ")
...
.workbooks.Open "C:\Temp\Denver Macro.xls"

to:

strWorksheet = InputBox("Please enter Worksheet")
...
.workbooks.Open strWorksheet

then the user would enter "C:\Temp\Denver Macro.xls" (without the
quote marks) into the input box.

If the files in question were *always* in C:\Temp, you could say:

strWorksheet = InputBox("Please enter Worksheet")
...
.workbooks.Open "C:\Temp\" & strWorksheet

then the user would only have to enter "Denver Macro.xls" into the
inputbox.

If, in addition, the worksheets were always named like "... Maro.xls",
you could say:

strWorksheet = InputBox("Please enter Worksheet")
...
.workbooks.Open "C:\Temp\" & strWorksheet & " Macro.xls"

and the user would only have to enter "Denver" into the inputbox!

HTH,
TC

hello tc,

the following code worked like a charm! thanks again for your help!!

jung