macro function failing in Windows 7 environment

Question

I have several Excel and Outlook (2007) macros that create an instance of Excel to open or create a workbook.  I recently trained a sub to cover for me while I'm on vacation, and found that this function failed on his Windows 7 machine.  The company is getting ready to move virtually everyone to Windows 7 in the very near future.  It would be extremely inconvenient for me and for others who use these macros if they could no longer function.    Is there a simple fix for this?

I am a "scratching the surface" developer making use of a knowledge of VB to extend the basic capabilities of recorded macros and make my primary job as a support technician easier and less tedious.  I am not an everyday programmer.  Please keep this in mind when responding.

Thanks for your time.

Answer 1

Can you find out where the macro fails? Without knowing that it's virtually impossible for me or others to help.

---

Regards, Hans Vogelaar

Answer 2

In addition to what Hans asked for, you should also check the versions of Excel and Outlook on each PC.  And check that the references you have selected on your PC (where everything works) are also selected on the target PC.

Finally, when you figure out where the code is failing, post the code so that we can help.  Otherwise, the help that gets provided is pretty bland and non-specific.

 

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 3

OK.  Here are the immediately available specifics.

I have:

Microsoft® Office Outlook® 2007 (12.0.6562.5003) SP2 MSO (12.0.6562.5003)

Microsoft® Office Excel® 2007 (12.0.6654.5003) SP2 MSO (12.0.6562.5003)  

He has exactly the same versions.

The failing macro code is below.

Sub ticketNumber()

Dim myNameSpace, myFolder, myItem, myInspector, myAttachments
Dim itm As Object
Dim ticketNumber, thisMonth, Proceed As String
Dim appExcel As Excel.Application- "Compile error : User-defined type not defined"
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim updated As Boolean
Dim alreadyopen As Boolean

'appExcel.Application.Visible = True

If Day(Date) < 4 And Hour(Now) < 9 Then

    Proceed = MsgBox("Have you created the new month folder and fixed the Rule?", vbYesNo, "New Month")

    If Proceed = vbNo Then
        MsgBox "Create the folder and fix the Rule.", vbCritical, "New Month"
        Exit Sub
       
    End If

End If

 

No specific library references are made in any of my macro modules.

Please let me know if you still need more information.  I do not have to troubleshoot these often enough to intuitively know what will best assist.  Your time and patience are appreciated.

Best Regards,

Arcoden

"Knowledge is better than wealth.  You must look after wealth.  Knowledge looks after you."

from The Fourth Tower of Inverness

Answer 4

If you run this code from within Outlook, you need to set a reference to the Microsoft Excel 12.0 Object Library in Tools | References..., otherwise you can't refer to Excel.Application, Excel.Workbook etc.

---

Regards, Hans Vogelaar

Answer 5

OK.  Related issue.  Using the code below, I create an instance of Excel from Outlook, paste in some data, then terminate the Excel session.  Unfortnately, the Excel session does not completely terminate in Windows 7, which causes the macro to fail at the point in bold below when it runs a second time.  Is my syntax incorrect?

Sub ticketNumber()

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim myNamespace As NameSpace
Dim myFolder As Folder
Dim myItem As Outlook.MailItem
Dim myMonth As String
Dim updated As Boolean

If Month(Date) < 11 Then

    myMonth = "0" & Month(Date)

Else

    myMonth = Month(Date)
   
End If

Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open filename:="\\JDShare\jdsn_shared_mb\JDSN Support Documents\Helpdesk Ticket contacts.xlsx"
appExcel.Visible = False
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets("Helpdesk ticket contacts")

Set myFolder = GetFolder("Mailbox - Hill Thomas J (JDSN)\Inbox\" & myMonth)
If myFolder.Items.Count = 0 Then Exit Sub

For Each myItem In myFolder.Items

    If myItem.UnRead = True Then
   
        updated = True
       
        wks.Activate
        wks.Range("A2").Select
   
        Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
   
            If Mid(myItem.Subject, 17, 1) = " " Then
           
                ActiveCell.FormulaR1C1 = Left(myItem.Subject, 16)
           
            Else
           
                ActiveCell.FormulaR1C1 = Left(myItem.Subject, 15)
           
            End If
       
    myItem.UnRead = False
    
    End If

Next myItem

If updated = True Then

    wks.Range("A2").Select
    wkb.AcceptAllChanges
    wkb.Close (True)
   
    MsgBox "Helpdesk ticket contacts updated!" & vbCr & "Save the open version!", , "Update Notification"

End If
appExcel.Quit
Set wks = Nothing
Set wkb = Nothing
Set appExcel = Nothing
Set myItem = Nothing
Set myFolder = Nothing
Set myNamespace = Nothing

End Sub

Answer 6

You're using Automation to control Excel from Outlook. Using Automation, you have to be very careful to make ALL objects of the external application refer either directly or indirectly to the application object variable that you create.

For example, the line

Set wkb = appExcel.ActiveWorkbook

is OK: wkb refers directly to the Excel application variable appExcel. And the line

Set wks = wkb.Sheets("Helpdesk ticket contacts")

is OK too: it refers to appExcel indirectly through wkb.

But in the lines

        Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

Cells does NOT refer to appExcel either directly or indirectly through another variable. Because of this, it may cause a second instance of Excel.Application to be created that is not under the control of your macro. The same goes for ActiveCell in the lines

                ActiveCell.FormulaR1C1 = Left(myItem.Subject, 16)

You should use

        wks.Cells.Find(...

and

                appExcel.ActiveCell.FormulaR1C1 ...

---

Regards, Hans Vogelaar

Answer 7

Thank you for your quick response! 

After updating with your recommendations, I have:

Sub ticketNumber()

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim myNamespace As NameSpace
Dim myFolder As Folder
Dim myItem As Outlook.MailItem
Dim myMonth As String
Dim updated As Boolean

If Month(Date) < 11 Then

    myMonth = "0" & Month(Date)

Else

    myMonth = Month(Date)
   
End If

Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open filename:="\\JDShare\jdsn_shared_mb\JDSN Support Documents\Helpdesk Ticket contacts.xlsx"
appExcel.Visible = False
Set wkb = appExcel.ActiveWorkbook
Set wks = wkb.Sheets("Helpdesk ticket contacts")

Set myFolder = GetFolder("Mailbox - Hill Thomas J (JDSN)\Inbox\" & myMonth)
If myFolder.Items.Count = 0 Then Exit Sub

For Each myItem In myFolder.Items

    If myItem.UnRead = True Then
   
        updated = True
       
        wks.Activate
        wks.Range("A2").Select
   
        wks.Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
   
            If Mid(myItem.Subject, 17, 1) = " " Then
           
                 appExcel.ActiveCell.FormulaR1C1 = Left(myItem.Subject, 16)
           
            Else
           
                 appExcel.ActiveCell.FormulaR1C1 = Left(myItem.Subject, 15)
           
            End If
       
    myItem.UnRead = False
    
    End If

Next myItem

If updated = True Then

    wks.Range("A2").Select
    wkb.AcceptAllChanges
    wkb.Close (True)
   
    MsgBox "Helpdesk ticket contacts updated!" & vbCr & "Save the open version!", , "Update Notification"

End If
appExcel.Quit
Set wks = Nothing
Set wkb = Nothing
Set appExcel = Nothing
Set myItem = Nothing
Set myFolder = Nothing
Set myNamespace = Nothing

End Sub

 

The Excel instance still does not completely terminate.  When the macro runs a second time (creating a third instance of Excel), I receive the following error at the Find statement.  Is there something additional needed to terminate the instance completely?

Run-time error '13':

Type mismatch

Regards,

Arcoden Roberts

Answer 8

Try this version:

Sub ticketNumber()
    Dim appExcel As Excel.Application
    Dim wkb As Excel.Workbook
    Dim wks As Excel.Worksheet
    Dim cel As Excel.Range
    Dim myFolder As Outlook.Folder
    Dim myItem As Outlook.MailItem
    Dim myMonth As String
    Dim updated As Boolean
 
    If Month(Date) < 11 Then
        myMonth = "0" & Month(Date)
    Else
        myMonth = Month(Date)
    End If
    Set myFolder = GetFolder("Mailbox - Hill Thomas J (JDSN)\Inbox\" & myMonth)
    If myFolder.Items.Count = 0 Then Exit Sub

    Set appExcel = CreateObject("Excel.Application")
    Set wkb = appExcel.Workbooks.Open _
        (FileName:="\\JDShare\jdsn_shared_mb\JDSN Support Documents\Helpdesk Ticket contacts.xlsx")
    Set wks = wkb.Sheets("Helpdesk ticket contacts")

    For Each myItem In myFolder.Items
        If myItem.UnRead = True Then
            updated = True
            Set cel = wks.Cells.Find(What:="", After:=wks.Range("A2"), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            If Mid(myItem.Subject, 17, 1) = " " Then
                 cel.Value = Left(myItem.Subject, 16)
            Else
                 cel.Value = Left(myItem.Subject, 15)
            End If
            myItem.UnRead = False
        End If
    Next myItem

    If updated = True Then
        wkb.AcceptAllChanges
        wkb.Close (True)
        MsgBox "Helpdesk ticket contacts updated!" & vbCr & _
            "Save the open version!", , "Update Notification"
    End If
    appExcel.Quit
    Set cel = Nothing
    Set wks = Nothing
    Set wkb = Nothing
    Set appExcel = Nothing
    Set myItem = Nothing
    Set myFolder = Nothing
End Sub

I assume that GetFolder is defined elsewhere in your code.

---

Regards, Hans Vogelaar

Answer 9

That appears to have done it.  Thank you very much!

Regards,

Arcoden Roberts