Import data from Access to Excel?

[kviglas]

Hi to everybody,

I have a problem and I was wondering if someone could help. I have created a grouped query in Access 2002 from some linked tables from Oracle 8.1.7. I want to import its (query's) data into a pivot table in Excel 2002 like the one in the attached picture. The query is grouped in two levels that are the Salesman and the Year, just like the excel file, as you can see from the attached file. I want to matchup these two grouping levels and import the values in the exact places.

 

[kviglas]

In fact, I want to matchup the first column of the query with the column header of the excel file and the second column of the query with the row header of the excel file. Then to import the value the third column of the query into a specific position in the excel file.

 

[kviglas]

Since no one is answering, I begin to think that this can't be done with VBA.

 

[kapil_kalra2001]

Try importing the data directly into the excel and then create a pivot table. this way the excel spreadsheet will be linked to the query/ table in access and you may have to refresh every time the main data is changed.

 

[Bachatero]

The problem with kapil's solution is that if the number of records changes in the sourcetable your pivot in excel might not catch all of the data. gimme a second

 

[Bachatero]

and finally

here u are .. this code exports your data table to an excel file (filename is hardcoded) and creates a new pivottable based on the data.
By the way you need to set the Microsoft Excel Object Library to run this code.

Public Sub exportToExcelPivot()
Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim FieldName As String
Const ExcelFile = "C:/PivotFile.xls"
Const DataTable = "tblData"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
DataTable, ExcelFile, True
Set TDef = CurrentDb.TableDefs(DataTable)
Set XlApp = New Excel.Application
XlApp.Visible = True
Set XlBook = XlApp.Workbooks.Open(ExcelFile)
With XlBook
DataRange = DataTable & "!" & .Worksheets(DataTable).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = .PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10)
With XlPT
.PivotFields(1).Orientation = xlRowField
.PivotFields(2).Orientation = xlColumnField
.PivotFields(3).Orientation = xlDataField
.RowGrand = False
End With
End With
End Sub

Hope it helps

 

[kviglas]

Firstly thanx a lot for the code. Secondly I must look at the code and try to understand what exactly it is doing! I will be in touch ...

 

[stpeteqt1975]

Help With This Code

I do not know what you mean by "set the Microsoft Excel Object Library to run this code". How do you get this code to execute? I try to compile the code and it gives me the "User defined type not defined" on the line "Dim XlApp As Excel.Application"

Thanks for any help!

 

[Victoria.Wild]

Hiya

Go to the module where you have pasted the code. Select Tools -> Reference from the Menu and select Microsoft Excel Object Library from the list. You may have version 9.0 or 10.0.

Then compile our code again and the error message should disappear.


Hope this helps you.

 

[stpeteqt1975]

Now I Get An Error

Thanks for your help on this! Now, when I try to run the script I get this run-time error: 3011 - The Microsoft Jet database engine could not find the object 'tblData'. Make sure the object exists and that you spell its name and the path name correctly.

The file is there on C:/ and there is a tab 'tblData' but I am not sure what the problem is.

 

[checoturco]

try this

Dim FieldName As String
Dim TDef As TableDef
Dim DataTable As String
Const ExcelFile = "C:/PivotFile.xls"
DataTable = "tblData"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
DataTable, ExcelFile, True
Set TDef = CurrentDb.TableDefs(DataTable)

checo