Win XP64 bit vs Win 7 64 bit both Office 2007

**racer25** · 07-05-2010, 09:14 AM

Hi

I have a work PC with XP 64 bit and a laptop with Win 7 64 bit both running Office 2007.

The below code works perfectly on the Win 7 machine, however on the XP machine while the code runs to completion most of the time sometimes it fails with varying error messages.

I found the reason for the failures to be part way through the code is an Excel routine, which ask Excel to Save, Close then Quit. On the XP machine when I open task manager, I have dozens on Excel instances open. Further if I run the code with task manager open and kill them one by one the code always run through to the end.

I have tried adding taskkill into the code but due to the speed of the process it kills both the running task and the new one that is starting.

Am I missing any patches/add-ins (anything) that will avoid upgrade to Win 7. Is their a smarter way of resolving this?

Thanks in advance

Rob

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim sql As String
Dim strPath As String
Dim avarattach(3) As Variant
Dim stDocName As String
Dim ApXL As Object
Dim workBook As Object
Dim Worksheets As Object

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
    
x = [Forms]![Form]![Text10]

MonthTag = [Forms]![Form]![Text18]

    Dim strbody As String
    Dim SigString As String
    Dim Signature As String

strPath = "C:\Temp\IncomeReport\"
sql = "SELECT DISTINCT Query.ServicerName, Query.Salutation, Query.ContactEmail FROM Query;"
Set rs = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)
Do While Not rs.EOF
    strBrokerCode = rs!ServicerName
    Email = rs!ContactEmail
    Salutation = rs!Salutation
   
    DoCmd.OutputTo acOutputReport, "Master", acFormatPDF, strPath & strBrokerCode & " - SummaryIncomeRpt" & ".pdf"
    DoCmd.OutputTo acOutputQuery, "qry_SummaryIncomeTable", acFormatXLS, strPath & strBrokerCode & " - FullIncomeRpt.xls", False, "", 0, acExportQualityPrint

 Set ApXL = CreateObject("Excel.Application")

With ApXL
.Application.Visible = False
.UserControl = False
.Workbooks.Open strPath & strBrokerCode & " - FullIncomeRpt.xls"
              
    .Columns("A:A").ColumnWidth = 7.5
    .Columns("B:B").ColumnWidth = 33
    .Columns("C:G").Select
    .Selection.ColumnWidth = 12
    .Columns("C").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("D").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("E").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("F").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("G").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
    .Columns("H:H").EntireColumn.Delete
    .Rows("1:1").Select
    .Selection.WrapText = True
    .Selection.RowHeight = 45
    .Range("A1").Select

End With

With ApXL.ActiveSheet.PageSetup
    
    .LeftFooter = "&F"
    .RightFooter = "&P of &N"
    .LeftMargin = ApXL.InchesToPoints(0.25)
    .RightMargin = ApXL.InchesToPoints(0.25)
    .TopMargin = ApXL.InchesToPoints(0.25)
    .BottomMargin = ApXL.InchesToPoints(0.5)
    .HeaderMargin = ApXL.InchesToPoints(0.25)
    .FooterMargin = ApXL.InchesToPoints(0.25)
    .PrintQuality = 600
    .Orientation = xlLandscape
    .Draft = False
    .FirstPageNumber = 1
    .PaperSize = xlPaperA4
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False

End With

With ApXL
     
    .ActiveWorkbook.Save
    .ActiveWorkbook.Close
    .Application.Quit

End With
   
    SigString = "C:\Temp\Sig\finance.htm"

    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
   
   ' Create the Outlook session.
   Set objOutlook = CreateObject("Outlook.Application")
    objOutlook.Session.Logon

   ' Create the message.
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    Set OutApp = CreateObject("Outlook.Application")

   With objOutlookMsg
      ' Add the To recipient(s) to the message.
     Set objOutlookRecip = .Recipients.Add(Email)
      objOutlookRecip.Type = olTo

      ' Add the CC recipient(s) to the message.
      'Set objOutlookRecip = .Recipients.Add("")
      'objOutlookRecip.Type = olCC

      ' Set the Subject, Body, and Importance of the message.
      .Subject = "Cumulative Income Report for YTD " & MonthTag & " for " & strBrokerCode
      .HTMLBody = "<SPAN STYLE='font: 8pt Verdana'>Dear " _
        & Salutation & "<BR></BR><BR></BR>" & _
        "Please find attached your Income Report for YTD Cumulative to June." & "<BR></BR><BR></BR>" & _
        "Any queries drop a line to ############." & "<BR></BR><BR></BR>" & _
        "There are 2 files attached, a summary page and full client by client page." & "<BR></BR><BR></BR>" & _
        "Kind Regards." & "<BR></BR><BR></BR>" & _
        "Finance Team." & "<BR></BR><BR></BR>" & _
        "</span>" & "<BR></BR>" & Signature & "<BR></BR><BR></BR>"
      
      .Importance = olImportanceHigh  'High importance
       
      ' Add attachments to the message.
      If Not IsMissing(AttachmentPath) Then
        AttachmentPath = strPath & strBrokerCode & " - SummaryIncomeRpt" & ".pdf"
        Set objOutlookAttach = .Attachments.Add(AttachmentPath)
        AttachmentPath = strPath & strBrokerCode & " - FullIncomeRpt.xls"
        Set objOutlookAttach = .Attachments.Add(AttachmentPath)
      End If

      ' Resolve each Recipient's name.
      For Each objOutlookRecip In .Recipients
         objOutlookRecip.Resolve
         If Not objOutlookRecip.Resolve Then
         objOutlookMsg.Display
      End If
      Next
        x = x + TimeValue([Forms]![Form]![Text14])
        .DeferredDeliveryTime = x
        .SendUsingAccount = OutApp.Session.Accounts.Item(3)
        .Send

   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
  
rs.MoveNext
  
Loop

Set rs = Nothing

End Sub

**romperstomper** · 07-05-2010, 09:53 AM

Do you have a reference set to Excel? I would guess not since you are using CreateObject (though you might). If you don't then you can't use Excel constants - they will all just have a value of 0.
I don't think that's actually your issue though - you might try using:

With ApXL
    .ActiveWorkbook.Close True
    .Application.Quit

End With

**racer25** · 07-05-2010, 11:05 AM

Hey romperstomper,

I do have a reference set to Excel - just tried your code unfortunately no change still numerous instances of Excel.

Thanks for taking a look and if you have any more thoughts they are appreciated.

Rob

**romperstomper** · 07-05-2010, 11:07 AM

Try making the app visible in your code and see if there are any dialogs left open as you step through and try to quit.

**racer25** · 07-05-2010, 11:27 AM

To the ***** eye its closing the app.

No dialogues, file opens changes are made file saves, file closes and Excel closes... Excel opens and loops again through changes,save then close and quit. - Task Manager though says different.....

Rather infuriating.

I had it running on the laptop sat next to it - exactly same except Task Manager was not filling with Excel instances.

**romperstomper** · 07-05-2010, 12:06 PM

Are you watching the Task manager to see at what point the excel instance is created?
Incidentally, it would be a lot more efficient to start Excel once outside your loop, process all the files you need to inside the loop, then close Excel at the end.

**racer25** · 07-06-2010, 04:20 AM

Taking out the quit part does not work either - which I assume is what you were aiming at for the efficiency as when we get back to

 Set ApXL = CreateObject("Excel.Application")

With ApXL
.Application.Visible = False
.UserControl = False
.Workbooks.Open strPath & strBrokerCode & " - FullIncomeRpt.xls"

it opens up another version anyway.

Maybe I am over simplifying this but if the same code works perfectly on another machine albeit different operating system and further to complete the loop I have now tested this on a 32 bit version of XP and again works fine.

Win 7 64 bit Perfect
XP 32 bit Perfect
XP 64 bit problems as outlined.

I spent some time googling and could not find anything other than using a TASKKILL command and then pausing for 20 seconds until clear given on a bad month we would have 400 files which adds hours on to the process.

Thanks for looking

**romperstomper** · 07-06-2010, 05:57 AM

No, I was suggesting moving the CreateObject part too - more like this:

Private Sub Command0_Click()
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim strPath As String
    Dim avarattach(3) As Variant
    Dim stDocName As String
    Dim ApXL As Excel.Application
    Dim objWorkbook As Excel.Workbook
    Dim Worksheets As Excel.Worksheets
    Dim dblMargins As Double
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    

    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
    
    x = [Forms]![Form]![Text10]

    MonthTag = [Forms]![Form]![Text18]
    
    
    strPath = "C:\Temp\IncomeReport\"
    sql = "SELECT DISTINCT Query.ServicerName, Query.Salutation, Query.ContactEmail FROM Query;"
    Set rs = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)
    If Not rs.EOF Then
    
        ' Start Excel
        Set ApXL = CreateObject("Excel.Application")
        With ApXL
            .Visible = False
            .UserControl = False
            dblMargins = .InchesToPoints(0.25)
        End With
        
        ' Create the Outlook session.
        Set objOutlook = CreateObject("Outlook.Application")
        objOutlook.Session.Logon

        Do While Not rs.EOF
            strBrokerCode = rs!ServicerName
            Email = rs!ContactEmail
            Salutation = rs!Salutation
           
            DoCmd.OutputTo acOutputReport, "Master", acFormatPDF, strPath & strBrokerCode & " - SummaryIncomeRpt" & ".pdf"
            DoCmd.OutputTo acOutputQuery, "qry_SummaryIncomeTable", acFormatXLS, strPath & strBrokerCode & " - FullIncomeRpt.xls", False, "", 0, acExportQualityPrint
        
        
            Set objWorkbook = ApXL.Workbooks.Open(strPath & strBrokerCode & " - FullIncomeRpt.xls")
                      
            With objWorkbook
                With .ActiveSheet
                    .Columns("A:A").ColumnWidth = 7.5
                    .Columns("B:B").ColumnWidth = 33
                    .Columns("C:G").ColumnWidth = 12
                    .Columns("C:G").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
                    .Columns("H:H").EntireColumn.Delete
                    With .Rows("1:1")
                        .WrapText = True
                        .RowHeight = 45
                    End With
        
        
                    With .PageSetup
                        
                        .LeftFooter = "&F"
                        .RightFooter = "&P of &N"
                        .LeftMargin = dblMargins
                        .RightMargin = dblMargins
                        .TopMargin = dblMargins
                        .BottomMargin = dblMargins
                        .HeaderMargin = dblMargins
                        .FooterMargin = dblMargins
                        .PrintQuality = 600
                        .Orientation = xlLandscape
                        .Draft = False
                        .FirstPageNumber = 1
                        .PaperSize = xlPaperA4
                        .Zoom = False
                        .FitToPagesWide = 1
                        .FitToPagesTall = False
                    
                    End With ' .PageSetup
                    
                End With ' .ActiveSheet
                .Save
                .Close False
            End With ' objWorkbook
            Set objWorkbook = Nothing
           
            SigString = "C:\Temp\Sig\finance.htm"
        
            If Dir(SigString) <> "" Then
                Signature = GetBoiler(SigString)
            Else
                Signature = ""
            End If
           
        
           ' Create the message.
           Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
                
           With objOutlookMsg
              ' Add the To recipient(s) to the message.
             Set objOutlookRecip = .Recipients.Add(Email)
              objOutlookRecip.Type = olTo
        
              ' Add the CC recipient(s) to the message.
              'Set objOutlookRecip = .Recipients.Add("")
              'objOutlookRecip.Type = olCC
        
              ' Set the Subject, Body, and Importance of the message.
              .Subject = "Cumulative Income Report for YTD " & MonthTag & " for " & strBrokerCode
              .HTMLBody = "<SPAN STYLE='font: 8pt Verdana'>Dear " _
                & Salutation & "<BR></BR><BR></BR>" & _
                "Please find attached your Income Report for YTD Cumulative to June." & "<BR></BR><BR></BR>" & _
                "Any queries drop a line to ############." & "<BR></BR><BR></BR>" & _
                "There are 2 files attached, a summary page and full client by client page." & "<BR></BR><BR></BR>" & _
                "Kind Regards." & "<BR></BR><BR></BR>" & _
                "Finance Team." & "<BR></BR><BR></BR>" & _
                "</span>" & "<BR></BR>" & Signature & "<BR></BR><BR></BR>"
              
              .Importance = olImportanceHigh  'High importance
               
              ' Add attachments to the message.
              If Not IsMissing(AttachmentPath) Then
                AttachmentPath = strPath & strBrokerCode & " - SummaryIncomeRpt" & ".pdf"
                Set objOutlookAttach = .Attachments.Add(AttachmentPath)
                AttachmentPath = strPath & strBrokerCode & " - FullIncomeRpt.xls"
                Set objOutlookAttach = .Attachments.Add(AttachmentPath)
              End If
        
                ' Resolve each Recipient's name.
                For Each objOutlookRecip In .Recipients
                
                    objOutlookRecip.Resolve
                    If Not objOutlookRecip.Resolve Then
                    objOutlookMsg.Display
                    End If
                    
                Next objOutlookRecip
                
                x = x + TimeValue([Forms]![Form]![Text14])
                .DeferredDeliveryTime = x
                .SendUsingAccount = OutApp.Session.Accounts.Item(3)
                .Send
        
           End With
           Set objOutlookMsg = Nothing
          
            rs.MoveNext
          
        Loop
        rs.Close
        Set rs = Nothing
        ApXL.Quit
        Set ApXL = Nothing
        objOutlook.Quit
        Set objOutlook = Nothing
    End If
End Sub

**racer25** · 07-07-2010, 10:48 AM

Still creating multiples .... copy and paste

I gave in about half an hour and IT are going to put Win 7 on my PC as a last resort.

XP 64 bit is not the most use friendly with all its driver issues so hopefully kill two birds with one stone. That said if a few lines of code or reinstall would have fixed it great.

Thanks for your time