Error 3011 when doing transferspreadsheet-CodePudding

Running VBA through Access.

Attempting to transfer select queries from access to excel.

If I run all the code together, then I get 3011 run-time error on the 2nd DoCmd.TransferSpreadsheet.

If I comment out all the code related to PATH1, then the 2nd DoCmd.TransferSpreadhseet runs fine.

The Microsoft Access database engine could not find the object 'TabUSR1'. Make sure the object exists and that you spell its name and the path name correctly...

I have removed a good bit of code that I feel to be irrelevant to my issue. That is why there are so many variables you do not see any code for.

Dim tempR1 As String
Dim tempR2 As String
Dim tempValue1 As String
Dim tempValue2 As String
Dim tempValue3 As String
Dim tempValue4 As String
Dim tempValue5 As String
Dim dt As Date
Dim d As String
Dim row As String
Dim rngC As Range
Dim rngU As Range
Dim fpath As String
Dim strFileExists
Dim xlappC As Excel.Application
Dim xlbookC As Excel.Workbook
Dim xlsheetC As Excel.Worksheet
Dim xlappU As Excel.Application
Dim xlbookU As Excel.Workbook
Dim xlsheetU As Excel.Worksheet

fpath = "PATH1"
strFileExists = Dir(fpath)
If strFileExists <> "" Then

    'set variables for Excel
    Set xlappC = CreateObject("Excel.Application")
    Set xlbookC = xlappC.Workbooks.Open(fpath)
    Set xlsheetC = xlbookC.Worksheets("Audit Fees Remittance")
    With xlappC
        .Visible = False
        .DisplayAlerts = False
        .Workbooks.Open fpath

        'Update Raw Data Cad and CSCT tab
        Set xlsheetC = xlbookC.Worksheets("Raw Data CAD and CSCT")
        With xlsheetC
            Set rst = CurrentDb.OpenRecordset("Weekly CAN 5 Raw Data to include csct")
            If rst.RecordCount > 0 Then
                tempR2 = rst.RecordCount   1
                tempR2 = .Cells(.Rows.Count, "CV").End(xlUp).Offset(tempR2).Address(False, False)
                tempR1 = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Address(False, False)
                Set rngC = .Range(tempR1, tempR2)
                rngC.Name = "TabFA8"
                DoCmd.TransferSpreadsheet acExport, 10, "PATH1", True, "TabFA8"
                .Rows(2).EntireRow.Delete
                rst.Close
                Set rst = Nothing
                Else
                    rst.Close
                    Set rst = Nothing
                End If
                tempValue2 = "$A$2:" & tempR2
                .Range(tempValue2).EntireColumn.AutoFit
                tempR1 = ""
                tempR2 = ""
            End With

'Remit for US
fpath = "PATH2"
strFileExists = Dir(fpath)
If strFileExists <> "" Then
    'set variables for Excel
    Set xlappU = CreateObject("Excel.Application")
    Set xlbookU = xlappU.Workbooks.Open(fpath)
    Set xlsheetU = xlbookU.Worksheets("Remittance Tab")
    With xlappU
        .Visible = False
        .DisplayAlerts = False
        .Workbooks.Open fpath

        'Update INTL Remittance tab
        Set xlsheetU = xlbookU.Worksheets("INTL Remittance")
        With xlsheetU
            Set rst = CurrentDb.OpenRecordset("Weekly US 5 Remittance Tab B DHLG and Jas")
            If rst.RecordCount > 0 Then
                tempR2 = rst.RecordCount   1
                tempR2 = .Cells(.Rows.Count, "V").End(xlUp).Offset(tempR2).Address(False, False)
                tempR1 = .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Address(False, False)
                If Len(tempR1) = 3 Then
                    row = Right(tempR1, 2)
                    Else
                        row = Right(tempR1, 3)
                    End If
                'set range for renaming
                'this will allow TransferSpreadhseet to know where to export to on the sheet
                Set rngU = .Range(tempR1, tempR2)
                rngU.Name = "TabUSR2"
                DoCmd.TransferSpreadsheet acExport, 10, "Weekly US 5 Remittance Tab B DHLG and Jas", "PATH2", True, "TabUSR2"
                'delete row with headers
                .Rows(row).EntireRow.Delete
                rst.Close
                Set rst = Nothing
                Else
                    rst.Close
                    Set rst = Nothing
                End If
            End With

CodePudding user response：

While I cannot exactly understand or diagnose your issue, for maintenance and readability, consider separating all Excel and Access processes. Avoid walking over same opened files with both object libraries. Therefore, consider Excel's Range.CopyFromRecordset over Access's DoCmd.TransferSpreadsheet using the very recordset you create.

...
Set rst = CurrentDb.OpenRecordset("Weekly CAN 5 Raw Data to include csct")
...

Set rngC = .Range(tempR1, tempR2)
rngC.Name = "TabFA8"
rngC.CopyFromRecordset rst
rst.Close

...
Set rst = CurrentDb.OpenRecordset("Weekly US 5 Remittance Tab B DHLG and Jas")
...

Set rngU = .Range(tempR1, tempR2)
rngU.Name = "TabUSR2"
rngU.CopyFromRecordset rst
rst.Close

CodePudding user response：

Parfait's suggestion of closing the workbook then doing the TransferSpreadsheet solved my issue.