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!