"c:" में कुछ xlsx फ़ाइलें हैं, जिन्हें Microsoft Access तालिका से निर्यात किया गया है। लगभग 4 फाइलें समान संख्या और कॉलम के नाम लेकिन अलग-अलग डेटा वाली हैं।

जब कोड चलना शुरू होता है, तो यह बिना किसी समस्या के पहले xlsx पिवट को सही ढंग से बनाता है, लेकिन दूसरा पुनरावृत्ति यहां त्रुटियों का कारण बनता है:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    fileName, Version:=6).CreatePivotTable TableDestination:= _
    "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6

त्रुटि: रन-टाइम त्रुटि '91': वस्तु चर या ब्लॉक चर के साथ सेट नहीं है

मैंने Sheets, ActiveWorkbook आदि से पहले "myWorkbook" का उपयोग करने का प्रयास किया है, लेकिन यह काम नहीं किया या मैंने इसे ठीक से नहीं किया।

सभी एक्सेल फाइलों में पिवट टेबल होनी चाहिए।

Sub test()
    Dim strF As String, strP As String
    Dim wb As Workbook
    Dim ws As Worksheet


    'Edit this declaration to your folder name
    strP = "c:\" 'change for the path of your folder


    strF = Dir(strP & "\*.xls*") 'Change as required



    Do While strF <> vbNullString
        'MsgBox strP & "\" & strF
        createPivot strP & "\" & strF, strF
        strF = Dir()
    Loop    
End Sub


Sub createPivot(path As String, fileName As String)

    fileName = Replace(fileName, ".xlsx", "")
    Dim appExcel As Excel.Application
    Dim myWorkbook As Excel.Workbook

    Set appExcel = CreateObject("Excel.Application")
    Set myWorkbook = appExcel.Workbooks.Open(path)
    appExcel.Visible = True

    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        fileName, Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field1")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field2")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field3")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("FieldN"), "Sum of FieldN", xlSum
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Field+1")
        .Orientation = xlRowField
        .Position = 1
    End With

    myWorkbook.Save
    myWorkbook.Close

    appExcel.Quit

    Set myWorkbook = Nothing
    Set appExcel = Nothing
Exit Sub
End Sub
1
thereal_92 19 जून 2019, 11:00

1 उत्तर

आप अर्ली बाइंडिंग या लेट बाइंडिंग का उपयोग करके एक्सेल को एक्सेस से नियंत्रित कर सकते हैं।

' EARLY BINDING
Option Compare Database
Option Explicit ' Use this to make sure your variables are defined

' One way to be able to use these objects throughout the Module is to Declare them
' Here and not in a Sub

Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub Rep()

Dim strFile As String

strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True

'Opens up the Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)

'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
'Set xlWS = xlWB("Sheet2")

With xlWS ' You are now working with the Named file and the named worksheet


End With

'Do Close and Cleanup
End Sub


 
' LATE BINDING
Sub ControlExcelFromAccess()

' No reference to a type library is needed to use late binding.
' As long as the object supports IDispatch, the method can
' be dynamically located and invoked at run-time.

' Declare the object as a late-bound object
  Dim oExcel As Object
  Dim strFile As String

  strFile = "C:\Users\Excel\Desktop\YourExcelFile.xls"

  Set oExcel = CreateObject("Excel.Application")

' The Visible property is called via IDispatch
  oExcel.Visible = True

  Set xlWB = oExcel.Workbooks.Open(strFile)

'Call code here . . .

Set oExcel = Nothing

End Sub
0
ASH 26 जून 2019, 15:41