VBA Access to Excel export, Error 1004 and 70 when re-running the script

Question

I have the following script - it's supposed to Export the Access table "vrt_master" into an Excelsheet, do a bit of formatting and Close the file afterwards. The first time the script runs without Problems, but when I try to re-run it I get the error: "Runtime Error 70: permission denied" when it tries to delete the old file (see code appended) and "Runtime Error 1004: The Method "Cells" for the Object "_Global" failed".

Somehow the Excelsheet doesn't close properly (I can find 1 or more EXCEL.EXE processes in my Task Manager even though the file is closed).

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

' (above) this is where I get Error 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

' (above) this is where I get Run-time error '1004' - Method 'Range' of object'_Global' failed

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"

' (above) this is where I get Run-time error 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

I would be very thankful for any help, Alwin

Your orphaned excel.exe processes are due to unqualified object references. For example, this: LastColumn = Cells.Find(What:="*", After:=[$A1] should be this: LastColumn = .Cells.Find(What:="*", After:=.Range("A1"). Note the full stop (period) before Cells and Range — Rory
– Rory, Commented Jul 23, 2015 at 8:31
Thanks for your help Rory, I removed the full stop and changed the object reference you mentioned for LastColumn and LastRow. However the Excel.exe process still stays orphaned after the first run. Only closing the Access application shuts down the process. Did I miss any other object references? — Alwin
– Alwin, Commented Jul 23, 2015 at 10:38
I didn't say remove full stops, you need to add them. You have the same issues with Selection, Columns and all the other unqualified Range and Cells calls. — Rory
– Rory, Commented Jul 23, 2015 at 10:50

Rory · Accepted Answer · 2015-07-23 10:56:58Z

This should fix your issues with orphaned processes:

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

Collectives™ on Stack Overflow

VBA Access to Excel export, Error 1004 and 70 when re-running the script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related