|
 |
access thread: Exporting to excel template
Message #1 by "Lesley Schulz" <lesley.schulz@h...> on Fri, 20 Dec 2002 16:52:55
|
|
I have a report in Access 97 that I want to export to an excel template.
On a form the user can press a button to export the document. How would I
program it to export that report to an excel template?
Can anyone help?
Thanks
Lesley Schulz
Message #2 by "Steve Klein" <Stephen@K...> on Fri, 20 Dec 2002 18:37:22 -0000
|
|
Are you tryng to output to an excel spreadsheet or to a specific template?
It is usually easier to work with the underlying query than outputting the
report
Steve K
-----Original Message-----
From: Lesley Schulz [mailto:lesley.schulz@h...]
Sent: 20 December 2002 16:53
To: Access
Subject: [access] Exporting to excel template
I have a report in Access 97 that I want to export to an excel template.
On a form the user can press a button to export the document. How would I
program it to export that report to an excel template?
Can anyone help?
Thanks
Lesley Schulz
Message #3 by "Gerald, Rand" <RGerald@u...> on Fri, 20 Dec 2002 13:21:52 -0600
|
|
One problem with the DoCmd.TransferSpreadsheet command is that it only
accepts TABLES as data sources. It will not work with QUERIES. The
only
way around it is to use the query to create a temporary table and then
use
DoCmd.TransferSpreadsheet with the temporary table.
Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Steve Klein [mailto:Stephen@K...]
Sent: Friday, December 20, 2002 12:37
To: Access
Subject: [access] RE: Exporting to excel template
Are you tryng to output to an excel spreadsheet or to a specific
template?
It is usually easier to work with the underlying query than outputting
the
report
Steve K
-----Original Message-----
From: Lesley Schulz [mailto:lesley.schulz@h...]
Sent: 20 December 2002 16:53
To: Access
Subject: [access] Exporting to excel template
I have a report in Access 97 that I want to export to an excel
template.
On a form the user can press a button to export the document. How
would I
program it to export that report to an excel template?
Can anyone help?
Thanks
Lesley Schulz
Message #4 by "Steve Klein" <Stephen@K...> on Fri, 20 Dec 2002 21:16:56 -0000
|
|
DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
Steve K
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: 20 December 2002 19:22
To: Access
Subject: [access] RE: Exporting to excel template
Importance: High
One problem with the DoCmd.TransferSpreadsheet command is that it only
accepts TABLES as data sources. It will not work with QUERIES. The only
way around it is to use the query to create a temporary table and then use
DoCmd.TransferSpreadsheet with the temporary table.
Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Steve Klein [mailto:Stephen@K...]
Sent: Friday, December 20, 2002 12:37
To: Access
Subject: [access] RE: Exporting to excel template
Are you tryng to output to an excel spreadsheet or to a specific template?
It is usually easier to work with the underlying query than outputting the
report
Steve K
-----Original Message-----
From: Lesley Schulz [mailto:lesley.schulz@h...]
Sent: 20 December 2002 16:53
To: Access
Subject: [access] Exporting to excel template
I have a report in Access 97 that I want to export to an excel template.
On a form the user can press a button to export the document. How would I
program it to export that report to an excel template?
Can anyone help?
Thanks
Lesley Schulz
Message #5 by "Bob Bedell" <bobbedell15@m...> on Fri, 20 Dec 2002 21:48:42 +0000
|
|
strReportName = "rptAlphabeticalListofCustomers"
Docmd.OutputTo acOutputReport, strReportName, acFormatXLS,, True
will also work and preserve minimal formatting like stepped groups
>From: "Steve Klein" <Stephen@K...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Exporting to excel template
>Date: Fri, 20 Dec 2002 21:16:56 -0000
>
> DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
>
>
>Steve K
>
>
>-----Original Message-----
>From: Gerald, Rand [mailto:RGerald@u...]
>Sent: 20 December 2002 19:22
>To: Access
>Subject: [access] RE: Exporting to excel template
>Importance: High
>
>
>One problem with the DoCmd.TransferSpreadsheet command is that it only
>accepts TABLES as data sources. It will not work with QUERIES. The only
>way around it is to use the query to create a temporary table and then use
>DoCmd.TransferSpreadsheet with the temporary table.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Steve Klein [mailto:Stephen@K...]
>Sent: Friday, December 20, 2002 12:37
>To: Access
>Subject: [access] RE: Exporting to excel template
>
>Are you tryng to output to an excel spreadsheet or to a specific template?
>
>It is usually easier to work with the underlying query than outputting the
>report
>
>Steve K
>
>
>-----Original Message-----
>From: Lesley Schulz [mailto:lesley.schulz@h...]
>Sent: 20 December 2002 16:53
>To: Access
>Subject: [access] Exporting to excel template
>
>
>I have a report in Access 97 that I want to export to an excel template.
>On a form the user can press a button to export the document. How would I
>program it to export that report to an excel template?
>
>Can anyone help?
>
>Thanks
>Lesley Schulz
>
>
>
>
>
>
_________________________________________________________________
The new MSN 8: smart spam protection and 3 months FREE*.
http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=7474&SU=
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspamprotection_3mf
Message #6 by "Steve Klein" <Stephen@K...> on Sat, 21 Dec 2002 06:27:39 -0000
|
|
Bob is right. but his is also a disadvantage. If your group header is
formated to Arial 16, which might work well on paper, it will be farmatted
to Arial 16 on your spreadsheet. Which is why I recommended using the
underlying query.
Steve K
-----Original Message-----
From: Bob Bedell [mailto:bobbedell15@m...]
Sent: 20 December 2002 21:49
To: Access
Subject: [access] RE: Exporting to excel template
strReportName = "rptAlphabeticalListofCustomers"
Docmd.OutputTo acOutputReport, strReportName, acFormatXLS,, True
will also work and preserve minimal formatting like stepped groups
>From: "Steve Klein" <Stephen@K...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Exporting to excel template
>Date: Fri, 20 Dec 2002 21:16:56 -0000
>
> DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
>
>
>Steve K
>
>
>-----Original Message-----
>From: Gerald, Rand [mailto:RGerald@u...]
>Sent: 20 December 2002 19:22
>To: Access
>Subject: [access] RE: Exporting to excel template
>Importance: High
>
>
>One problem with the DoCmd.TransferSpreadsheet command is that it only
>accepts TABLES as data sources. It will not work with QUERIES. The only
>way around it is to use the query to create a temporary table and then use
>DoCmd.TransferSpreadsheet with the temporary table.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Steve Klein [mailto:Stephen@K...]
>Sent: Friday, December 20, 2002 12:37
>To: Access
>Subject: [access] RE: Exporting to excel template
>
>Are you tryng to output to an excel spreadsheet or to a specific template?
>
>It is usually easier to work with the underlying query than outputting the
>report
>
>Steve K
>
>
>-----Original Message-----
>From: Lesley Schulz [mailto:lesley.schulz@h...]
>Sent: 20 December 2002 16:53
>To: Access
>Subject: [access] Exporting to excel template
>
>
>I have a report in Access 97 that I want to export to an excel template.
>On a form the user can press a button to export the document. How would I
>program it to export that report to an excel template?
>
>Can anyone help?
>
>Thanks
>Lesley Schulz
>
>
>
>
>
>
_________________________________________________________________
The new MSN 8: smart spam protection and 3 months FREE*.
http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=747
4&SU
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspampr
otection_3mf
Message #7 by "Lesley Schulz" <lesley.schulz@h...> on Mon, 23 Dec 2002 17:36:50
|
|
I have an excel template that I want the data to dump to. Is that
possible?
> DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
Steve K
-----Original Message-----
From: Gerald, Rand [mailto:RGerald@u...]
Sent: 20 December 2002 19:22
To: Access
Subject: [access] RE: Exporting to excel template
Importance: High
One problem with the DoCmd.TransferSpreadsheet command is that it only
accepts TABLES as data sources. It will not work with QUERIES. The only
way around it is to use the query to create a temporary table and then use
DoCmd.TransferSpreadsheet with the temporary table.
Rand E Gerald
Database Specialist
Information Services / Operations
Bahá'í National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx
-----Original Message-----
From: Steve Klein [mailto:Stephen@K...]
Sent: Friday, December 20, 2002 12:37
To: Access
Subject: [access] RE: Exporting to excel template
Are you tryng to output to an excel spreadsheet or to a specific template?
It is usually easier to work with the underlying query than outputting the
report
Steve K
-----Original Message-----
From: Lesley Schulz [mailto:lesley.schulz@h...]
Sent: 20 December 2002 16:53
To: Access
Subject: [access] Exporting to excel template
I have a report in Access 97 that I want to export to an excel template.
On a form the user can press a button to export the document. How would I
program it to export that report to an excel template?
Can anyone help?
Thanks
Lesley Schulz
Message #8 by "Bob Bedell" <bobbedell15@m...> on Tue, 24 Dec 2002 02:22:13 +0000
|
|
Hi Lesley,
Turns out the Add method of an Excel Workbook object takes an optional
Template argument.
The two functions (plus one test function) below should do the job for
you. Paste everything into a single standard module. You need to modify
very little.
ExportToTemplate accepts an optional name of an Excel template, and
creates a new workbook based on the template. The other optional
parameter, strDataPage, indicates the worksheet in a multipage workbook
where you want to write the data. The two required parameters,
intStartRow and intStartColumn, position the output on the worksheet
for you. This is helpful if your template has column or row headers
that you don't want over-written. (1,1) is the upper-lefthand corner
of the worksheet, (2,1) starts writing to the second row, etc.
OpenExcel is a utility function that opens an instance of Excel and
returns True if it succeeds.
To test the module, add the following function:
Public Function Test() As Boolean
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Table1")
Debug.Print ExportToTemplate(rst, 1, 1, m_xls, _
"C:\Temp\Template1.xlt")
Set m_xls = Nothing
End Function
Just replace my arguments with the name of the Access object that
stores your data, and the path and name of your .xlt file. On my
system the code defaults to C:\My Documents if I don't include the path.
Call 'Test' from the Immediate Window, and everything should run. When
the routines finish, ExportToTemplate (hopefully) returns True, which is
written to the Immediate Window.
Just be sure m_xls is a module level variable. You need a Reference to
Excel and DAO 3.x
~~~~~~~~~StartCode~~~~~~~~~~
Option Compare Database
Option Explicit
Private m_xls As Excel.Application
Public Function ExportToTemplate(rst As DAO.Recordset, _
intStartRow As Integer, _
intStartCol As Integer, _
objExcel As Object, _
Optional strTemplate, _
Optional strDataPage) As Boolean
Dim xlsSheet As Excel.Worksheet
Dim intRow As Integer
Dim i As Integer
If rst.RecordCount = 0 Then
Call MsgBox("No Data", vbOKOnly)
Else
If OpenExcel() Then
If Not IsMissing(strTemplate) Then
m_xls.Workbooks.Add strTemplate
Else
m_xls.Workbooks.Add
End If
If Not IsMissing(strDataPage) Then
Set xlsSheet = m_xls.Worksheets(strDataPage)
Else
Set xlsSheet = m_xls.ActiveSheet
End If
With xlsSheet
intRow = intStartRow
Do Until rst.EOF
For i = intStartCol To _
(intStartCol + rst.Fields.Count - 1)
.Cells(intRow, i).Value = rst.Fields _
(i - intStartCol)
Next i
rst.MoveNext
intRow = intRow + 1
Loop
End With
xlsSheet.Visible = True
m_xls.Visible = True
Set objExcel = m_xls
ExportToTemplate = True
End If
End If
End Function
Function OpenExcel()
On Error Resume Next
Set m_xls = GetObject(, "Excel.Application")
If m_xls Is Nothing Then
Set m_xls = New Excel.Application
End If
If m_xls Is Nothing Then
MsgBox "Can't Create Excel Object"
OpenExcel = False
Else
OpenExcel = True
End If
DoEvents
End Function
~~~~~~~~~EndCode~~~~~~~~~~
Best,
Bob
>From: "Lesley Schulz" <lesley.schulz@h...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Exporting to excel template
>Date: Mon, 23 Dec 2002 17:36:50
>
>I have an excel template that I want the data to dump to. Is that
>possible?
>
> > DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
>
>
>Steve K
>
>
>-----Original Message-----
>From: Gerald, Rand [mailto:RGerald@u...]
>Sent: 20 December 2002 19:22
>To: Access
>Subject: [access] RE: Exporting to excel template
>Importance: High
>
>
>One problem with the DoCmd.TransferSpreadsheet command is that it only
>accepts TABLES as data sources. It will not work with QUERIES. The only
>way around it is to use the query to create a temporary table and then use
>DoCmd.TransferSpreadsheet with the temporary table.
>
>Rand E Gerald
>Database Specialist
>Information Services / Operations
>Bahá'í National Office
>1233 Central St.
>Evanston IL 60201
>(xxx) xxx-xxxx
>
>-----Original Message-----
>From: Steve Klein [mailto:Stephen@K...]
>Sent: Friday, December 20, 2002 12:37
>To: Access
>Subject: [access] RE: Exporting to excel template
>
>Are you tryng to output to an excel spreadsheet or to a specific template?
>
>It is usually easier to work with the underlying query than outputting the
>report
>
>Steve K
>
>
>-----Original Message-----
>From: Lesley Schulz [mailto:lesley.schulz@h...]
>Sent: 20 December 2002 16:53
>To: Access
>Subject: [access] Exporting to excel template
>
>
>I have a report in Access 97 that I want to export to an excel template.
>On a form the user can press a button to export the document. How would I
>program it to export that report to an excel template?
>
>Can anyone help?
>
>Thanks
>Lesley Schulz
>
>
>
>
>
_________________________________________________________________
The new MSN 8: smart spam protection and 3 months FREE*.
http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=7474&SU=
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspamprotection_3mf
Message #9 by "Bob Bedell" <bobbedell15@m...> on Tue, 24 Dec 2002 03:24:43 +0000
|
|
'Course I guess that'll only work for a pretty rudimentary template,
like something with uniform column headings that you might want to
chart from or something. As soon as your templates get a little more
complex (like read-only, protected cells and what not), I can see how
exporting to a template would soon become a huge head-ache, if it could
be done at all. You'd need to write specific fields to specific cells,
etc.
On the other hand, I'd like to see if there is an Excel equivalent to
Word Custom Doc Properties that would correspond to the fields you want
to write to in various Excel template sections. I'll bet there is.
That's probably more along the lines of what you would need. Then you
could just format an Excel template to look like an Access report in
the same way that you can format a Word template to look like an Access
report.
Worth looking into.
>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] RE: Exporting to excel template
>Date: Tue, 24 Dec 2002 02:22:13 +0000
>
>Hi Lesley,
>
>Turns out the Add method of an Excel Workbook object takes an optional
>Template argument.
>
>The two functions (plus one test function) below should do the job for
>you. Paste everything into a single standard module. You need to modify
>very little.
>
>ExportToTemplate accepts an optional name of an Excel template, and
>creates a new workbook based on the template. The other optional
>parameter, strDataPage, indicates the worksheet in a multipage workbook
>where you want to write the data. The two required parameters,
>intStartRow and intStartColumn, position the output on the worksheet
>for you. This is helpful if your template has column or row headers
>that you don't want over-written. (1,1) is the upper-lefthand corner
>of the worksheet, (2,1) starts writing to the second row, etc.
>
>OpenExcel is a utility function that opens an instance of Excel and
>returns True if it succeeds.
>
>To test the module, add the following function:
>
>Public Function Test() As Boolean
>
> Dim rst As DAO.Recordset
> Set rst = CurrentDb.OpenRecordset("Table1")
> Debug.Print ExportToTemplate(rst, 1, 1, m_xls, _
> "C:\Temp\Template1.xlt")
> Set m_xls = Nothing
>
>End Function
>
>Just replace my arguments with the name of the Access object that
>stores your data, and the path and name of your .xlt file. On my
>system the code defaults to C:\My Documents if I don't include the path.
>
>Call 'Test' from the Immediate Window, and everything should run. When
>the routines finish, ExportToTemplate (hopefully) returns True, which is
>written to the Immediate Window.
>
>Just be sure m_xls is a module level variable. You need a Reference to
>Excel and DAO 3.x
>
>~~~~~~~~~StartCode~~~~~~~~~~
>
>Option Compare Database
>Option Explicit
>
>Private m_xls As Excel.Application
>
>Public Function ExportToTemplate(rst As DAO.Recordset, _
> intStartRow As Integer, _
> intStartCol As Integer, _
> objExcel As Object, _
> Optional strTemplate, _
> Optional strDataPage) As Boolean
>
> Dim xlsSheet As Excel.Worksheet
> Dim intRow As Integer
> Dim i As Integer
>
> If rst.RecordCount = 0 Then
> Call MsgBox("No Data", vbOKOnly)
> Else
> If OpenExcel() Then
>
> If Not IsMissing(strTemplate) Then
> m_xls.Workbooks.Add strTemplate
> Else
> m_xls.Workbooks.Add
> End If
>
> If Not IsMissing(strDataPage) Then
> Set xlsSheet = m_xls.Worksheets(strDataPage)
> Else
> Set xlsSheet = m_xls.ActiveSheet
> End If
>
> With xlsSheet
> intRow = intStartRow
> Do Until rst.EOF
> For i = intStartCol To _
> (intStartCol + rst.Fields.Count - 1)
> .Cells(intRow, i).Value = rst.Fields _
> (i - intStartCol)
> Next i
> rst.MoveNext
> intRow = intRow + 1
> Loop
> End With
>
> xlsSheet.Visible = True
> m_xls.Visible = True
> Set objExcel = m_xls
> ExportToTemplate = True
> End If
> End If
>
>End Function
>
>Function OpenExcel()
>
>On Error Resume Next
>
> Set m_xls = GetObject(, "Excel.Application")
> If m_xls Is Nothing Then
> Set m_xls = New Excel.Application
> End If
>
> If m_xls Is Nothing Then
> MsgBox "Can't Create Excel Object"
> OpenExcel = False
> Else
> OpenExcel = True
> End If
>
> DoEvents
>
>End Function
>
>~~~~~~~~~EndCode~~~~~~~~~~
>
>Best,
>
>Bob
>
>
>
>>From: "Lesley Schulz" <lesley.schulz@h...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] RE: Exporting to excel template
>>Date: Mon, 23 Dec 2002 17:36:50
>>
>>I have an excel template that I want the data to dump to. Is that
>>possible?
>>
>> > DoCmd.OutputTo acOutputQuery,"QueryName", acFormatXLS, , True
>>
>>
>>Steve K
>>
>>
>>-----Original Message-----
>>From: Gerald, Rand [mailto:RGerald@u...]
>>Sent: 20 December 2002 19:22
>>To: Access
>>Subject: [access] RE: Exporting to excel template
>>Importance: High
>>
>>
>>One problem with the DoCmd.TransferSpreadsheet command is that it only
>>accepts TABLES as data sources. It will not work with QUERIES. The only
>>way around it is to use the query to create a temporary table and then use
>>DoCmd.TransferSpreadsheet with the temporary table.
>>
>>Rand E Gerald
>>Database Specialist
>>Information Services / Operations
>>Bahá'í National Office
>>1233 Central St.
>>Evanston IL 60201
>>(xxx) xxx-xxxx
>>
>>-----Original Message-----
>>From: Steve Klein [mailto:Stephen@K...]
>>Sent: Friday, December 20, 2002 12:37
>>To: Access
>>Subject: [access] RE: Exporting to excel template
>>
>>Are you tryng to output to an excel spreadsheet or to a specific template?
>>
>>It is usually easier to work with the underlying query than outputting the
>>report
>>
>>Steve K
>>
>>
>>-----Original Message-----
>>From: Lesley Schulz [mailto:lesley.schulz@h...]
>>Sent: 20 December 2002 16:53
>>To: Access
>>Subject: [access] Exporting to excel template
>>
>>
>>I have a report in Access 97 that I want to export to an excel template.
>>On a form the user can press a button to export the document. How would I
>>program it to export that report to an excel template?
>>
>>Can anyone help?
>>
>>Thanks
>>Lesley Schulz
>>
>>
>>
>>
>>
>
>
>_________________________________________________________________
>The new MSN 8: smart spam protection and 3 months FREE*.
>http://join.msn.com/?page=features/junkmail&xAPID=42&PS=47575&PI=7324&DI=7474&SU=
>http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_smartspamprotection_3mf
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to
>unsubscribe send a blank email to
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 3 months FREE*.
http://join.msn.com/?page=features/virus&xAPID=42&PS=47575&PI=7324&DI=7474&SU=
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_virusprotection_3mf
Message #10 by "Bob Bedell" <bobbedell15@m...> on Tue, 24 Dec 2002 07:04:21
|
|
Found it. Helen Feddema's web site should be titled "everything you
ever wanted to know about Office automation and didn't think to ask."
To really see how to control export to an Excel template on a cell-by-cell
basis, check out:
http://www.helenfeddema.com/access.htm
See Archon article #66 - Working with the Excel Object Model. The zip
file contains two Excel template files. One is fairly basic, but the other
is a complex form template.
|
|
|
View Cart
