access 97 to excel 97: execute macro automation
|
access 97 to excel 97: execute macro automation
(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 |
All times are GMT +1. The time now is 12:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com