Access 2010 Excel Export 1004 Error

Question

I have some VBA in Access 2010 that runs a query and exports the results to a column in Excel. The goal here is that when the user presses the required button an excel workbook is opened, if it doesn't exist then it is created. When a new workbook is created the VBA preforms as expected. The issue I'm having is when the workbook already exists.

So after I create an set my excel app object I attempt to open the workbook. When it doesn't exist a 1004 error occurs and I have the following error handler:

    Select Case Err
    Case 1004   'Workbook doesn't exist, make it
    xl.Workbooks.Add
    Set wb = xl.ActiveWorkbook
    wb.SaveAs strWorkBook

    Resume Next

When the user opens an existing workbook they are prompted with an Overwrite Yes,No,Cancel options. Any response other than yes generates a 1004 error (seems odd 2 different errors having same code #). I was just looking for a way around this, whether it be I write some VBA to automatically accept or I have something else that allows users to say no/cancel. The easiest is preferred, I just don't want an error poping up on them.

Answer 1

You don't need to rely on error handling to deal with whether or not the Excel file exists. Use Dir() to check whether it exists, and open or create accordingly.

If Len(Dir(strFullPathToWorkbook)) > 0 Then
    ' open the workbook '
Else
    ' create the workbook '
End If

Use wb.SaveAs strWorkBook if this is a new workbook.

Use wb.Save if it's an existing workbook.