Advanced Search

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Public Function OpenExcelTarget()
  2. Dim xl As Object
  3.  
  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")
  7.  
  8.     'Step 2:  Make Excel visible
  9.        xl.Visible = True
  10.  
  11.     'Step 3:  Run the target macro
  12.        xl.Run "ReplaceEmpty"
  13.  
  14.     'Step 4:  Close and save the workbook, then close Excel
  15.        xl.ActiveWorkbook.Close (True)
  16.         xl.Quit
  17.  
  18.     'Step 5:  Memory Clean up.
  19.        Set xl = Nothing
  20.  
  21. End Function

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.