By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,739 Members | 1,506 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,739 IT Pros & Developers. It's quick & easy.

Problem with formulas in Excel using TransferSpreadsheet. Can't use OutputTo.

P: n/a
I've hit a bit of a brick wall here, and could use some advice. I have
an Access application whose output is a four-tab Excel spreadsheet
where three of the four tabs are the result of database queries.

A number of fields in the queries are designed to become formulae in
Excel after the export has taken place (and they need to be in Excel
rather than Access). I need to use the TransferSpreadsheet Method
rather than the OutputTo to enable me to have each of queries on its
own tab within the spreadsheet, but TransferSpreadsheet formats the
formulae as text, so that the resultant cell value in Excel is
'=Function(etc.).

Is there an alternative transfer method that would allow me to combine
the functionality of OutputTo that allows me to export a formula, and
the functionality of TransferSpreadsheet that allows me to export
multiple queries to multiple tabs within the same Excel file?

Thanks...!
Mark
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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").PasteSpecial, 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 TransferSpreadsheet, sort of, but
with transferspreadsheet 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.Connection, RS As New ADODB.Recordset

Set DB = CurrentDb

strSourcePath = Left(DB.Name, Len(DB.Name) - Len(Dir(DB.Name)))
strSourcePath = strSourcePath & "yourExcelFile.xls;"
RS.CursorLocation = adUseClient
cn.Mode = adModeReadWrite
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strSourcePath & _
"Extended Properties=""Excel 8.0;HDR=NO;"""

'populate NcalOrganData Sheet
Set RS1 = DB.OpenRecordset("tbl1")
j = 2
DoEvents
Do While Not RS1.EOF
strSql = "SELECT * FROM [Sheet1$A" & j & ":U" & j & "]"
RS.Open strSql, cn, adOpenDynamic, adLockPessimistic
For k = 0 To RS1.Fields.Count - 1
RS(k) = RS1(k)
Next
RS.Update
RS.Close
j = j + 1
RS1.MoveNext
t = t + 1
RetVal = SysCmd(acSysCmdSetStatus, 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!
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.