export query with formulas to excel

Is there a way to export my Access query to Excel 2003 while keeping the 
formulas in Excel.  For example, in my Access query I have a field called 
"Other" which is defined as the following:

Other: [DiffTotal]-[BudTo]

[DiffTotal] and [BudTo] are "real" fields in the query and become columns in 
Excel.  Instead of having the value of "Other" appear in Excel, I'd like the 
"Other" column to have the formula where, for example in row 1, it could look 
like "=A3-A2."

Thanks,

~Mark
0
Utf
4/19/2010 5:23:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
3584 Views

Similar Articles

[PageSpeed] 0

When Access exports to Excel, it exports the data, not the formula.

If you are willing to undertake the work of automating Excel, you can use 
Access to "push" a formula into Excel.

If you want [DiffTotal] and [BudTo] amounts to go to Excel, send them, then 
in Excel, create your formula.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"mark" <mark@discussions.microsoft.com> wrote in message 
news:497A67C6-21F5-4D2E-A21E-F69EE3E8EB55@microsoft.com...
> Is there a way to export my Access query to Excel 2003 while keeping the
> formulas in Excel.  For example, in my Access query I have a field called
> "Other" which is defined as the following:
>
> Other: [DiffTotal]-[BudTo]
>
> [DiffTotal] and [BudTo] are "real" fields in the query and become columns 
> in
> Excel.  Instead of having the value of "Other" appear in Excel, I'd like 
> the
> "Other" column to have the formula where, for example in row 1, it could 
> look
> like "=A3-A2."
>
> Thanks,
>
> ~Mark 


0
Jeff
4/19/2010 5:35:00 PM
Hi Mark,

It is possible, but the only thing I can come up with is to export the data 
without the calculated field to Excel, and then using the excel application 
object in Access VBA to open the created workbook, add a column and fill it 
with the formula.
-- 
Kind regards
Noëlla


"mark" wrote:

> Is there a way to export my Access query to Excel 2003 while keeping the 
> formulas in Excel.  For example, in my Access query I have a field called 
> "Other" which is defined as the following:
> 
> Other: [DiffTotal]-[BudTo]
> 
> [DiffTotal] and [BudTo] are "real" fields in the query and become columns in 
> Excel.  Instead of having the value of "Other" appear in Excel, I'd like the 
> "Other" column to have the formula where, for example in row 1, it could look 
> like "=A3-A2."
> 
> Thanks,
> 
> ~Mark
0
Utf
4/19/2010 5:55:01 PM
Reply:

Similar Artilces:

Formula #47
i am trying to work out a formula to: calculate a cell but only if another cell is of greater value than another,(if not then value in cell = 0) then if the value is greater and the date is under 365 days calculates at %50 rate and if its over, calculate at %25 rate. can i do this from one cell/one formula Jo Certainly, check out the IF function and you can nest these like so =IF(Test,Value_If_True,IF(Test,Value_if_True,Value_If_False)) This is only set out to show the approx syntax. If you want an exact formula then try and let us now the cells you are using, where we get the...

why wont an excel doc open with new msn office
i have just got a new comp with the new microsoft woeks on it, word doc if differant, and a few other things but an excel doc was sent to me an it will not open it ???????? Hi MS Works is a 'light' version of Office, containing a 'light' version of Excel and a 'light' version of Word. You cannot open Excel or Word files with Works. You'll have to get the files saved as .csv or .txt or a Works file to be able to open them with Works. Andy. "peterk4" <peterk4@discussions.microsoft.com> wrote in message news:53597904-F529-4E67-BC56-D957C375779...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

formula to long
is there a way to get around the error "formula to long" Dickey You know the simple answer to this ;-) You will need to look at the functions you are using to see if it can be shortened, for example multiple IFs may be shortened by using a table and VLOOKUPs for example. Without further info it's difficult to diagnose -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Dickey" <Dickey@discussions.microsoft.com> wrote in message news:6E98B6AA-4BBA-4BA2-8F43-6EBD6F9597D8@microsoft.com... > is there a wa...

Excel 2002 Create Workbook (merge/reformat)?
I have 12 seperate spreadsheets I want to combine into 1 workbook (12 tabs). Any other alternative ways to do this other than copy and paste? Need to reformat "most" of the worksheets, I assume this can be done if all 12 tabs are selected. I may have to do this several times otherwise I would just copy and paste. Each spreadsheet has an average of 3k-4k transactions. You can open the workbook, ctrl-click on the worksheet tab and drag that worksheet into another workbook. (Then close that "sending" workbook and open the next...) ~Dave~ wrote: > > I have 1...

Excel 97-remove link but keep data
Hi guys, I have a pre-existing excel 97 s/s which has links to other s/s's. I would like to keep the data only (like paste-special, values) and remove the linked reference from showing in the current s/s. Any ideas how to do this instead of me changing each linked reference? Thanks in advance! search for .xls or [ or ] to see if you can find the formulas that contain links to other workbooks. Van wrote: > > Hi guys, > > I have a pre-existing excel 97 s/s which has links to other s/s's. I would > like to keep the data only (like paste-special, values) and rem...

Excel file takes a long time to save
I have a 1,000 x 15 straight worksheet with no formulas - about 100K. I went to auto-format a pattern, but didn't select the area and if formatted the entire spreadsheet. It's since gone from 5 seconds to 60 seconds per save and I can't reverse this, even by changing the auto-format setting to "none." I'm backed-up, but the current version is about three hours ahead and it will take longer to retrace the work by hand. Any suggestions? Thanks, Ben Try resetting the last used cell. Debra Dalgleish has some techniques at: http://www.contextures.com/xlfaqApp.html#Un...

Formula Question #3
I have a spreadsheet that I'm working on that tracks employee attendance. A sample is included below: A B C D 1 Name 06/07/04 Final Warning Attendance 2 Name 08/21/03 Warning Tardy 3 Name 12/04/02 Warning Attendance 4 Name 09/16/02 Counsel Tardy 5 Name 08/05/02 Counsel Attendance Is there a formula that will tell me how many instances an employee was on an Attendance or Tardy disciplinary step of Final Warning, Warning ...

Formula In A Query
Hi I have a query in which I am trying to create a formula to check if the date in a record is in the same month as the current date, if this is true to return the months name, if not to return another value. The formula does not fall over but only returns the OR value. The OR value is meaningless at the moment as I am just seeing if the formula is actually doing something Could someone have a look at this MonthNo: IIf([Date SubmittedtoLab]=Month(Date()),MonthName(Month([Date SubmittedtoLab])),5) Thanks Richard Hi Richard, You can use the Immediate Window (open with <Ctrl><...

Using Excel 2000 as Data source for Word 2000 document
Problem using Mail Merge using Word 2000 & Excel 2000. After numerous problems - Word failed to connect to Data Souce file then it would & then it wouldn't and so on - reasons which are unknown! The 106 records in 19 fields from the Excel Data Source are only transferring the information for the first 104 records. I am looking to add further records but obviously need to overcome this problem. Hi Malcolm- Obvious, perhaps, but are you certain that the records are in consecutive rows & no vacant columns separating the fields of data? Does the data range contain any Merge...

Excel design questions
Hi, I'm a new to excel. I'm assigned task to perform creating charts based on the following data. I explored little bit from web about the capabilities of excel, but few areas I'm not still quite sure about how to get it working. - Is it possible to have advanced filter to automatically refresh if the data is changed on which the filter is acting on?? Do I need to write maro for this?? - If i have a column with data (different types of tree names, ex: Apple, Apple, Maple, Grape,Grape..etc - name is repeatable). Can I uniquely get the name of all the different tree names automati...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

Excel 2000 #9
Is there a way to change the date format in footers and headers.?.these two items do not seem to pick up the formatting specified for the individual cells. David H. David Excel in any version uses the Short Date format from Windows Regional settings in headers/footers. You can make some minor changes in Regional settings but not too much. How about a macro to add the date in any format you choose? Sub DateInFooter() With ActiveSheet .PageSetup.CenterFooter = Format(Date, "dddd, mmmm-dd-yyyy") End With End Sub Gord Dibben MS Excel MVP On Tue, 5 Feb 2008...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

Creating a query out of another query
Hi Guys, I have a query looking like that: field1 field2 field3 field4 field5 field6 Data 1 Test 6 Poly 8 Data 5 Poly 10 I need a query from this query like this: Field 1 Field 2 Data 1 Test 6 Poly 8 Data 5 Poly 10 I'll really appreciate if someone can help me with this one Thanks This should do it -- SELECT Field1 AS Field_1, Field2 AS Field_2 FROM YourTable WHERE Field1 ...

Visio2k7 has two PDF export methods
I have a floorplan diagram that is 20"x20". On the diagram, there is metadata for the shapes of the offices that tells you the person's name, phone #, etc. When I use the "Publish as PDF or XPS", the Image is exported properly, as shown in the link below: http://img527.imageshack.us/img527/2676/error1kn6.jpg But none of the metadata comes across - and there's no option to include it either that I can find. However, if you use the "Convert to Adobe PDF" option, you get the opposite: http://img503.imageshack.us/img503/2893/error2ou8.jpg The i...

Can I export Distribution Lists?
A different department has asked us for a copy of all our distribution lists, members of those lists, their smtp e-mail addresses, x500 addresses, as well as group owners and who has rights to send to thos lists. How do I get that? We have about 100 distribution lists each with about 100 users. We are running Windows 2000 and Exchange 2000. Thanks Hi Boe, - Scripting this would produce much more readable output. I was able to modify a script I wrote a while back for general use. Posted to blog: http://www.suneja.com/blog/2006/02/script-list-distribution-groups.html It doesn't hav...

Multi-User Excel File on a Network Drive
How can I make an Excel file accessible to multiple users concurrently? With all able to make and save changes to the same file. Thanks, Mike "Mike" wrote > How can I make an Excel file accessible to multiple > users concurrently? With all able to make and save > changes to the same file. Tools / Share Workbook Jordon ...

Inserting Rows in Excel
I have a spreadsheet with a unique identifier for each customer, Customer Information File (CIF). Whenever this number changes, I want to automatically insert a blank row. This will merely making the the spreadsheet more pleasing to the eye to read. Keep in mind, there may not be a break in CIF for 1 to 15 lines, but whenever this CIF number changes, I want a new blank row. Any help would be greatly appreciated!! David David Macro solution OK? Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With ...

I would like some formula for students score
Happy New Year and Sawasdee from Thailand. I have a file that I would like anyone who can help me to solve my problem. Please d/l my file and help me please.... Thank you Attachment filename: mark.xls Download attachment: http://www.excelforum.com/attachment.php?postid=404449 --- Message posted from http://www.ExcelForum.com/ > Happy New Year and Sawasdee from Thailand. I have a file that I would > like anyone who can help me to solve my problem. Please d/l my file > and help me please.... Thank you Not saying you would purposely send a virus, ...

Use Excel to update Outlook address book
hi all... Is there a way to update outlook address book using excel? Is there a macro available for it? Thank you for your answers Mike ...

Compress an Excel File
I have an excel file that is 36 meg. Do you know how to compress the file? When I zip the file it drops to 3meg so I know it can be compressed. I beleive the file has become large due to the constent changes I need to make to the file. Help Please. Hi Mandy Maybe this will help? http://www.contextures.com/xlfaqApp.html#Unused You can use Rob Bovey's Code cleaner to clean up your code http://www.appspro.com/utilities/utilities.asp -- Regards Ron de Bruin http://www.rondebruin.nl "Mandy" <mangelo@arbys.com> wrote in message news:42c401c47fa1$ddc405d0$a401280a@...

Tracking an email when sent directly from Excel
I often use the feature File>Send To>Mail Reciepient (as attachment) when I'm working in an Excel Workbook. However I can never find any record of this in Outlook, or anywhere else. Is there a way so this gets recorded in Outlook, or can you tell me where I can find some record of the email and its contents. Thank you, On May 18, 7:23=A0pm, Rob <robfl...@sbcglobal.net> wrote: > I often use the feature File>Send To>Mail Reciepient (as attachment) > when I'm working in an Excel Workbook. =A0However I can never find any > record of this in Outloo...

query help 01-08-08
My main table is called tblMain and this has an abundance of information in there. Each record has an ID called MainID. I need to do a 'search type query' on 4 fields . I have a primary business unit which each record must fill in (PrimaryBU). I then have another 3 related business unit fields (RelatedBU1, RelatedBU2, RelatedBU3). All 4 of these fields are linked to a lookup table tblBusinessUnit. I need to write a query that will prompt the user to type a business unit (I know how to do this part) and will then show any record which has that business unit in any of those 4 fie...

Diable excel macro for DATES
Hi, everytime i copy and paste from a website in this format XX/YY excel sees it as a DATE. how can i disable this feature..I just want t paste it as its raw form of XX/YY. When i cut and paste it in my exce and try to change the format by using TExt. it comes up with rubbis numbers. Thank -- Message posted from http://www.ExcelForum.com try formatting, as text, before you import -- Don Guillett SalesAid Software donaldb@281.com "evo >" <<evo.18vufd@excelforum-nospam.com> wrote in message news:evo.18vufd@excelforum-nospam.com... > Hi, > > everytime i copy ...