Exporting a macro to Excel 1

[jmstarbuck]

I'm "exporting" a query to Excel with the following:

 Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Set xlApp = Excel.Application
    Set xlBook = xlApp.Workbooks.Add
    xlApp.DisplayAlerts = False
    For i = xlBook.Worksheets.Count To 2 Step -1
        xlBook.Worksheets(1).Delete
    Next i
    xlApp.DisplayAlerts = True
     
    Set xlSheet = xlBook.ActiveSheet
    xlSheet.Name = "ProgramReport"
    With xlSheet
        
        'copy data from recordset to excel
        .Range("A1").CopyFromRecordset rst
...

That works fine. Now I would like to also export a macro to that workbook that my users can run later. My thought is to have a large block of text somewhere in Access that is actually an Excel macro and then do something nifty like ".addmacro LgBlkText"

I'm not quite sure how to say this correctly, which is probably why I am having trouble finding an answer with a search.

Any ideas?

Janice

 

[PHV]

Why not simply use an Excel template (.xlt)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jmstarbuck]

HI PH,

I'm not familiar with templates in Excel. I just read a bit about them and it is not clear to me how to accomplish what I want to do.

I want to maintain the Excel macro code solely in the Access database.

If I'm understanding the template idea I think
I would need to reference an external template file created in advance when I create the Excel file I am exporting. Is that true?

Please elaborate on how I could use the template idea and how I could implement it. Can I create a template in Access?

Best,
J

J

 

[PHV]

I'd have an Excel template with a single sheet (ProgramReport) and the necessary macro.
Then:

Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Set xlBook = xlApp.Workbooks.Add("\path\to\template.xlt")
With xlBook.Sheets("ProgramReport")
  .Range("A1").CopyFromRecordset rst
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886