Access 2016 - trouble shooting VBA Code for Pivot Table (temporary solution)

Question

I'm very, very rusty at my VBA code - I'm trying to get a pivot table into Access from excel and have ran into the following issue, and I'm not seeing what I've missed here:

Here is the error:

Here is my code:

Option Compare Database

Option Explicit

Sub PivotFromExcel()
'Reference to MS Excel Library and to DAO or ACEDAO
Dim oRS As DAO.Recordset, i As Long, sFile As String
Dim oXL As Excel.Application, oWB As Excel.Workbook
Dim oWS As Excel.Worksheet, oRange As Excel.Range
Dim oTable As PivotTable, oField As PivotField, oReport As Report

Set oXL = CreateObject("Excel.Application")
'oXL.Visible = True
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.Sheets(1)
Set oRS = CurrentDb.OpenRecordset("InboundShipments Query")

oWS.Cells(1, 1).CopyFromRecordset oRS
For i = 0 To oRS.Fields.Count - 1
    oWS.Cells(1, i + 1) = UCase(oRS.Fields(i).Name)
Next i
oWS.Cells.EntireColumn.AutoFit
Set oTable = oWS.PivotTableWizard

Set oField = oTable.PivotFields("CarrierScac")
oField.Orientation = xlRowField

Set oField = oTable.PivotFields("Plant")
oField.Orientation = xlRowField

Set oField = oTable.PivotFields("Mode")
oField.Orientation = xlRowField

Set oField = oTable.PivotFields("Pieces")
oField.Orientation = xlDataField
oField.Function = xlCount
oField.NumberFormat = "0"

Set oField = oTable.PivotFields("TotalCost")
oField.Orientation = xlDataField
oField.Function = xlSum
oField.NumberFormat = "$##0.00"

Set oField = oTable.PivotFields("ShipmentID")
oField.Orientation = xlColumnField
oField.Function = xlCount
oField.NumberFormat = "0"
oRange = oField.DataRange.Cells(1)

Set oReport = CreateReport
oRange.CarrierScac.CopyPicture
DoCmd.RunCommand acCmdPaste
DoCmd.OpenReport oReport.Name, acViewPreview
oWB.Close False
oXL.Quit

'create AutoExec macro with message box
'create AutoKeys with submacro ^P to run next function      

(<---This is the highlighted line with a cursor)                                

Function RunPivot()
    PivotFromExcel
End Function

---

JP

Answer 1

Hello JP,

>>Here is the error:

We did not find the error message, please try to supply the information again.

>>Here is my code:

We would suggest you share a database file so we could use the same data source to try to reproduce your issue.

Besides, I did not see which line code is the highlighted line with a cursor, did you miss some parts of code?

For sharing file, you could share it via Cloud Storage, such as One Drive and then put link here.

By the way, what's the CarrierScac? I did not see such memeber in range object members.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].