Excel Macro Stored in Access

Question

So I'm importing data every day into Access to use for reporting. The data comes from several spreadsheets created by different individuals. Because those individuals like to format things incorrectly I created a macro that reformats their document so that it can be imported cleanly into Access for me to use. Works great but it gets tedious having to open up each Excel sheet to run this Macro.

What I'm trying to do is place the Excel Macro in Access and then run the formatting code before importing it all at once. I am a bit lost in approaching this. I'm aware of ways to run Macros already placed in Excel sheets but is there a way to run a macro that is stored in Access that works in excel. I also thought to maybe inject the Macro into the excel document and then run it.

To sum things up, what I'm hoping to do is from Access, store a macro, that can be used to alter Excel Files.

Is this at all possible? If so How? Is there another approach?

what version of access / excel are you using? If you they're less than 10 years old (or more), you can insert the same type of modules in either access or excel and use pretty much the same code after referencing the other application. You can in excel create a macro at the personal workbook level that would run your reformat on multiple files and then send the info to access. You can do the same thing from access directly. — nutsch

Rachel Hettinger Rachel Hettinger · Accepted Answer · 2013-11-20T21:26:38

What you are asking to do is automate Excel from Access. Yes, you can do this. In Access, add a module, add a reference to the Microsoft Excel object model (Tools: References), and use this framework code to get you started:

Sub PrepExcelFileForImport()

  Dim xl As Excel.Application
  Dim wbk As Excel.Workbook
  Dim wst As Excel.Worksheet

  Set xl = CreateObject("Excel.Application")
  With xl
    .Visible = True
    Set wbk = .Workbooks.Open("c:\temp\temp.xlsx")
    Set wst = wbk.Worksheets("data")
    With wst
      ' add your formatting code here, be sure to use qualified references, e.g.
      .Rows(1).Font.Bold = True
    End With
  End With
  wbk.Close SaveChanges:=True
  xl.Quit

End Sub

Excel Macro Stored in Access

1 Answers