473,225 Members | 1,259 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,225 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 6142
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...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

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.