VBA Code to Call Excel Macro (1 Viewer)

J

julia55

Registered User.
Local time
Today, 02:28
Joined
May 2, 2012
Messages
10
I have a VBA code written to call an Excel macro. The code runs, and it runs the Excel macro just fine, but for some reason in Access the code is erroring on the Excel macro even though it ran. Not sure what is happening. It is erroring on the red line of code. I did not write the Excel code, but it doesn't seem to be the issue. Any ideas what would make this line error even though the Excel macro runs fine?

Access Code:
Code: 
Function QMFQueries()
Dim xl As Object
'Step 1:  Start Excel, then open the target workbook.
    Set xl = CreateObject("Excel.Application")
    xl.Workbooks.Open ("C:\REPORT_DATA\ChronicUnits\BuildSheets\Get QMF Data.xlsb")
'Step 2:  Make Excel visible
    xl.Visible = True
'Step 3:  Run the target macro
[COLOR=red]   xl.Run "Get_QMF_Data_Nationals"[/COLOR]
'Step 4:  Close and save the workbook, then close Excel
    xl.ActiveWorkbook.Close (True)
    xl.Quit
'Step 5:  Memory Clean up.
    Set xl = Nothing
End Function

Excel Code:
Code: 
[SIZE=3][FONT=Calibri]Sub Get_QMF_Data_Nationals()[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.EnableEvents = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.DisplayAlerts = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Dim BeginTime As Double[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   BeginTime = Timer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Calculate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.ScreenUpdating = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set QMFWin = CreateObject("QMFWin.Interface")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Stat = QMFWin.InitializeServer("eProd", "", "", False)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Stat <> 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   [HOME1].Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MsgBox ("Unable to Initialize QMF Server.  " + QMFWin.GetLastErrorstring() + "     ")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Set QMFWin = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       End[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   cnt1 = 0[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   rwcnt1 = 0[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Do[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   starttime = Timer[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   procname1 = Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   runproc1 = UCase(Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column - 1))[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If runproc1 <> "YES" And runproc1 <> "Y" Or procname1 = "" Then GoTo 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 65535[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 65535[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   ProcID = QMFWin.InitializeProc(2, procname1)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If ProcID < 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MsgBox ("Unable to Initalize Proc.  " + QMFWin.GetLastErrorstring() + Chr(10) _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       + "Check the file name and make sure that it is in the path specified.")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Set QMFWin = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       [HOME1].Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ActiveWorkbook.Save[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       End[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Stat = QMFWin.RunProc(ProcID)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If Stat <> 0 Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Application.WindowState = xlMaximized[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       MsgBox (QMFWin.GetLastErrorstring() + Chr(10) + "A window will appear showing the procedure called and the date range global variables used." + Chr(10) _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       + "Please check the date range parameters and if correct, debug the proc and/or any queries using QMF for windows then try again." + Chr(10) _[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       + "This error may also have been caused by an application disconnect with the server.")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       QMFProcInfo.Label1.Caption = "GLOBAL DATE RANGE VARIABLES"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       QMFProcInfo.Label2.Caption = procname1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       QMFProcInfo.TextBox1.Text = ("&FROM = " + QMFWin.Getglobalvariable("FROM") + Chr(10) + "&TO  = " + QMFWin.Getglobalvariable("TO"))[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       QMFProcInfo.TextBox2.Text = (QMFWin.GetProcText(ProcID))[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       QMFProcInfo.Show[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Application.WindowState = xlMaximized[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       [HOME1].Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       ActiveWorkbook.Save[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]       End[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End If[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column).Interior.Color = 5296274[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).Interior.Color = 5296274[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1) = Date & " " & Time[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 1).HorizontalAlignment = xlCenter[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Cells([PROC_NAME1_1].Row + rwcnt1, [PROC_NAME1_1].Column + 2) = Format(DateAdd("s", (Timer - starttime), "00:00:00"), "h:mm:ss")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   cnt1 = cnt1 + 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]1:  rwcnt1 = rwcnt1 + 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Loop Until rwcnt1 > 30[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Set QMFWin = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Dim hours1 As String, minutes1 As String, seconds1 As String[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   hours1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "H")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If hours1 = 0 Then hours1 = "" Else If hours1 = 1 Then hours1 = hours1 & " HOUR, " Else hours1 = hours1 & " HOURS, "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   minutes1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "N")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If minutes1 = 0 Then minutes1 = "" Else If minutes1 = 1 Then minutes1 = minutes1 & " MINUTE, " Else minutes1 = minutes1 & " MINUTES, "[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   seconds1 = Format(DateAdd("s", (Timer - BeginTime), "00:00:00"), "S")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   If seconds1 = 1 Then seconds1 = seconds1 & " SECOND" Else seconds1 = seconds1 & " SECONDS"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   ActiveWorkbook.Save[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.WindowState = xlMaximized[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.DisplayAlerts = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   [HOME1].Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Call ActivateAccess[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   End[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]2:  MsgBox ("Can't continue.  Unable to locate the required files to update this report.")[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]3:  Sheets("NATIONALS").Shapes("Autoshape 101").Visible = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Sheets("NATIONALS").Shapes("Autoshape 102").Visible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.WindowState = xlMaximized[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.EnableEvents = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Application.DisplayAlerts = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   [HOME1].Activate[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   ActiveWorkbook.Close savechanges:=True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
 
D

DavidAtWork

Registered User.
Local time
Today, 08:28
Joined
Oct 25, 2011
Messages
699
have you looked at J3nny's very recent post, are you getting the same run-time 1004 error?

David
 
J

julia55

Registered User.
Local time
Today, 02:28
Joined
May 2, 2012
Messages
10
I did look at her post, but mine is a different issue. I am getting a Run-time error '440' The item with the specified name wasn't found. Which dosn't make much sense because it found and ran the Excel macro.
 
You must log in or register to reply here.

Similar threads

B
Solved VBA code which exports data from Access to Excel and then loops through the Excel file
Replies
0
Views
531
BlueFllame
B
W
CSV or XLSX Bank Statement to Access module (need to import via VBA and then write back to Excel with imported flag)
Replies
14
Views
1,068
Cotswold
Cotswold
C
Divide code to avoid "Procedure too large error"
Replies
18
Views
1,875
The_Doc_Man
The_Doc_Man
M
Solved Import from excel to access and export from Access to Excel
Replies
16
Views
2,245
mansied
M
M
Solved VBA Export to excel ..runtime error 91
Replies
9
Views
1,611
mansied
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom