CODEWITHSUNDEEP
excelvbams-access
Kevin Malloy
Kevin Malloy

Reputation: 183

Excel Macro Stored in Access

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?

Upvotes: 1

Views: 1027

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

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

Upvotes: 2

Related Questions