DoCmd.TransferSpreadsheet error

[Danick]

I am using the DoCmd.TransferSpreadsheet to transfer a query to an excel spreadsheet and then Open the Spreadsheet.

    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "qryActionItems", CurrentProject.Path & "\" & "Action Items.xls", True

    oApp.Workbooks.Open Application.CurrentProject.Path & "\Action Items.xls", True, False
    oApp.Visible = True
    oApp.UserControl = True

Works great - but only if the spreadsheet is Closed. If it's still opened, then I get an error that the table is already opened. Is there a way to check if the spreadsheet is open. Basically, I need to:

1) check if the spreadsheet is open
2) If it's opened, then close it
3) run the DoCmd.TransferSpreadsheet
4) Open the spreadsheet.

Thanks

 

[CJ_London]

this is where error handling comes in - there are a number of ways - here is one

 ....
 ....
 On Error Resume Next
 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel19, "qryActionItems", CurrentProject.Path & "\" & "Action Items.xls", True
if err.number=1234 then
     msgbox "File Open, Please close and try again"
     On Error GoTo 0
     exit sub
 else
    On Error GoTo 0
     oApp.Workbooks.Open Application.CurrentProject.Path & "\Action Items.xls", True, False
 ....
 ....

- change the 1234 to the error number generated, or change to <>0, in which case the msgbox will need a less informative message such as 'There is an error, cannot continue'

 

[Danick]

Thanks, that seems to work in that I get the error message and it gives me a chance to close excel. But the DoCmd.TransferSpreadsheet still fires which changes the spreadsheet to bad data.

Isn't there a way to close the spreadsheet or quit excel automatically before the DoCmd.TransferSpreadsheet?

 

[CJ_London]

your question was about the code erroring if the spreadsheet was open, not about bad data - if the file is open, transfer spreadsheet fails - so does not change anything - so I suspect something else is at fault.

It may be this - acSpreadsheetTypeExcel19 - you are opening a .xls - which should be acSpreadsheetTypeExcel9 or earlier,

 

[Danick]

your question was about the code erroring if the spreadsheet was open, not about bad data - if the file is open, transfer spreadsheet fails - so does not change anything - so I suspect something else is at fault.

It may be this - acSpreadsheetTypeExcel19 - you are opening a .xls - which should be acSpreadsheetTypeExcel9 or earlier,

I'm not really getting "bad data". But since Access tries to do the TransferSpreadsheet and fails, the spreadsheet ends up with no data at all. That's not a problem since I can just close the spreadsheet and transfer new data into it. Plus, since the spreadsheet was open, it couldn't really save anything into the spreadsheet anyway. I just have to close with out saving.

So I can keep the error checking you suggested, but it would be much better if Access can check if the spreadsheet is open, close it if it is and then run TransferSpreadsheet. Is there no simple way of doing that?

 

[Minty]

There is some handy code here that might help ;
http://stackoverflow.com/questions/...-excel-workbook-without-closing-all-workbooks

 

[Danick]

There is some handy code here that might help ;
http://stackoverflow.com/questions/...-excel-workbook-without-closing-all-workbooks

I saw that one but it doesn't work for me. Maybe because I'm using Access 2003.
I get a compile error User defined type not defined on the line:

Dim XLapp As New Excel.Application

 

[Minty]

Try using late binding. https://support.microsoft.com/en-us/kb/245115

 

[Danick]

Try using late binding. https://support.microsoft.com/en-us/kb/245115

Isn't Late binding what I'm doing in my original post?

 

[Minty]

Err yes I think so - sorry I was looking at the last bit of code you posted, not the earlier posts.

 

[Danick]

I've put the working code here, but with a MsgBox as suggested by CJ. Hoping someone could add some VBA that will allow the transfer without the error trapping.

    On Error Resume Next
    Dim oApp As Object
    Set oApp = CreateObject("Excel.Application")
    
    Dim WbName As String
    Dim WbSheet As String
    
    WbSheet = "qryActionItems"
    WbName = "Action Items.xls"
    
[COLOR="Red"] 'NEED VBA HERE TO CLOSE WORKBOOK WbName IF OPEN OR CONTINUE WITH TRANSFER IF IT'S ALREADY CLOSED
 'NOTHING I'VE TRIED WORKS HENCE THE MSGBOX - GOAL IS TO REMOVE MSGBOX TO CLOSE WbName AUTOMATICALLY[/COLOR]
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True
    If Err.Number <> 0 Then
    MsgBox "File is Open. Please close and try again"
    On Error GoTo 0
    Exit Sub
    Else
    On Error GoTo 0
    oApp.Workbooks.Open Application.CurrentProject.Path & "\" & WbName, True, False
    oApp.Visible = True
    oApp.UserControl = True
    End If

 

[Danick]

I found some code that will close Excel here:

http://www.mrexcel.com/forum/excel-...sic-applications-close-excel-application.html

This uses early binding of the Excel objects but needs a reference to MS Excel Object library to get it work.

Dim Excel As Excel.Application
    Dim ExcelOpened As Boolean
    
    ExcelOpened = False
    On Error Resume Next
    Set Excel = GetObject(, "Excel.Application")
    If Excel Is Nothing Then
        Set Excel = New Excel.Application
        ExcelOpened = True
    End If
    On Error GoTo 0

    With Excel
        If ExcelOpened Then
            .Visible = True
            .Workbooks.Add
        End If
        .ActiveWorkbook.Close False
        .Quit
    End With

    
    Dim WbName As String
    Dim WbSheet As String
    
    WbSheet = "qryActionItems"
    WbName = "Action Items.xls"
    
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, WbSheet, CurrentProject.Path & "\" & WbName, True
  
    Excel.Workbooks.Open Application.CurrentProject.Path & "\" & WbName, True, False
    Excel.Visible = True
    Excel.UserControl = True

So now it does what I basically wanted to do which was to transfer the query automatically. The only issue I have with this is that it closes EVERY instance of Excel. Would have been better if it would close just the workbook WbName. But hey - at least the error message is gone!!