none
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.

    Thursday, December 29, 2011 5:27 AM

Answers

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


    Regards, Hans Vogelaar
    Thursday, December 29, 2011 10:24 AM
  • 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
    Thursday, December 29, 2011 2:10 PM
  • 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
    • Marked as answer by Arcoden Tuesday, January 17, 2012 6:54 PM
    Tuesday, January 17, 2012 4:56 PM
  • 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

    • Marked as answer by Arcoden Wednesday, December 12, 2012 9:00 PM
    Tuesday, December 11, 2012 10:36 PM

All replies

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


    Regards, Hans Vogelaar
    Thursday, December 29, 2011 10:24 AM
  • 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
    Thursday, December 29, 2011 2:10 PM
  • 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

    Tuesday, January 17, 2012 4:45 PM
  • 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
    • Marked as answer by Arcoden Tuesday, January 17, 2012 6:54 PM
    Tuesday, January 17, 2012 4:56 PM
  • 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

    Monday, December 10, 2012 4:26 PM
  • 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

    Monday, December 10, 2012 9:26 PM
  • 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

    Tuesday, December 11, 2012 9:49 PM
  • 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

    • Marked as answer by Arcoden Wednesday, December 12, 2012 9:00 PM
    Tuesday, December 11, 2012 10:36 PM
  • That appears to have done it.  Thank you very much!

    Regards,

    Arcoden Roberts

    Wednesday, December 12, 2012 9:01 PM