Running Excel Macros from Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I wrote a formatting macro in excel to format the data i'm receiving so that
it can be imported into Access. My process now is to run the macro, select
the file i wish to format, then take the formatted information and manually
copy and paste it into an Access table. I'm trying to make this process as
user friendly as possible. Is there a way that i can call the excel macro
and automate the process i just mentioned with the click of a button in
Access??? Any suggestions would be helpful. Thanks!
 
Hi TVT,

You can use Automation to have VBA code running in Access launch Excel,
and open the workbook. From there, the options include:

1) Do whatever your present code does (you can either start the Excel
macro or put its code into your Access VBA procedure, which only
involves minor modifications), save the workbook, close Excel and import
the data in the usual way with DoCmd.TransferSpreadsheet.
(Copy-and-paste will almost never be the best way; if you have problems
with TransferSpreadsheet, try exporting the data from Excel to a CSV
file and import that with TransferText)

2) Go through the worksheet, collecting and formatting or transforming
the values you need before appending them to the table. Typically one
would iterate through the rows and and cells, but this method is also
particularly suitable if the data on the worksheet is in scattered cells
rather than in a table.

Without knowing more about what "formatting" you're doing there's not
much more I can say at this stage.

The following links should be helpful:
Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Run Excel macros through Automation
http://www.mvps.org/access/modules/mdl0007.htm

Importing Excel spreadsheets from code
http://www.mvps.org/access/general/gen0008.htm

Using Automation to Create and Manipulate an Excel Workbook (Q210148)
http://support.microsoft.com/?id=210148

Exporting an Excel worksheet as a CSV file
http://www.mvps.org/access/modules/mdl0046.htm

There's a white paper and code samples including tutorials available
from Microsoft at
http://support.microsoft.com/?id=253235
 
Thanks John. Those links were really helpful. With a little tweeking of my
i got it to run exactly the way i wanted to.
 
Back
Top