Run Excel Macro with a string

[thegameoflife]

Is there a way to run an Excel macro and pass a string to the macro? I have the code to run the macro but I can't pass a string to excel.

 

[VBslammer]

Need more info...are you automating Excel? VBSlammer

Unemployed in Houston, Texas

 

[thegameoflife]

I'm trying to export a spreadsheet and then call a macro to format and save the sheet.

I have all the code in Excel to format a sheet but when I call the macro from Access the macro does not know what sheet to format. One other problem, the user may or may not have Excel open and be working on other sheets.


The reason why I wanted to pass the string to Excel. If I could pass the name of the file that Access just created then I could have the Excel macro open that file.

 

[thegameoflife]

I got it.......

  Sub XLTest(exlpath)
  

  Dim TemplateFile As String
  Dim XLApp As Object


    TemplateFile = "C:\Recoveries\AIM_HRI.xls"
    On Error Resume Next 'ignore error if Excel not running
    Set XLApp = GetObject("Excel.Application") 'Check for Excel

    If Err <> 0 Then
        Set XLApp = CreateObject(&quot;Excel.Application&quot;) 'Open Excel
    End If

    XLApp.Visible = True 'Make Excel Visible
    XLApp.Workbooks.Open TemplateFile 'Open the spreadsheet
    
    'XLApp.Workbooks.Open exlpath
    XLApp.ActiveWorkbook.Application.Run &quot;AIMHRI&quot;, exlpath 'run excel macro and PASS the string
        
  End Sub