UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Macro Runs Inconsistently, Office 2007    
 
   
NineIron
post Mar 26 2012, 12:00 PM
Post #1

UtterAccess Ruler
Posts: 1,895
From: Massachusetts
The following code runs the macro in the attached spreadsheet. It works fine the first time around but, the macro doesn't run from ACCESS the second time. Works fine if I run it from EXCEL.
Any thougths?

CODE
Public Sub OpenSafetyCrossAnyMonth()
Dim objXL As Object
Dim strPath As String
Dim strMonth As String

    strPath = "C:\IncidentReporting\SafetyCross_AnyMonth.xlsm"
    strMonth = Forms!frmReports.cboAnyMonth.Column(1) & "_" & Forms!frmReports.txtYearAnyMonth
    
    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")

    If TypeName(objXL) = "Nothing" Then
        Set objXL = CreateObject("Excel.Application")
    End If

    With objXL
        .Visible = False
        '.UserControl = True
        .Workbooks.Open strPath
        .Run "AnyMonth"
        .Activeworkbook.Application.DisplayAlerts = False
        
        .Activeworkbook.SaveAs "C:\IncidentReporting\SafetyCross_AnyMonth_" & strMonth & ".xls", FileFormat:=56

        .Quit
        
    End With

    objXL.Close
    Set objXL = Nothing
    
End Sub

Attached File(s)
Attached File  SafetyCross_AnyMonth.zip ( 24.52K ) Number of downloads: 2
 
Go to the top of the page
 
+
Bob G
post Mar 26 2012, 12:13 PM
Post #2

UtterAccess VIP
Posts: 8,104
From: CT
i am just comparing what you have to something that I use and the only visual difference, which might not mean a darn thing is this so far...

yours:....

.Workbooks.Open strPath


Mine:......
.Workbooks.Open FileName:=strPath
Go to the top of the page
 
+
NineIron
post Mar 26 2012, 12:27 PM
Post #3

UtterAccess Ruler
Posts: 1,895
From: Massachusetts
It opens fine, just doesn't run the macro.
Go to the top of the page
 
+
Bob G
post Mar 26 2012, 12:37 PM
Post #4

UtterAccess VIP
Posts: 8,104
From: CT
perhaps select the proper sheet first?? this is what I have. cleansed of course

oApp.Sheets("yoursheet").Select
oApp.Run "start"
Go to the top of the page
 
+
NineIron
post Mar 26 2012, 01:13 PM
Post #5

UtterAccess Ruler
Posts: 1,895
From: Massachusetts
Nope. That didn't help. I've attached the ACCESS db.
Attached File(s)
Attached File  IncidentReportingForUA.zip ( 268.84K ) Number of downloads: 1
 
Go to the top of the page
 
+
Bob G
post Mar 26 2012, 01:15 PM
Post #6

UtterAccess VIP
Posts: 8,104
From: CT
did you do a compile ?
Go to the top of the page
 
+
NineIron
post Mar 26 2012, 01:43 PM
Post #7

UtterAccess Ruler
Posts: 1,895
From: Massachusetts
Nope but, I will.
Go to the top of the page
 
+
NineIron
post Mar 27 2012, 06:32 AM
Post #8

UtterAccess Ruler
Posts: 1,895
From: Massachusetts
I did the recompile and that didn't change anything. Now, I am calling the macro at the Workbook_Open event and that is working.
Thanx for the help.
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th May 2013 - 03:21 PM