Access 2010 Excel Export 1004错误 [英] Access 2010 Excel Export 1004 Error

问题描述

我在Access 2010中有一些VBA，可以运行查询并将结果导出到Excel中的列.目的是当用户按下所需的按钮时，将打开一个excel工作簿，如果不存在则创建它.创建新工作簿后，VBA将按预期执行.我遇到的问题是工作簿已经存在.

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.

因此，在创建一个我的excel应用程序对象集之后，我尝试打开工作簿.当它不存在时，将发生1004错误，并且我具有以下错误处理程序:

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

当用户打开现有工作簿时，系统会提示他们覆盖是，否，取消"选项.除是"以外的任何响应都将产生1004错误(似乎有2个具有相同代码#的不同错误).我只是在寻找解决此问题的方法，无论是编写一些VBA自动接受还是让用户说不/取消.最简单的方法是首选，我只是不想在它们上弹出错误.

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.

推荐答案

您不需要依靠错误处理来处理Excel文件是否存在.使用 Dir()检查它是否存在，并相应地打开或创建.

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

如果这是新工作簿，请使用 wb.SaveAs strWorkBook .

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

如果已存在工作簿，请使用 wb.Save .

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

这篇关于Access 2010 Excel Export 1004错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！