Export to Excel

[jeffgeorge]

I'm currently exporting a form to Excel. Because there are controls
and totals in the header, I first have a button for users to convert
to a datasheet. Then I use the automated quick office Export to Excel
Link then I have a button which returns the user to a form view. It's
a 3 step process with the user needing to Save As in Excel if they
wish to keep the spreadsheet.

Are there any other easier options? It seems I should be able to
program those 3 steps, and perhaps have some code which will name the
spreadsheet something other than the form name. The one other fix so
far has been putting the totals and controls in the footer, then when
exporting, those columns are all the way to the right.

[Rich P]

It is completely doable to achieve the functionality you are looking
for. But first understand that big software companies like Microsoft,
Lotus Notes, etc provide several utilities to add functionality to an
application without having to write code. This is kind of generic. To
achieve the customized functionality you are looking for you have to
write code. That is the catch.

I pass data from Access to Excel on a daily basis. I will share the
techniques and code that I use. First, I create the Excel file and
format it exactly the way I want it, column sizes, fonts, bolding, excel
headers/footers, if I want to freeze a row, charts, etc. This is a
standard .xls file but I will use it as a sort of template (not .xlt)
file. From Access I will write data to exact cells/cell ranges in Excel
using COM based ADO (as opposed to ADO.Net). Note: for ADO to work
correctly with Excel (97 and on up) you have to pre-populate your data
cells with fake data which will get overwritten with the data from ADO
(and you just write Nulls to the cells that have fake data which did not
receive real data). In Excel you can further format your data using
Excel VBA (Excel VBA is more extensive than Access VBA). Also, to use
com based ADO you need to load Mdac2.5 and Mdac2.6 on each workstation
that will be using your app(s). Mdac2.5 contains the Jet Engine which
interfaces with ADO, Mdac2.6 is the updated version of ADO for win2k and
on up. You can get Mdac from the Microsoft website. Oh, and you can
further manipulate Excel from Access by using a coding technique called
Automation. I will demonstrate.

Here is my code from Access (with a reference to Microsoft ActiveX Data
Objects 2.6 - Mdac2.6 in Tools/References). An explanation follows
after the code.

'************************************************* **
Sub DataToExcel()
Dim cn As New ADODB.Connection, RS As New ADODB.Recordset
Dim RS1 As DAO.Recordset, strSql As String
Dim xlObj As Excel.Application, wkbk As Excel.Workbook
Dim SourceDoc As String, SourcePath As String
Dim i As Integer, j As Integer

SourcePath = Left(CurrentDb.Name, Len(CurrentDb.Name) -
Len(Dir(CurrentDb.Name)))
SourceDoc = SourcePath & "Test1.xls"

Set xlObj = CreateObject("Excel.Application")
xlObj.DisplayAlerts = False
xlObj.WindowState = xlMaximized
Set wkbk = xlObj.Workbooks.Open(SourceDoc)
SourceDoc = "Test1newName.xls"
SourceDoc = SourcePath & "newfolder\" & SourceDoc
wkbk.SaveAs SourceDoc
wkbk.Close
xlObj.Quit
Set xlObj = Nothing

RS.CursorLocation = adUseClient
cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceDoc & _
";Extended Properties=""Excel 8.0;HDR=NO;"""
Set RS1 = CurrentDB.OpenRecordset("tbl1")
j = 2
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":H" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For i = 0 to RS1.Fields.Count - 1
RS(i) = RS1(i)
Next
RS.Update
RS.Close
RS1.MoveNext
j = j + 1
Loop

Set xlObj = CreateObject("Excel.Application")
xlObj.DisplayAlerts = False
xlObj.WindowState = xlMaximized
Set wkbk = xlObj.Workbooks.Open(SourceDoc)
wkbk.Application.Run "ExcelMacro1", arg1, arg2
wkbk.Save
xlObj.Visible = True
Set xlObj = Nothing
End Sub
'************************************************* *******

Here I have my template Excel File Test1.xls. I first save it as
Test1newName.xls and to another directory (folder). The idea here is to
preserve the original Test1.xls. Then I make an ADO connection to the
new Excel file and write data to is in the Do Loop to the range from
columns A through H with the rows identified by variable j. Note: you
can only write one row at a time with ADO so I have to keep creating
strSql for a new row (each row is noted by j - but it is still very fast
- very fast). So I update RS (the ADO Recordset) and close it for each
iteration of RS1 (the DAO recordset). After I finish writing my data I
can further format Excel by invoking a Macro I wrote in Excel and send
some arguments (using automation). Plus, the automation routine will
Open Excel (xlObj.Visible = True) for your viewing pleasure.

My object for sharing all this here is not just to show off programming
and so forth, but to demonstrate that your objective is doable and here
is a sample of what it takes. You can add additional functionality to
this with ActiveX Dlls (pass lots of parameters to Excel this way), but
I will save that code for later (it's less fun :(.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Scott Belviso]

Following is an example of what I do using ADO/ASP pages. When the
user executes this ASP page it will automatically create a spreadsheet
on the users PC by the name of donations_totals.xls. I have a
uniquename variable included so that you can use that in the name to
make it different each time. The spreadsheet comes out fully formated
with column widths and headers. If you need more information send me
an email to belvisos@ yahoo.com (remove space before yahoo).


<%
Dim rs, conn, sql

Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
server.mappath("Master.mdb") & ";"
%>
<%
sql = "SELECT [Master contacts].[Last name], [Master
contacts].[First name], Sum(Donors.Amount) AS [Total Donation] FROM
[Master contacts] INNER JOIN Donors ON [Master contacts].ID =
Donors.MasterID GROUP BY [Master contacts].[Last name], [Master
contacts].[First name], Donors.MasterID;"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3

uniqueName = replace(Now()," ","")
uniqueName = replace(uniqueName,"/","")
uniqueName = replace(uniqueName,":","")

myfilename = uniqueName

response.AddHeader "Content-Disposition", "attachment;
filename=Donation_Totals.xls"
response.ContentType="application/vnd.ms-excel"
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial"
COLOR=#000000><CAPTION><B>Donations Total</B></CAPTION></FONT>

<THEAD>
<TR>
<TH>Last name</TH>
<TH>First name</TH>
<TH>Total Donation</TH>

</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD><%=Server.HTMLEncode(rs.Fields("Last name").Value)%></TD>
<TD><%=Server.HTMLEncode(rs.Fields("First name").Value)%></TD>
<TD><%=Server.HTMLEncode(rs.Fields(2).Value)%></TD>

</TR>
<%
rs.MoveNext
loop
rs.Close
conn.Close
set rs = Nothing
set conn = Nothing
%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>

On 18 Feb 2004 07:15:20 -0800, ki*******@aol.com (jeffgeorge) wrotE:

I'm currently exporting a form to Excel. Because there are controls
and totals in the header, I first have a button for users to convert
to a datasheet. Then I use the automated quick office Export to Excel
Link then I have a button which returns the user to a form view. It's
a 3 step process with the user needing to Save As in Excel if they
wish to keep the spreadsheet.

Are there any other easier options? It seems I should be able to
program those 3 steps, and perhaps have some code which will name the
spreadsheet something other than the form name. The one other fix so
far has been putting the totals and controls in the footer, then when
exporting, those columns are all the way to the right.