Export Query As Pivot and Chart

[JMichaelM]

I am required to export data to excel with pivot and chart. Does anyone know the best practice to do so? Here is my code in Access:


Private Sub cmdExport_Click()

Dim strFile As String
Dim objXL As Object
Dim objWB As Object


strFile = CurrentProject.Path & "\Diversity File.xlsx"

If Len(Dir(strFile)) > 0 Then
Kill strFile
End If

DoCmd.TransferSpreadsheet acExport, 10, _
"Diversity with Race Combination", CurrentProject.Path & "\Diversity File.xlsx", True


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strFile)

objXL.UserControl = True
Set objXL = Nothing

strFile = CurrentProject.Path & "\Diversity File.xlsx"
MsgBox "Data export completed", vbInformation, "Completed"
End Sub

 

[CJ_London]

because you have spaces I think this

"Diversity with Race Combination"

needs to be

"[Diversity with Race Combination]"

Don't know why you are opening the file after you have exported the data. If it is to create the pivot, suggest export the data, then open the file and start to record a macro to create your pivot.

Once done, copy the code back into Access and modify as required for object references