1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 VB code to run Excel 2007 macro in active sheet

Discussion in 'Business Applications' started by nagiese, Dec 31, 2012.

Thread Status:
Not open for further replies.
  1. nagiese

    nagiese Thread Starter

    Dec 31, 2012
    :confused:I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.

    Option Compare Database
    Sub GetJournal_Entry_Data_transfer_to_Excel()
    'Step 1: Declare your Variables
        Dim MyConnect As String
        Dim MyRecordset As ADODB.Recordset
        Dim MyQueryDef As DAO.QueryDef
        Dim MyDatabase As DAO.Database
        Dim MySQL As String
        Dim MyRange As String
        Dim s As String
        Dim Db As Database
        Dim xl As Excel.Application
        Set xl = CreateObject("Excel.Application")
        Dim xlwkbk As Excel.Workbook
        'Dim xlworkbk As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim i As Integer
    'Step 2:Declare your connection string
        MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"
    'Step 3: Build Your SQL Statement
        MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"
    'Step 4: Instantiate and specify your recordset
        Set MyRecordset = New ADODB.Recordset
        MyRecordset.Open MySQL, CurrentProject.Connection
    'Step 6: Instantiate Excel. If Excel isn't Loaded, Error # 429 occurs.
        Set xl = GetObject(, "Excel.Application")
    'Step 7: Open TAN_JE_Export.xlsx
        Set xlwkbk = xl.Workbooks.Open("P:\FINANCE\Balance Sheet\Inventory\Project TAN\TAN_JE_Export.xlsm")
        Set xlsheet = xlwkbk.Worksheets("Culls_Stat34_1381")
        xl.Visible = True
        xlwkbk.Windows(1).Visible = True
    'Step 8: Find First empty cell and use that to build a dynamic range
        With xlsheet
        xl.Range("A1").CopyFromRecordset MyRecordset
        Selection.Insert Shift:=xlToRight
        'Cols_To_Insert = 0
        'Worksheets("Culls_Stat34_1381").Range("B1:" & Chr(Asc("B") + Cols_To_Insert) & "1").EntireColumn.Insert
        Cols_To_Insert = 2
        Worksheets("Culls_Stat34_1381").Range("F1:" & Chr(Asc("F") + Cols_To_Insert) & "1").EntireColumn.Insert
        Cols_To_Insert = 0
        Worksheets("Culls_Stat34_1381").Range("J1:" & Chr(Asc("J") + Cols_To_Insert) & "1").EntireColumn.Insert
        Cols_To_Insert = 1
        Worksheets("Culls_Stat34_1381").Range("M1:" & Chr(Asc("M") + Cols_To_Insert) & "1").EntireColumn.Insert
        Cols_To_Insert = 0
        Worksheets("Culls_Stat34_1381").Range("Q1:" & Chr(Asc("Q") + Cols_To_Insert) & "1").EntireColumn.Insert
        Cols_To_Insert = 0
        Worksheets("Culls_Stat34_1381").Range("T1:" & Chr(Asc("T") + Cols_To_Insert) & "1").EntireColumn.Insert
        End With
    'Step 9: Add column heading names to the spreadsheet
        'For i = 1 To MyRecordset.Fields.Count
        'xl.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
        'Next i
    'Step 10: save as text file & Close active recorset
        xl.Visible = True
        xl.Run "Export_Stat34_1381_culls_TXT_File"
        xlwkbk.Close (True)
        [COLOR=red]ActiveWorkbook.Close ------ This is where I get the run-time error
    [/COLOR]    ActiveWindow.WindowState = xlMinimized
        MsgBox "JE Has Been Exported to Excel & Text File"
    'Step 11: Memory Clean up
        Set xlsheet = Nothing
        Set xlwkbk = Nothing
        Set xlApp = Nothing
        Set Db = Nothing
    End Sub
    'Step 12: Close Excel
        Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim wBook As Workbook
        Dim LCount As Long
        If Cancel = False Then
            For Each wBook In Workbooks
                If wBook.Name <> Me.Name And UCase(wBook.Name) <> "PERSONAL.XLS" Then
                    LCount = LCount + 1
                End If
            Next wBook
            If LCount = 0 Then Excel.Application.Quit
        End If
    End Sub
  2. Rollin_Again


    Sep 4, 2003
    Does the previous line not do what you are wanting to accomplish >> xlwkbk.Close

    What is the name of the workbook you are trying to close?

  3. OBP


    Mar 8, 2005
    The message that you get is usually related to not having the VBA Editor's Library References set.
  4. Rollin_Again


    Sep 4, 2003

    What you are saying is generally true but this is not the case here since the code would have blown up much earlier. You can't simply use "Activeworkbook" without including including reference the Excel objects that were created earlier in the code. That is why I was asking what the line before the faulting line does since it is correctly calling for a workbook to be closed. As far as I can see it does the same thing unless the user is trying to close a different workbook that was not opened via the macro.

  5. OBP


    Mar 8, 2005
    Rollin, Happy New Year to you, you are right, perhaps the active workbook was xlwkbk and closing it without switching to another workbook has caused the problem.
    The other time you get that kind of message is when a variable has not been dimensioned before referencing it.
    The OP does not appear to be in a hurry to respond to your question.
  6. nagiese

    nagiese Thread Starter

    Dec 31, 2012
    I apologize for my delayed response; I was out of the office for the New Year’s holiday.
    It does not close the active workbook (TAN_JE_Export.xlsm), but it also does not run the Excel macro in the line above. The workbook is already active and I'm trying to get it to run an Excel macro stored in the TAN_JE_Export.xlsm workbook after it formats it. My ultimate goal is for the code to select the active sheet and save a copy of that sheet as a text file. I couldn't figure out how to get it to work in Access VBA, so I wrote the macro in Excel. Now I'm trying to get the Access VBA code to run the Excel Macro. It’s not recognizing the active workbook to run the Excel macro. I'm not sure if I need to tell it which workbook to run as there may be several open but the TAN_JE_Export.xlsm workbook would be the active one based on what the code does before that section.
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083196

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice