473,326 Members | 2,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Exporting to excel

Hi

I'm trying to download a query into excel and opening it up as a pivot
table. Firstly, is this possible? This is my code thus far:

Function Export()
Dim excel As String
excel = "CONSOLIDATION_XTRACT_TOTAL"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, excel,
"C:\Documents and Settings\GregoryCh\My Documents\Test.xls"

End Function

Greg

Jul 18 '06 #1
4 1679
You could record a macro in Excel that creates the pivot table, and
then call it using automation after you transfer the data.

Jul 18 '06 #2

pi********@hotmail.com wrote:
You could record a macro in Excel that creates the pivot table, and
then call it using automation after you transfer the data.
I'm trying to automate the process.

Greg

Jul 18 '06 #3
I know I did that a while back. I believe what I did was downloaded the
data, then from access, opened the spreadsheet, whent to the pivot
table tab, and issued a refresh.

I believe I made a macro in excell to show me the steps needed and then
duplicated these instructions from within Access.

Ron

Jul 18 '06 #4

Ron2006 wrote:
I know I did that a while back. I believe what I did was downloaded the
data, then from access, opened the spreadsheet, whent to the pivot
table tab, and issued a refresh.

I believe I made a macro in excell to show me the steps needed and then
duplicated these instructions from within Access.

Ron
Well this is where I am at.

Function TsfrExcel()
Dim objXL As Object
Dim excel As String
Dim strFile As String

excel = "CONSOLIDATION_XTRACT_TOTAL"
strFile = "C:\Documents and Settings\gregorych\My
Documents\Test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, excel,
"C:\Documents and Settings\GregoryCh\My Documents\Test.xls"
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open strFile

objXL.ActiveWorkbook.PivotCaches.Add(SourceType:=x lDatabase,
SourceData:= _
"CONSOLIDATION_XTRACT_TOTAL!C1:C7").CreatePivotTab le
TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
objXL.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
objXL.ActiveSheet.Cells(3, 1).Select
objXL.ActiveSheet.PivotTables("PivotTable3").AddFi elds
RowFields:=Array( _
"Fund_Type_2", "Fund_Type", "Expenditure_Type"),
ColumnFields:="Sub Hierarchy"
With
objXL.ActiveSheet.PivotTables("PivotTable3").Pivot Fields("SumOfTotal_Budget")
.Orientation = xlDataField
.Caption = "Sum of SumOfTotal_Budget"
.Function = xlSum
End With
objXL.ActiveSheet.PivotTables("PivotTable3").Pivot Select "YHR",
xlDataAndLabel, True
With obj.Xl.ActiveSheet.PivotTables("PivotTable3").Pivo tFields("Sub
Hierarchy")
.PivotItems("(blank)").Visible = False
End With
Set objXL = Nothing
End With

End Function

At the moment I am stuck here:
objXL.ActiveWorkbook.PivotCaches.Add(SourceType:=x lDatabase,
SourceData:= _
"CONSOLIDATION_XTRACT_TOTAL!C1:C7").CreatePivotTab le
TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

Greg

Jul 19 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: sridevi | last post by:
Hello How to export data from ms-access database to excel worksheet using ASP. mainly i need to export data to multiple worksheets. it is very urgent to us. i have a sample code which works...
4
by: D | last post by:
I've created a report with many subreports of aggregate data. I want my client to be able to export this data to Excel to make her charts, etc. Only one problem: one of the fields is a "SchoolYear"...
2
by: G | last post by:
When I export data from access to excel by with "export" or "Analyze with" I seem to loose parts of some fields (long text strings). Is there a way to export it all to excel? Thanks G
2
by: Kenneth | last post by:
How do I remove the limitation in Access that deny me from exporting 24000 rows and 17 columns (in a query) into Excel? Kenneth
5
by: Neil | last post by:
Hi I'm currently exporting my datagrid to excel, taking advantage of the fact that excel can render html, the problem is that when i click the button to export it opens in browser, I want the button...
2
by: Mustufa Baig | last post by:
Hi everybody, I have an ASP.NET website where clients can view their monthly billings by selecting different options. One of the option is the way they want to see the report i.e. whether they...
1
by: Mustufa Baig | last post by:
I have an ASP.NET website where I am showing off crystal reports to users by exporting them to pdf format. Following is the code: ---------------- 1 Private Sub ExportReport() 2 Dim oStream...
2
by: bienwell | last post by:
Hi, I have a question about exporting data from datagrid control into Excel file in ASP.NET. On my Web page, I have a linkbutton "Export data". This link will call a Sub Function to perform...
2
by: Snozz | last post by:
The short of it: If you needed to import a CSV file of a certain structure on a regular basis(say 32 csv files, each to one a table in 32 databases), what would be your first instinct on how to...
4
by: Tom | last post by:
I have a gridview on all of my web pages in my web app and they all export to excel. I have one page where the gridview is binding to a datatable that i created and only the first column is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.