none
Run VBA for Excel from Access

    Question

  • After exporting a query from Access as an .xls file, I want to then open the .xls file, and further tailor it using VBA.
    The catch is that I want to run the VBA code from Access to minimize timing errors from other automation programs.

    Using the linked thread, http://social.msdn.microsoft.com/forums/en-us/isvvba/thread/A8AF6B0E-38FC-4BCC-B839-F36A70D92EE9, I discovered that I can open the Excel file from Access VBA.

    Function lixiang()
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open "c:/access files/excel vba test/query3.xls"
    XL.Visible = True
    '''''XL.Run "congrats_macro.congrats"

     

    However, I can't run the "Congrats" subprocedure from my personal.xlsb workbook, because it doesn't appear at all when opening Excel files certain ways from other programs. The macros dialog box is completely blank.

    If possible, I'll simply paste the "Congrats" subprocedure into my Access VBA, but I don't know how to run it to effect the Excel spreadsheet from Access.

     

    Saturday, January 29, 2011 3:47 PM

Answers

  • Add-ins and startup programs are not loaded when using Excel via automation.
    You could do
    XL.Workbooks.Open XL.StartupPath & "\Personal.xlsb", ReadOnly:=True

    Or you could run Excel via the Shell command instead of via automation.

    Or you could copy the procedure to Access and modify it.
    It should work if you put
      With XL
      End With
    around the code and put a . before every Excel object reference
    e.g.

    Sub Transplanted()
      With XL
        .Worksheets("Sheet1").Activate
        .Range("A1").Select
        .ActiveCell.Value = 123
      End With
    End Sub

    But the simplest of the 3 is the first.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, January 30, 2011 8:03 AM
  • Success!

    I wasn't able to get the simple first one above to work, but this is to a much greater delight!

    Inside the function from my first post:

    Function lixiang()
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open "c:/access files/excel vba test/query3.xls"
    XL.Visible = True

    'I added the following below on the very next line

    With XL
    call procedure_below
    End With

    . I then copied and pasted the complex Subroutine I have stored in Excel into this .accdb's VBA as a seperate Subroutine, which was called by the "call procedure_below" command.

    This worked perfectly!!!!!

    • Marked as answer by Douglas F Tuesday, February 01, 2011 2:40 AM
    Tuesday, February 01, 2011 2:40 AM
  • It made perfect sense - it just wasn't working. It turns out, I had to reference the Excel Object Library for it to work.

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/88f06ae2-b39c-47c7-b466-6b49b1521da0

    Your code coupled with the above link works perfectly now! Much appreciation!

    • Marked as answer by Douglas F Sunday, February 06, 2011 12:49 AM
    Sunday, February 06, 2011 12:49 AM

All replies

  • After exporting a query from Access as an .xls file, I want to then open the .xls file, and further tailor it using VBA.
    The catch is that I want to run the VBA code from Access to minimize timing errors from other automation programs.

    Using the linked thread, http://social.msdn.microsoft.com/forums/en-us/isvvba/thread/A8AF6B0E-38FC-4BCC-B839-F36A70D92EE9, I discovered that I can open the Excel file from Access VBA.

    Function lixiang()
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open "c:/access files/excel vba test/query3.xls"
    XL.Visible = True
    '''''XL.Run "congrats_macro.congrats"

     

    However, I can't run the "Congrats" subprocedure from my personal.xlsb workbook, because it doesn't appear at all when opening Excel files certain ways from other programs. The macros dialog box is completely blank.

    If possible, I'll simply paste the "Congrats" subprocedure into my Access VBA, but I don't know how to run it to effect the Excel spreadsheet from Access.

     

    • Merged by Calvin_GaoModerator Tuesday, February 01, 2011 5:04 AM Merge them to keep in the same topic
    Saturday, January 29, 2011 2:43 AM
  • Hi Douglas

    sounds like this question should be posted in the VBA Forum, where you'll likely get more help:

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

    • Proposed as answer by Rudedog2MVP Saturday, January 29, 2011 5:34 PM
    Saturday, January 29, 2011 7:32 AM
  • Add-ins and startup programs are not loaded when using Excel via automation.
    You could do
    XL.Workbooks.Open XL.StartupPath & "\Personal.xlsb", ReadOnly:=True

    Or you could run Excel via the Shell command instead of via automation.

    Or you could copy the procedure to Access and modify it.
    It should work if you put
      With XL
      End With
    around the code and put a . before every Excel object reference
    e.g.

    Sub Transplanted()
      With XL
        .Worksheets("Sheet1").Activate
        .Range("A1").Select
        .ActiveCell.Value = 123
      End With
    End Sub

    But the simplest of the 3 is the first.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, January 30, 2011 8:03 AM
  • Success!

    I wasn't able to get the simple first one above to work, but this is to a much greater delight!

    Inside the function from my first post:

    Function lixiang()
    Dim XL As Object
    Set XL = CreateObject("Excel.Application")
    XL.Workbooks.Open "c:/access files/excel vba test/query3.xls"
    XL.Visible = True

    'I added the following below on the very next line

    With XL
    call procedure_below
    End With

    . I then copied and pasted the complex Subroutine I have stored in Excel into this .accdb's VBA as a seperate Subroutine, which was called by the "call procedure_below" command.

    This worked perfectly!!!!!

    • Marked as answer by Douglas F Tuesday, February 01, 2011 2:40 AM
    Tuesday, February 01, 2011 2:40 AM
  • I am quite surprised that the Excel macro worked perfectly in Access with no change. 

    I would have expected you to have to make XL a module level variable and put the With XL .. End With in the procedure_below and precede Excel objects within procedure_below with . as I indicated before. 

    I guess you must have a Reference to Excel's object model and you are lucky that the only global objects of Excel your code uses are ones that Access does not have itself (Selection, ActiveCell, ActiveWorkbook, ThisWorkbook).  Any use of Application.something in procedure_below would probably have given a compile error because Application would be interpreted as meaning the Access Application object, not Excel's.

     


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Tuesday, February 01, 2011 7:35 AM
  • This is interesting. I marked my above response as the answer, because it worked..

    But, once I moved my .accdb file from my personal computer to another computer with the same version of Office and Windows, it no longer worked. It keeps getting stuck on code as simple as

    range("a1").Value = "Test"

    . I started another thread because I thought the principle of what I was doing was incorrect, but it led me back to the same similar code you provided, with the same error messages.
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/88f06ae2-b39c-47c7-b466-6b49b1521da0

     

    Saturday, February 05, 2011 11:18 PM
  • As I have said before, I would expect you to have to qualify any global Excel objects to be relative to an Excel object, ultimately from your XL (the Excel.Application object).
    So,
      With XL
         ... in here put a . before any Excel object e.g. Application, Range, ActiveCell, ActiveSheet, Selection, ... .Range("A1").value = "Test"
      End With

    If you really don't get it, post your code here and I will show you what I mean.
    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, February 06, 2011 12:41 AM
  • It made perfect sense - it just wasn't working. It turns out, I had to reference the Excel Object Library for it to work.

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/88f06ae2-b39c-47c7-b466-6b49b1521da0

    Your code coupled with the above link works perfectly now! Much appreciation!

    • Marked as answer by Douglas F Sunday, February 06, 2011 12:49 AM
    Sunday, February 06, 2011 12:49 AM
  • You needed the reference to the Excel object library for
      Set objXL = New Excel.Application

    Having the reference would also avoid the need for some changes to the procedure you copied over from Excel in that it makes Excel's globals such as ActiveSheet, ActiveCell, Selection, ... known to Access.

    However, where there is a clash of globals, such as Application which exists in both Access and Excel's object models the Access one would "win" and things like Application.ScreenUpdating = False would give run-time errors.  They would need changing to objXL.ScreenUpdating = False or
      With objXL
        .Application.ScreenUpdating = False
        ...
      End With

    For that reason I still think the safest route would be to modify the called code making sure that all Excel objects were referenced from the Excel application object. 
    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Sunday, February 06, 2011 7:35 AM
  • Now what if I have my Access vba:

    1. Open 2 independant workbooks
    2. Copy data from A.(a1:a1000) to B.(a5:a1006)
    3. Copy/Paste Values on certain columns in B, and then with additional formatting on B
    4. Close B
    5. Close A

    I'm not sure how many With objXLA.. End With..  and With objXLB's.. End With..'s i'll need, or if a simple objXLA/objXLA.activate would suffice?

    Fortunately, while both sheets are open, the code will be as simple as:

    A.range(a1:a1000).copy
    B.range(a6).paste
    B.Columns(B:C).paste values <using correct syntax of course.

    I'll have vlookups in B(Template) which need updated data in A.range(a1:a1000).

     

    Wednesday, February 09, 2011 3:24 AM
  • Every use of an Excel object should reference back to the Excel application object you have created in one way or another.  Your code could be something like this:
      
      Dim objXL As Object
      Set objXL = CreateObject("Excel.Application")
      With objXL
        .Workbooks.Open(strPath & "\A.xls")
        .Workbooks.Open(strPath & "\B.xls")
        .Workbooks("A.xls").Sheets("Sheet1").Range("A1:A1000").Copy .Workbooks("B.xls").Sheets("Sheet1").Range("A6")
        ' don't know what you are wanting to paste values to B:C
        .Workbooks("A.xls").Close False
        .Workbooks("B.xls").Close True  ' save changes
        .Quit
      End With
      Set objXL = Nothing

    So, there is a With objXL block around the Excel-related code.
    There is a . before each Excel property or method that would be referenced from the Application object in Excel VBA (explicitly as in the case of Quit or implicitly in the case of Workbooks).

    Alternatively you could (with a reference to the Excel object model) use variables to reference the workbook objects,  as in

      Dim objXL As Excel.Application
      Dim wbA As Excel.Workbook
      Dim wbB As Excel.Workbook
      Set objXL = New Excel.Application
      With objXL
        Set wbA = .Workbooks.Open(strPath & "\A.xls")
        Set wbB = .Workbooks.Open(strPath & "\B.xls")
        wbA.Sheets("Sheet1").Range("A1:A1000").Copy wbB.Sheets("Sheet1").Range("A6")
        ' don't know what you are wanting to paste values to B:C
        wbA.Close False
        wbB.Close True  ' save changes
        .Quit
      End With
      Set objXL = Nothing
      Set wbA = Nothing
      Set wbB = Nothing

    No . is required before wbA because wbA is an Access VBA object variable referencing an Excel object.


    Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk
    Wednesday, February 09, 2011 7:35 AM