473,849 Members | 1,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with formulas in Excel using TransferSpreads heet. 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 TransferSpreads heet Method
rather than the OutputTo to enable me to have each of queries on its
own tab within the spreadsheet, but TransferSpreads heet 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 TransferSpreads heet that allows me to export
multiple queries to multiple tabs within the same Excel file?

Thanks...!
Mark
Nov 13 '05 #1
1 6200
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!
Nov 13 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2144
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 the ability to do the same thing his program does, but through the website. Because his excel program is a dozen worksheets (all packed with endless rows of formulas) long, i thought it'd use excel as backbone of the webprogram (don't worry, the...
1
4232
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", "" & strFileExtn, False, "Spreadheet1!A1:D30"
2
5695
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: ----------------------------------------------------- On Error GoTo ErrReport
7
5153
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 probleme? To export my tables i use folowing code: StTotaal = stLocatieName & "\Archief\" & Year(Date) & Month(Date) & Day(Date) & "_Gegevens.xls" 'location and name where the table must be saved
7
2926
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 Action but there was no example code (Example appeared at the top next to 'See Also' but it was grayed out).
2
2482
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 = Session("User") & "-Customer List-" & Today() sFile = sFile.Replace("/", "") RbtnExport.Visible = False Select Case RbtnExport.SelectedItem.Value Case "Excel" Response.ContentType = "application/x-msexcel"
2
10892
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 doing is asking the user where to specify where the file
3
4334
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 changes to and then reimport. But now matter what format I specify to export to, when I open the exported files, it says they are in Excel 5.0. I originally used the code: DoCmd.OutputTo acTable, "PRC_REPRT",...
4
1791
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 worksheet. The text I write into, let's say, cell A25 on Sheet1 (using .NET) looks something like this: =VLOOKUP(RC,'Sheet2'!A:X,6,FALSE) On the completed workbook this turns into:
0
9738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10665
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10723
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10349
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9501
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5921
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4544
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4139
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3177
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.