Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Unanswered: Create/Run a macro in Excel from Access

    I have a number of excel files created every week that I would like to import into Access, however the data in its current form/structure is not suitable.

    I have written a macro in Excel to restructure the data and this is now working perfectly.

    However, it will not be possible to get this macro saved into my target files before they are created, so was wondering if I could use access to open all of the target files, saved in a particular directory, run the "excel VBA" to create an "import" tab in each file and then import into my db.

    I am ok with the looping through all the files but can't work out how to run the "excel VBA" from Access.

    Many thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry not my bag. if it were me I'd do the import within Access, do my validation there, especially if its a coming from a regular spreadsheet format(s)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution:
    Code:
    Sub RunExcelMacro()
    
        Dim appXl As Excel.Application
        
        Set appXl = CreateObject("Excel.Application")
        appXl.Workbooks.Open "C:\SinnDHo\Access\ExcelFile.xls" ' Name of the Excel workbook.
        appXl.Run "Macro1" ' Name of the Macro.
        appXl.Quit
        Set appXl = Nothing
    
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •