473,287 Members | 1,946 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,287 software developers and data experts.

Export to Excel

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.
Nov 12 '05 #1
2 9457
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!
Nov 12 '05 #2

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.


Nov 12 '05 #3

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

Similar topics

1
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table....
5
by: Maria L. | last post by:
Hi, I need to export the content of a DataGrid (in Windows application in C#), into an Excel spreadsheet. Anyone knows how to do this? Any code snippets would help! thanks a lot, Maria
2
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType =...
6
by: Elena | last post by:
I'm trying to export data to an Excel worksheet. I can export the data in the cell values perfectly. I need the code to change a header and footer for the worksheet, not for the columns. Is...
13
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"...
5
by: Simon | last post by:
Dear reader, With the export command you can export a query to Excel. By activate this command a form pop's up with the following text:
1
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
2
hemantbasva
by: hemantbasva | last post by:
Note We need to have a template on server for generating report in multiple sheet as we do not had msoffice on server moreover this require a batch job to delete excel file created by the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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...
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)...

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.