Access (VBA) Open and run macro from an Excel workbook

/ Published in: Visual Basic
Save to your folder(s)

This code allows you to open an Excel workbook from an Access database and run macros within the target workbook. This could be used to format / clean data prior to import for example.

Copy this code and paste it in your HTML
  1. Public Function OpenExcelTarget()
  2. Dim xl As Object
  4. 'Step 1: Start Excel, then open the target workbook.
  5. Set xl = CreateObject("Excel.Application")
  6. xl.Workbooks.Open ("C:\Full_path_of_excel_file.xls")
  8. 'Step 2: Make Excel visible
  9. xl.Visible = True
  11. 'Step 3: Run the target macro
  12. xl.Run "ReplaceEmpty"
  14. 'Step 4: Close and save the workbook, then close Excel
  15. xl.ActiveWorkbook.Close (True)
  16. xl.Quit
  18. 'Step 5: Memory Clean up.
  19. Set xl = Nothing
  21. End Function

Report this snippet


RSS Icon Subscribe to comments

You need to login to post a comment.