'MS Access to MS Excel export with VBA coding in MS Excel where having issue
Below is my code where I'm trying to export a MS Access query output to an Excel file and then editing the Excel to create a pivot table on a new sheet.
The code works fine every alternate time I run the code i.e. on the 1st, 3rd, 5th, 7th time it is run.
When it is run 2nd, 4th, 6th, 8th time it gives an error pointing to the line numbers 74-76 for the TableDestination field where the Activecell is used.
How can I decode the issue with this line?
1 Private Sub btnExcelExport_Click()
2 Dim strDesktop As String
3 Dim sFileName As String
4 strDesktop = Environ("USERPROFILE") & "\Desktop"
5 sFileName = strDesktop & "\CRM_Flash_Report.xlsx"
6 DoCmd.OutputTo acOutputQuery, "FinalReport", acFormatXLSX, sFileName, Autostart:=False
7 Dim xls As Excel.Application
8 Dim wkb As Excel.Workbook
9 Dim wks As Excel.Worksheet
10 Set xls = CreateObject("Excel.Application")
11 Set wkb = xls.Workbooks.Open(sFileName)
12 Set wks = wkb.Worksheets("FinalReport")
13 wks.Name = "Flash_Dump"
14 wks.Cells.WrapText = False
15 wks.Cells.Font.Name = "Trebuchet MS"
16 wks.Cells.Font.Size = "10"
17 Dim MyTableRange As Range
18 Dim TableRange As String
19 wks.Cells(1, 1).Select
20 Set MyTableRange = wks.Range("A1").CurrentRegion
21 TableRange = wks.Name & "!" & MyTableRange.Address
22 wks.Cells(1, 1).Select
23 wkb.Worksheets.Add(Before:=wkb.Worksheets(1)).Name = "Flash_Pivot"
24 Set wks = wkb.Worksheets("Flash_Pivot")
25 wks.Cells.Font.Name = "Trebuchet MS"
26 wks.Cells.Font.Size = "10"
27 wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
28 TableRange, Version:=xlPivotTableVersion15).CreatePivotTable _
29 TableDestination:="Flash_Pivot!R1C1", TableName:="PivotTable1", DefaultVersion _
30 :=xlPivotTableVersion15
31 wks.PivotTables("PivotTable1").PivotFields("Service Line").Orientation = xlRowField
32 wks.PivotTables("PivotTable1").PivotFields("Service Line").Position = 1
33 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Quarter").ClearAllFilters
34 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Quarter") _
35 .PivotFilters.Add Type:=xlCaptionContains, Value1:=FYShort(DateValue(reportDate))
36 If Not ([Forms]![Report]![Combo3] = "Mar" Or [Forms]![Report]![Combo3] = "Jun" Or _
37 [Forms]![Report]![Combo3] = "Sep" Or [Forms]![Report]![Combo3] = "Dec") Then
38 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Month").Orientation = xlColumnField
39 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Month").ClearAllFilters
40 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Quarter"). _
41 Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
42 False, False)
43 End If
44 wks.PivotTables("PivotTable1").AddDataField wks.PivotTables( _
45 "PivotTable1").PivotFields("POTENTIALID"), "Count of POTENTIALID", xlCount
46 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Quarter").Orientation = xlColumnField
47 wks.PivotTables("PivotTable1").PivotFields("Create_Period_Quarter").Position = 1
48 wks.PivotTables("PivotTable1").PivotFields("Service Line").AutoSort xlDescending, "Count of POTENTIALID"
49 wks.Range("A50000").End(xlUp).Offset(5, 0).Select
50 wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
51 TableRange, Version:=xlPivotTableVersion15).CreatePivotTable _
52 TableDestination:=wks.Name & "!" & ActiveCell.Address(ReferenceStyle:=xlR1C1), _
53 TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15
54 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").Orientation = xlRowField
55 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").Position = 1
56 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").ClearAllFilters
57 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter") _
58 .PivotFilters.Add Type:=xlCaptionContains, Value1:=FYShort(DateValue(reportDate))
59 If Not ([Forms]![Report]![Combo3] = "Mar" Or [Forms]![Report]![Combo3] = "Jun" Or _
60 [Forms]![Report]![Combo3] = "Sep" Or [Forms]![Report]![Combo3] = "Dec") Then
61 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Month").Orientation = xlColumnField
62 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Month").ClearAllFilters
63 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter"). _
64 Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
65 False, False)
66 End If
67 wks.PivotTables("PivotTable2").AddDataField wks.PivotTables( _
68 "PivotTable2").PivotFields("POTENTIALID"), "Count of POTENTIALID", xlCount
69 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").Orientation = xlColumnField
70 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").Position = 1
71 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").AutoSort xlDescending, "Count of POTENTIALID"
72 Range("A50000").End(xlUp).Offset(5, 0).Select
73 wkb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
74 TableRange, Version:=xlPivotTableVersion15).CreatePivotTable _
75 TableDestination:=wks.Name & "!" & ActiveCell.Address(ReferenceStyle:=xlR1C1), _
76 TableName:="PivotTable2", DefaultVersion:=xlPivotTableVersion15
77 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").Orientation = xlRowField
78 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").Position = 1
79 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").ClearAllFilters
80 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter") _
81 .PivotFilters.Add Type:=xlCaptionContains, Value1:=FYShort(DateValue(reportDate))
82 If Not ([Forms]![Report]![Combo3] = "Mar" Or [Forms]![Report]![Combo3] = "Jun" Or _
83 [Forms]![Report]![Combo3] = "Sep" Or [Forms]![Report]![Combo3] = "Dec") Then
84 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Month").Orientation = xlColumnField
85 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Month").ClearAllFilters
86 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter"). _
87 Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
88 False, False)
89 End If
90 wks.PivotTables("PivotTable2").AddDataField wks.PivotTables( _
91 "PivotTable2").PivotFields("POTENTIALID"), "Count of POTENTIALID", xlCount
92 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").Orientation = xlColumnField
93 wks.PivotTables("PivotTable2").PivotFields("Create_Period_Quarter").Position = 1
94 wks.PivotTables("PivotTable2").PivotFields("BDO Pursuit Location").AutoSort xlDescending, "Count of POTENTIALID"
95 Range("A50000").End(xlUp).Offset(5, 0).Select
96 wks.Cells(1, 1).Select
97 wkb.Save
98 wkb.Close True
99 Set wks = Nothing
100 Set wkb = Nothing
101 xls.Quit
102 Set xls = Nothing
103 MsgBox "Excel Formatting operation completed"
104 End Sub
Solution 1:[1]
I seem to have found the solution to this problem by some trial & error method. The issue was resoled when I replaced the ActiveCell.
with xls.
and ran the code multiple times which fortunately worked.
Every time I ran the code earlier, the excel instance was not being closed due to the ActiveCell.
reference. But when I used xls.
the excel instance in the memory was properly getting closed by the code at the end of the subroutine.
Thank you for all the people who gave me a direction to think on this issue.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | SachinK |