Output to Excel Coding Suggestions Please

[tmdrake]

I have a subform where when you double click on a specific record, another
form opens up with specific information for the record selected. This form
is a pivot table.

What code would I use to automatically output that form (pivot table) to
Excel. I tried using a Macro, however I get an error message saying my
version of Access (2003) does not have that feature. I am trying to output
this into a template file already created with the proper formatting.

This is the code used on the double click to open the pivot table; its works
fine.

Private Sub ProjectID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.ProjectID
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"ProjectID = """ & stLinkCriteria & """"

Debug.Print stLinkCriteria
End Sub


Any help would be greatly appreciated.

 

[Jeanette Cunningham]

Can you give more info on the error message?
For the code you can use OutputTo or TransferSpreadsheet.
The VBA help on both of those explains it quite well.

Should work in a macro as well.

There are some errors where you have to register some wizard files to get
the export to work.
If it doesn't work, post back with more details.

Jeanette Cunningham

 

[tmdrake]

Thanks for your help. However, I use the following code and it worked as far
a exporting the form to Excel. Only thing is the form is a pivot table and
when Access performs the export, it exports it in a Datasheet view. How do I
make it export as a pivot table.

Private Sub ProjectID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.ProjectID
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"ProjectID = """ & stLinkCriteria & """"

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"G:\Project Staffing Spreadsheets\pivtblHours_Worked_template.xls", True, ""

Debug.Print stLinkCriteria

End Sub

Again your help is greatly appreciated.

 

[Jeanette Cunningham]

As far as I know, there is no way to do this just be export.
It would be possible to have a template workbook setup. I have seen it done
this way.
The template workbook has a worksheet for raw data and builds the pivot
table from the raw data.
The pivot table is setup permanently in the template.

When you export your data, you create a copy of the template file and you
export the data to the raw data worksheet,
and excel uses it to populate the pivot table.

Jeanette Cunningham

 

[tmdrake]

Again, thanks for your help. However, I'm still having problems with the
output to excel.

I have created a template as you suggested, only thing is when Access
outputs the data, instead of dumping the data into the template, it creates a
new excel file. Please explain what I am doing wrong.

Below is the code that I am using.

Private Sub ProjectID_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = Me.ProjectID
stDocName = "pivtblHours_Worked"
DoCmd.OpenForm stDocName, acFormDS, , _
"ProjectID = """ & stLinkCriteria & """"

DoCmd.OutputTo acOutputForm, "pivtblHours_Worked", "(*.xls)", _
"\\Escfil02\4400_Shared\4400_Project_Control\Resource Management
DB\pivtblHours_Worked_template.xlt", True, ""

Debug.Print stLinkCriteria
End Sub

 

[Jeanette Cunningham]

Here is an example using excel's copy from recordset method.
Sorry to say it is quite a bit of work to set up and get working.
Give it a try.

Dim objXLApp As Object
Dim objXLws As Object
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strDocPath
Dim strPath As String

Set db = DBEngine(0)(0)
' Open a recordset on the query for the data to export
Set rst = db.OpenRecordset("your query")
' If there are no records, return an error and exit function
If rst.EOF Then
msgbox "error, no data"
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
End If


'You need to use code here to get strPath
'Use the common file open save dialog code from The Access Web
'There are many posts on the discussion group that show you how to do this
part.

'After you have this code working, you need code like this:
'replace with names and cell references that suit your template

' Populate the excel object
Set objXLApp = CreateObject("Excel.Application")
' Open the template workbook
objXLApp.Workbooks.Open (strDocPath)
' Save the template as the file specified by the user
objXLApp.ActiveWorkbook.SaveAs (strPath)
' Select the 'Raw Data' worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets("RawData")
' Activate the selected worksheet
objXLws.Activate
' Ask Excel to copy the data from the recordset
objXLws.Range("A5").CopyFromRecordset rst
' Select the main worksheet
objXLApp.Worksheets("Main").Activate
' Activate the selected worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets("Main")
' Populate the criteria box on the main form (so the user knows what was
exported)
objXLws.Cells(10, 2).Value = strCriteria

' Destroy the recordset and database objects
rst.Close
Set rst = Nothing
Set db = Nothing

' Hide warnings on the spreadsheet
objXLApp.DisplayAlerts = False
' Refresh the root PivotTable (which refreshes all)
objXLApp.Worksheets("TheWsName").PivotTables(1).RefreshTable
' Save the workbook
objXLApp.ActiveWorkbook.Save
' Turn spreadsheet warnings back on
objXLApp.DisplayAlerts = True
' Make it visible
objXLApp.Visible = True

'**error handling, in the function exit - make sure you set the object
references to nothing as shown below.

FunctionExit:

Set objXLws = Nothing
Set objXLApp = Nothing
Exit Function

Jeanette Cunningham

 

[DawnTreader]

Hello

i have a suggestion.

instead of trying to make it export, why not make the excel file gather the
data from the database?

if you make a query that gives the results you want, then use the excel
pivot table feature to pull information from the access query. this creates
the pivot table without any coding.