Controlling Excel workbook from Access 2010 VBA

Question

I have a situation very similar to the following post:

Access query to excel 2010 to create a graph via vba

In my case, I export a table, but I want to do a lot more to the Excel file.

First I want to rename the tab on the first sheet of the Excel file, since it exports with the funky table name. So, instead of "tblThisIsMyTable", I'd like to change it to "MyTable as of (add a date variable here)"

Secondly, I need to create a Pivot table and make a copy of the Pivot table on another tab, to change one of the parameters.

My question is, can I record a macro in Excel to do the basic steps and drop that code into my Access VBA?

I hope I was clear enough and thanks for any help you can provide.

David Zemens David Zemens · Accepted Answer · 2013-04-11T20:21:18

My question is, can I record a macro in Excel to do the basic steps and drop that code into my Access VBA?

Yes, absolutely.

From Access, you may need to add a reference to the Microsoft Excel library.

You will then need to make some changes, to the "recorded" macro so that it creates a new instance of Excel.Application, and opens the desired file & worksheet. E.g.,

Sub OpenExcel()
Dim xlApp as Excel.Application
Dim xlWB as Excel.Workbook
Dim xlSh as Excel.Worksheet

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\Your filename.xlsx") '<modify as needed
Set xlSH = xlWB.Sheets("Sheet Name") '<modify as needed

'You can paste the recorded macro in here
'
'
'
'
'

xlWB.Save
xlWB.Close
xlApp.Quit
Set xlApp = Nothing

End Sub

Controlling Excel workbook from Access 2010 VBA

1 Answers