Yes. There are 2 other ways to pass data to Excel from Access. Each of
these methods is more sophisticated than Output or Transfer methods but
gives you more control.
The first method is to copy the data to memory/the clipboard using API
code. The specific API code for this is located at:
http://www.mvps.org/access/api/api0049.htm
What you do is to loop through the table/query and write the contents of
each field to a string. You delimit each field with a tab char --
chr(9) and delimit each row with a vbCRLF. Excel is nothing more that
one giant string (with thousands of properties). You then have to use
automation to access the specific workbook/worksheet/Range to copy the
contents of the clipboard to. The only catch with this method is that
you can only select one Excel cell as the starting point to copy your
data to. Like if you have 10 fields and 10 rows of data, you could
select Sheets("Sheet1" ).Range("A1").P asteSpecial, or Range("H25")
and the data will be copies as 10 columns and 10 rows starting at the
select Range. You can do that with TransferSpreads heet, sort of, but
with transferspreads heet you need a new sheet each time. With the
Clipboard method, you can overwrite existing data, or, you just don't
need a new sheet each time.
The other method is to use ADO. With ADO you can designate each cell
that you want to write data to. This is similar to using automation,
sht1.Cells(1,1) , ... but way more efficient and way faster and you don't
need automation. You do not need to instantiate an Excel Object to
write to Excel using ADO.
THe copy to clipboard method is a little easier than ADO. Plus, with
ADO you have to prime the worksheet that will receive the data, put
garbage data in the desired range and overwrite it with ADO.
Here is a sample of ADO code to Excel:
Sub DataToExcelADO( )
Dim strSql As String
Dim DB As Database, RS1 As Recordset
Dim j As Integer, k as integer, t as integer
Dim strSourcePath As String, RetVal As Variant
Dim cn As New ADODB.Connectio n, RS As New ADODB.Recordset
Set DB = CurrentDb
strSourcePath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name )))
strSourcePath = strSourcePath & "yourExcelFile. xls;"
RS.CursorLocati on = adUseClient
cn.Mode = adModeReadWrite
cn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Ex cel 8.0;HDR=NO;"""
'populate NcalOrganData Sheet
Set RS1 = DB.OpenRecordse t("tbl1")
j = 2
DoEvents
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimist ic
For k = 0 To RS1.Fields.Coun t - 1
RS(k) = RS1(k)
Next
RS.Update
RS.Close
j = j + 1
RS1.MoveNext
t = t + 1
RetVal = SysCmd(acSysCmd SetStatus, t)
Loop
RS1.Close
cn.Close
End Sub
With ADO you have to close the ADO recordset for each row in Excel and
then start a new row with a new Sql String. I use the variable j to
count the new row. I start at j = 2 because the 1st row is the field
Names.
As you can see, these methods are a little more of a hassel than Output
and Transfer methods. But you have way more control.
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
