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

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

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
1 6147
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: David Alliet | last post by:
Hello, I'm having a bit of a problem with ASP and Excel. A client has developed his own program, which calculates alot of financial stuff, in excel. I'm doing his websites and he has requested...
1
by: O'Donnell Tribunal | last post by:
Hello Everybody, I am using the TransferSpreadsheet method to import spreadsheets for processing. say my code looks like this: DoCmd.TransferSpreadsheet acImport, 0, _ "tbltempImport1", ""...
2
by: Ian | last post by:
I have an Access97 database and I am trying to create an Excel sheet from a report within this database. I have the following code on the On Click event of a button on a form: ...
7
by: Keon | last post by:
Hoi, I'm using a database with alot of records in 1 table (more than 3000). If i want to export this table to excel i only get it till record 2385. Do someone know how i can solve this...
7
by: MLH | last post by:
A97 has menu options that support exporting table data to ms excel data file format. Is this easily implemented from within code? Any examples? I looked in A97 HELP for the TransferSpreadsheet...
2
by: Steve Chatham | last post by:
I use the following code: Private Sub RbtnExport_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RbtnExport.SelectedIndexChanged Dim sFile As String =...
2
by: Matt | last post by:
Hi All, I am currently outputting the results of a query to an Excel Workbook using the following line of code: DoCmd.OutputTo acOutputQuery, queryName, acFormatXLS, , True What this is...
3
by: JHNielson | last post by:
I am having quite the unique problem trying to Export to Excel and I need ot find a solution within 3 hours. -- PLEASE HELP! The system exports a file from the application that the users can make...
4
by: John Brock | last post by:
I have a .NET application that, among other things, creates Excel workbooks, and I have run into a very strange problem involving formulas on one worksheet that reference values on another...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

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.