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

  • 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
    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
    DoCmd.RunCommand acCmdPaste
    DoCmd.OpenReport oReport.Name, acViewPreview
    oWB.Close False

    '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()
    End Function


    • Edited by JP97 Monday, February 19, 2018 7:40 PM
    Monday, February 19, 2018 7:09 PM

All replies

  • 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,


    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 MSDNFSF@microsoft.com.

    Thursday, February 22, 2018 2:57 AM