Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange Behaviour of Excel after Access update (A2k Win 2k)

    Dear All

    I'm using the following code to check the three Excel files versions and update them where they are not the current version. The original code was given to me by Hans (and worked fine), but I've altered it a bit...... It's since I altered it that the problems have started <img src=/S/innocent.gif border=0 alt=innocent width=20 height=20>.

    The code does what it is supposed to do, checks the files and updates them when they are not the current version. Unfortunately it also has a strange side effect. When Access is used to export data to Excel (DoCmd.OutPutTo acQuery, "queryname", acFormatxls, "C:theexcelfilename",True) the file openpords.xls also opens. I have also had some strange happening when closing the PC down, it reports multiple instances of Excel and shuts them down, although when I check in the task manager there are no instance of Excel shown. I've also noted a marked slow down of boh my PC and use's PC if the database is opened three of four times, the more times it's opened the longer Wndows spends shutting down 'invisible' versions of Excel.

    I've added to various areas, all bar one of the xlWkb.Close instructions are added by me to try and sort out the problem, they haven't.

    As per usual this is going to be something glaringly obvious to the the VBA capable, but to me it's been a pain in the neck for the last week.

    Private Sub Form_Open(Cancel As Integer) 'code from HansV of wopr.com


    Dim strWorkbook As String
    Dim strWorkbook1 As String
    Dim strWorkBook2 As String

    strWorkbook = "C:PurchasingTrackeropenpords.xls"
    strWorkbook1 = "C:PurchasingTrackerfasshorts.xls"
    strWorkBook2 = "C:PurchasingTrackerVendMaster.xls" 'If still present,and text, after 31/07/03 delete

    Dim xlApp As New Excel.Application
    Dim xlWkb As Excel.Workbook

    'On Error GoTo ErrHandler

    'No interaction with user
    xlApp.DisplayAlerts = False
    ' Open workbook
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strWorkbook)
    ' Test version
    If xlWkb.FileFormat <> xlWorkbookNormal Then
    ' Save as current version
    xlWkb.SaveAs FileName:=strWorkbook, FileFormat:=xlWorkbookNormal
    xlWkb.Close savechanges:=False
    End If
    ' Open workbook
    Set xlWkb = xlApp.Workbooks.Open(FileName:=strWorkbook1)
    ' Test version
    If xlWkb.FileFormat <> xlWorkbookNormal Then
    ' Save as current version
    xlWkb.SaveAs FileName:=strWorkbook1, FileFormat:=xlWorkbookNormal
    xlWkb.Close savechanges:=False
    End If

    ExitHandler:
    'Cleaning up
    If Not xlWkb Is Nothing Then
    xlWkb.Close savechanges:=False
    Set xlWkb = Nothing
    If Not xlWkb Is Nothing Then
    xlWkb.Close savechanges:=False
    Set xlWkb = Nothing
    If Not xlWkb Is Nothing Then
    xlWkb.Close savechanges:=False
    Set xlWkb = Nothing
    Else
    End If
    End If
    End If
    If Not xlApp Is Nothing Then
    ' Set xlApp = Nothing
    End If
    xlApp.DisplayAlerts = True
    Exit Sub

    ErrHandler:
    'Inform User
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler

    End Sub


    Please help.

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Strange Behaviour of Excel after Access update (A2k Win 2k)

    Your ExitHandler part is not quite correct. In the first place, there is only one xlWkb object, so you only need to close it and set it to Nothing once (it doesn't matter that you use it to open a workbook more than once in the code that goes before). In the second place, you don't quit the Excel application object, so it will stay in memory. This way, a new instance of Excel will be left in memory each time this code is run. You won't see these instances in the Applications tab of the Task Manager, but you will (or should) see them in the Processes tab.

    ExitHandler:
    'Cleaning up
    If Not xlWkb Is Nothing Then
    xlWkb.Close SaveChanges:=False
    Set xlWkb = Nothing
    End If
    If Not xlApp Is Nothing Then
    xlApp.Quit
    Set xlApp = Nothing
    End If
    xlApp.DisplayAlerts = True
    Exit Sub

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Behaviour of Excel after Access update (A2k Win 2k)

    Hans

    You were right, as usual <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>.

    Thanks for saving my sanity again.

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •