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.