I have a query, the results from which I copy into xls. In the xls there are columns the users will use to hand key additional data. That data needs to be summed. Is there a way to send the formula along with my copy and paste from access?
I have tried =B1+B2
but it requires going into the field in xls then hitting enter for xls to perform the calculation. Is there a way force the calculation?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
when you are exporting into Excel the 'formulas' are being passed as literal text ... you would need to write automation code to get Excel to chnage them into formulas. Not so hard if you know the name and location of the file you just exported to ...
Public Sub ExportXL(FullPath As String)
Dim xlApp As Excel.Application
Dim xlWB as Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlCell As Excel.Range
Dim xlRng as Excel.Range
Dim strAddress As String
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(FullP
Set xlWS = xlWB.Worksheets(1)
strAddress = xlWS.UsedRange.Address
strAddress = Right(strAddress,Len(strAd
strAddress = "D2:D" & strAddress
Set xlRng = xlWS.Range(strAddress)
For Each xlCell In xlRng
xlCell.Formula = xlCell.Value
Next
xlWB.Save
Set xlCell = Nothing
Set xlRng = Nothing
Set xlWS = Nothing
xlWB.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
Steve
jppinto
The formulas are based on fields that will be manually entered after the the xls is created
Steve
So I have copy and pasted from Access to Excel several sheets... Could I run a macro that would check every sheet for formulas and force them to calculate? Is that kinda where your code is going except you know exactly what cells have formulas?
My code was intended to export the query directly into a spreadsheet, no copy and paste necessary ... and yes ... it wad based on the presumption that you knew where the formulas would be. If all you need to do is have a macro look at all the worksheets in your workbook and if the first two characters are '= and then remove the first character and force a recalc ...
Public Sub FixAndCalculate(FullPath As String)
Dim xlApp As Excel.Application
Dim xlWB as Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlCell As Excel.Range
Dim xlRng as Excel.Range
Dim strAddress As String
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open(FullP
For Each xlWS in xlWB
Set xlRng = xlWS.UsedRange.Address
For Each xlCell In xlRng
If Left(xlCell.Value & " ", 2) = "'=" Then
xlCell.Formula = Mid(xlCell.Value, 1)
End If
Next
xlWS.Calculate
Next
xlWB.Save
Set xlCell = Nothing
Set xlRng = Nothing
Set xlWS = Nothing
xlWB.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub
Business Accounts
Answer for Membership
by: GRayLPosted on 2008-05-19 at 12:16:52ID: 21600673
In Excel, pressing Shift+F9 will recalculate the current worksheet