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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

nagiese

Thread Starter
Joined
Dec 31, 2012
Messages
2
: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.

Code:
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
    xlsheet.Cells.ClearContents
        
'Step 8: Find First empty cell and use that to build a dynamic range
    With xlsheet
    xl.Range("A1").CopyFromRecordset MyRecordset
    .Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    .Range("C1").Select
    .Range("C3").Select
    '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
    MyRecordset.Close
    
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
 
Joined
Sep 4, 2003
Messages
4,912
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?

Rollin
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The message that you get is usually related to not having the VBA Editor's Library References set.
 
Joined
Sep 4, 2003
Messages
4,912
The message that you get is usually related to not having the VBA Editor's Library References set.

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.


Rollin
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

nagiese

Thread Starter
Joined
Dec 31, 2012
Messages
2
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top