Create and run an Excel macro from within Access

[dbelsey]

I have Office Pro 2003 and am trying to get Access and Excel to play nice but having little luck. In Access I'm using VBA and TransferSpreadsheet to create a new Excel spreadsheet. Now I want to run an Excel macro against that new spreadsheet. Since the spreadsheet created has no macros in it, it appears to me I have to do one of the following:

EITHER
Create the Excel macro (from scratch so to speak) within Access, plop that new macro into the new spreadsheet and then run it (from Access)

OR
FROM ACCESS run an already-written Excel macro (contained in a separate spreadsheet, completely unrelated to the one just created) against the new spreadsheet.

Since I want Access to do this with multiple spreadsheets, I'd prefer the second method above since the new macro wouldn't have to be created multiple times. I just don't know how to do it. Can anyone help?

 

[SkipVought]

Hi,

If you are doing this from Access VBA, you do not need a separate macro for each sheet, IF you reference your objects properly in order to reference any sheet you care to reference.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dbelsey]

Thanks for your quick reply Skip. Your "IF" is a pretty big if. While I'm comfortable with Access VBA for a standalone database, I'm NOT comfortable referencing Excel objects from within Access.

I think I need to have open my new spreadsheet and also the spreadsheet containing the macro, and I somehow have to be able to get that macro to run against the new spreadsheet. It's the somehow I'm hazy on. Can you cite code samples that do this?

Thanks again!

Dave

 

[SkipVought]

After you transfer the data.

Use the CreateObject method to create an Excel application object.

Then OPEN the workbook and manipulate the Excel objects...

dim xl as Excel

set xl = CreateObject("Excel.Application")

with xl.workbooks.open([i]YourPath&Workbookname[/i])
  with .Sheets(1)
    'now work on the objects in the sheet

  end with
  .Save
  .Close
end with

set xl = nothing

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dbelsey]

Thanks again, Skip. I got that far, but at the point where you say 'now work on... I have to reference a macro in a totally unrelated spreadsheet (one that's not open yet). That's where I'm lost. How do I do that?

Sorry to be such a pain.

Dave

 

[SkipVought]

Well then you would have to open THAT workbook. Do you need multiple workbooks open at the same time?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dbelsey]

Skip-

Darned if I know. I DO know that I'll have two spreadsheets, one (call it XL_1) just created with TransferSpreadsheet (which has no macros) and another (call it XL_2) that has the macro. I want to run XL_2's macro against spreadsheet XL_1. I've seen this done (and it worked flawlessly), but unfortunately I didn't have the common sense to copy the Access VBA code. I think both were open, but I'm not sure. I'll try playing some more with it, but my inexperience with Access AND Excel is really frustrating. Thanks again!

Dave

 

[tman138]

Hey db.
Here's what I did that works well. Create a template which contains your macros. Run a prep function similar to this:
Function fnPrepSheet1()
Dim templateXLS As String
Dim Msg1 As String
Dim pathout As String
Dim fileout As String

Dim strExcelPath As String

templateXLS = "c:\hold\MyTemplate.xlt"

pathout = "c:\Hold\"
fileout = "MySheet.xls"

gFileName = pathout & fileout
FileCopy templateXLS, pathout & fileout

End Function

This will copy the template into an empty workbook that you will then populate using your transfer spreadsheet command:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "YourTableorQuery", "YourWorkbookLocation", False

By the way you can populate multiple sheets in the same workbook by running TransferSpreadsheet more than once.

Hope this helps