Works then doesn't then does - repeat!

[walrus]

Hi

I'm trying to run a bit of code in Access 2007 on Excel 2007. Its supposed to open 2 workbooks, change the names of the sheets, copy one sheet to the other workbook, save it with a new name then close.

My problem is it works... once. Run it again and I get errors (Run time error 462 at the Move statement), . Run it again and it works - rinse and repeat. Any ideas anyone?

Private Sub cmdATNormal_Click()
On Error GoTo ErrorTrap
On Error Resume Next

Dim strLocation1 As String, strLocation2 As String, strLocation As String
Dim appExcel As Object
Dim myWorkbook As Object

    strLocation1 = Me.txtFolder & "\ATXFOC.xlsx"
    strLocation2 = Me.txtFolder & "\AT_FOC.xlsx"
    fPause (10) 'if I don't pause I get errors!
    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open("" & strLocation1 & "")
    Set myWorkbook = appExcel.Workbooks.Open("" & strLocation2 & "")
    appExcel.Visible = True
    
    With appExcel
    
        .Windows("ATXFOC.xlsx").Activate
        .Sheets("qryDummy").Activate
        .Sheets("qryDummy").Select
        .Sheets("qryDummy").Name = "ATXFOC"

        .Windows("AT_FOC.xlsx").Activate
        .Sheets("qryDummy").Activate
        .Sheets("qryDummy").Select
        .Sheets("qryDummy").Name = "AT_FOC"

        .Sheets("AT_FOC").Move After:=Workbooks("ATXFOC.xlsx").Sheets(1)
        
        .Windows("ATXFOC.xlsx").Activate
        .ActiveWorkbook.SaveAs Filename:="" & Me.txtFolder & "\AssayTracker.xlsx", FileFormat:=51, CreateBackup:=True
        .Windows("AssayTracker.xlsx").Close False
               
    End With

    Set appExcel = Nothing
    Set myWorkbook = Nothing
    
ErrorTrap:
Select Case Err.Number
    Case Is = 0 'no errors
        'Do nothing
    Case Else
        MsgBox "Database Error #: " & Err.Number & vbCrLf & Err.Description
End Select
    
End Sub

 

[Mr. B]

This sounds like the situation that can occur when VBA code is being used to access objects in the Excel model but not specifically addressing the object.

One way to check to see if this is the case is, after running your code the first time, start the Task Manager and and check to see if there is an unexpected instance of Excel running when you think you have closed Excel. If the instance of Excel is found, end it. Try running your code again and it should run just fine, but then if you check again, you will fine that remaining instance of Excel again.

To correct the issue, you need to create specific object for your various objects and then use that object when you address that object.

Take a look at these links to get more info about this issue:
http://www.access-programmers.co.uk/forums/showthread.php?t=206220
The following link is also in the discussion in the link above:
http://www.btabdevelopment.com/ts/excelinstance

 

[walrus]

I tried adding the following at the start and end of my code:

'Terminate Excel if it is still open
    sKill = "TASKKILL /F /IM excel*.exe"
    Shell sKill, vbHide

to kill any hidden instances of excel but it still does it - works once, fails once, etc. But I think you are on the right track... I have a little bell ringing at the back of my mind but I cant remember why (or what I did if/when it happened before).

 

[Mr. B]

Rather than try to close the orphan Excel instance, you will be much better off to eliminate the creation of it in the first place. That instance of Excel is being created when you do not use complete references to the object and the methods you are trying to access.

Here is a couple of lines of code that I hope will help you get a handle on how to address this:

'instantiate your objects
Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLSheet As Excel.Worksheet

'Note: If you are going to open more than one workbook, you will need to
'instantiate a second instance for the second workbook so you can specifically refer 'to one specific workbook in your code.

'open Excel
Set objXLApp = New Excel.Application

'Make Excel visible
'for production the two lines below would be commented out
objXLApp.Visible = True

'open a specific workbook
'use your specific path and file name in the next line
Set objXLBook = objXLApp.Workbooks.Open("c:\Folder\FileName.xlsx")

'delete a sheet named "Sheet1"
'Note: a complete reference is used to address the worksheet and the action
objXLBook.Worksheets("Sheet1").Delete

'update the value in D12 using row and column designation (row 12, column 4)
'Note: the purpose for using the row and column method for me was that I was
'actually incrementing the column and assigning a different name for the column
'heading
'Again, note the complete reference to access the object and the action
objXLSheet.Cells(12, 4) = "Forecast Current Year January"

'close workbook and Excel
'turn off the warnings
objXLApp.Application.DisplayAlerts = False
'save changes
objXLBook.Save
'close the workbook
objXLBook.Close
'turn warning back on
objXLApp.Application.DisplayAlerts = True
'Close Excel
objXLApp.Application.Quit
'destroy your object variables
Set objXLSheet = Nothing
Set objXLBook = Nothing
Set objXLApp = Nothing

 

[walrus]

I identified my little bell! I'd come across this issue before, and solved it...

My code now reads...

Private Sub cmdATNormal_Click()
On Error GoTo ErrorTrap
On Error Resume Next

Dim strLocation1 As String, strLocation2 As String, strLocation As String
Dim appExcel As Object
Dim myWorkbook As Object
Dim sKill As String
Dim wbkSource As excel.Workbook, wbkDest As excel.Workbook, shtToCopy As excel.Worksheet

    strLocation1 = Me.txtFolder & "\ATXFOC.xlsx"
    strLocation2 = Me.txtFolder & "\AT_FOC.xlsx"
    fPause (10) 'access needs time to get its head around what I'm asking next...
    Set appExcel = CreateObject("Excel.Application")
    Set wbkSource = appExcel.Workbooks.Open("" & strLocation2 & "")
    Set wbkDest = appExcel.Workbooks.Open("" & strLocation1 & "")
    appExcel.Visible = False
    
    With appExcel
    
        .Windows("ATXFOC.xlsx").Activate
        .Sheets("qryDummy").Activate
        .Sheets("qryDummy").Select
        .Sheets("qryDummy").Name = "ATXFOC"
      
        .Windows("AT_FOC.xlsx").Activate
        .Sheets("qryDummy").Activate
        .Sheets("qryDummy").Select
        .Sheets("qryDummy").Name = "AT_FOC"
        
        .Workbooks("AT_FOC.xlsx").Activate
        .Sheets("AT_FOC").Select

        Set shtToCopy = wbkSource.Sheets(1)
        shtToCopy.Copy wbkDest.Sheets(1)

        .Windows("ATXFOC.xlsx").Activate
        .ActiveWorkbook.SaveAs Filename:="" & Me.txtFolder & "\AssayTracker.xlsx", FileFormat:=51, CreateBackup:=True
        .Windows("AssayTracker.xlsx").Close False
               
    End With
  
    appExcel.Quit
    Set appExcel = Nothing
    Set myWorkbook = Nothing
    
'Terminate Excel if it is still open
    sKill = "TASKKILL /F /IM excel.exe"
    Shell sKill, vbHide
    
ErrorTrap:
Select Case Err.Number
    Case Is = 0 'no errors
        'Do nothing
    Case Else
        MsgBox "Database Error #: " & Err.Number & vbCrLf & Err.Description
End Select
    
End Sub

...and it works every time. Although it's not as neat as yours, I'm from the school of 'Don't fix it unless its broken', and it's no longer broken!

 

[Mr. B]

I do understand that if it is not broken, don't fix it.

Gald you got it figured out.