VBA 访问 Excel 导出，重新运行脚本时出现错误 1004 和 70

Question

我有以下脚本 - 它应该将 Access 表“vrt_master”导出到 Excel 表中，进行一些格式化，然后关闭文件。脚本第一次运行时没有问题，但是当我尝试重新运行它时，当它尝试删除旧文件时​​出现错误:“运行时错误 70:权限被拒绝”(请参阅​​附加的代码)和“运行时错误 1004:对象“_Global”的方法“Cells”失败。

不知何故，Excelsheet 无法正确关闭(即使文件已关闭，我仍可以在任务管理器中找到 1 个或多个 EXCEL.EXE 进程)。

Public Sub Select_in_Excel_anzeigen()

 Dim sDatei As String
 sDatei = "C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx"
 If Dir(sDatei) <> "" Then
 Kill sDatei
 End If

'(上图)这是我收到错误 70 的地方

 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "vrt_master", "C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx", True, ""

Dim xlApp As Excel.Application
Dim xlBook As Workbook
Dim xlSheet As Worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx")
Set xlSheet = xlBook.Worksheets(1) ' 1. Tabellenblatt in Excel festlegen

With xlSheet                    


Dim LastColumn As Long
With xlSheet
    LastColumn = Cells.Find(What:="*", After:=[$A1], _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column

'(上)这是我收到运行时错误“1004” - 对象“_Global”的方法“Range”失败的地方

End With
Dim LastRow As Long
With xlSheet
    LastRow = Cells.Find(What:="*", After:=[A$1], _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
End With
xlSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1), Cells(LastRow, LastColumn)), , xlYes).Name = _
"Table1"

'(上图)这是我收到运行时错误 1004 Table Cannot Overlap A Range

Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
xlSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"

Columns(LastColumn).Select
Selection.Offset(0, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireColumn.Hidden = True

xlSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(LastRow, 12)).Address

 Rows(LastRow).Select
Selection.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Hidden = True

End With
xlBook.Save
xlBook.Close
xlApp.Application.Quit
Set xlApp = Nothing
Set xlBook = Nothing
Set xlSheet = Nothing

我将非常感谢任何帮助，阿尔文

Answer 1

这应该可以解决孤立进程的问题:

Public Sub Select_in_Excel_anzeigen()

    Dim sDatei                As String
    Dim xlApp                 As Excel.Application
    Dim xlBook                As Workbook
    Dim xlSheet               As Worksheet
    Dim LastColumn            As Long
    Dim LastRow               As Long

    sDatei = "C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx"
    If Dir(sDatei) <> "" Then Kill sDatei
    ' (above) this is where I get Error 70

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "vrt_master", "C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx", True, ""

    Set xlApp = CreateObject("Excel.Application")
    Set xlBook = xlApp.Workbooks.Open("C:\Users\a.hopf\Desktop\Export_Vertriebsreporting_Test2.xlsx")
    Set xlSheet = xlBook.Worksheets(1)    ' 1. Tabellenblatt in Excel festlegen

    With xlSheet
        LastColumn = .Cells.Find(What:="*", After:=.Range("A1"), _
                                 SearchOrder:=xlByColumns, _
                                 SearchDirection:=xlPrevious).Column

        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious).Row

        With .ListObjects.Add(xlSrcRange, .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)), , xlYes)
            .Name = "Table1"
            .TableStyle = "TableStyleLight2"
        End With

        .Range(.Cells(1, LastColumn), .Cells(1, .Columns.Count)).EntireColumn.Hidden = True

        .PageSetup.PrintArea = "A1:L" & LastRow

        .Range(.Cells(LastRow, 1), .Cells(.Rows.Count, 1)).EntireRow.Hidden = True

    End With
    xlBook.Close True
    xlApp.Quit
    Set xlApp = Nothing
    Set xlBook = Nothing
    Set xlSheet = Nothing

End Sub