Results 1 to 4 of 4
  1. #1
    twckfa16 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2014
    Posts
    5

    Access macro vs. excel macro


    Hello -

    Does anyone know of a reason why the below would work in an excel macro vs. in a access macro that calls the excel macro?

    mFile = Dir(mpath & "*.xls")
    Do Until mFile = ""
    Workbooks.Open (mFile)

    Basically, the excel macro opens a file and does something. I wanted to set it up in access to call the excel macro but am
    getting an error 'File Not found'. Works fine in Excel directly but in Access getting 'File Not Found' error.

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Youd have to start an Excel object in Access (to control excel)
    THEN you can make access , run macros in excel. or just open excel, and run the macro from access using excel.
    YOU MUST HAVE THE EXCEL OBJECT LIBRARY CHECKED IN vbe menu, tools, references

    Code:
    Private Sub EditXL()
    Dim xl As excel.Application
    Set xl = CreateObject("excel.application")
    With xl
       .Visible = True
       .Workbooks.Open "C:\MSEXCEL_TEST\test.xlsx"
    
       'put more code here
    
    End With
    Set xl = Nothing
    end sub

  3. #3
    twckfa16 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2014
    Posts
    5
    Yes - this is what I have:

    Code:
    Set xl = CreateObject("Excel.Application")
        xl.Workbooks.Open ("C:\file.xlsm")
        xl.Visible = True
       xl.Run "run"
    It creates the object, opens the .xlsm, then supposed to loop through each .xls in the directory.

    Code:
    mFile = Dir(mpath & "*.xls")
    Do Until mFile = ""
    Workbooks.Open (mFile)
    
    However - I am encountering a 'File not found' error. Like I said - I can run C:\file.xlsm directly with no issue.

    thank you

  4. #4
    twckfa16 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2014
    Posts
    5
    hello -

    I figured it out - had to specify the whole path when calling macro from access. The below worked for me.

    Code:
    mFile = Dir(mpath & "*.xls")
    Do Until mFile = ""
       tPath = mpath & mFile
       Workbooks.Open (tPath)
    Thanks for your help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with excel macro from Access
    By allenjasonbrown@gmail.com in forum Macros
    Replies: 4
    Last Post: 09-18-2013, 12:50 PM
  2. Run Excel macro from Access VBA
    By ragsgold in forum Programming
    Replies: 29
    Last Post: 01-29-2013, 06:55 PM
  3. Replies: 18
    Last Post: 09-04-2012, 12:06 PM
  4. Excel Import Macro Help in Access
    By vennies83 in forum Import/Export Data
    Replies: 5
    Last Post: 11-02-2011, 09:24 AM
  5. Running excel macro from access
    By timpepu in forum Programming
    Replies: 1
    Last Post: 02-26-2010, 11:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums