Posted By

cueballrawn on 06/13/16


Tagged

macro excel access vba run


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

giru


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


 / Published in: Visual Basic
 

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.

  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  

You need to login to post a comment.