Jump to content
Sign in to follow this  
RickB75

How can I save excel formula as a value in excel?

Recommended Posts

RickB75

Just a quick question guys. How can I save a formula as a value in excel?  I'm using autoit to write an excel sheet for me, writes a formula (VLookup) that looks in different workbooks for values. Well, I would like to email this report but I need the values rendered and not the formula. I found an answer on Microsoft's website here, but they want you to copy and paste the values. There's gotta be an easier way...

I would think it would be something like this I found on this site but I don't know how to write it into my script.

Range("A1:A10").formula="=20*10"
Range("A1:A10").value=range("A1:A10").value

Share this post


Link to post
Share on other sites
water

Can't test at the moment but I think

Range("A1:A10").formula="=20*10"
Range("A1:A10").value=range("A1:A10").formula

should do what you want.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

If you are only interested in copying your values quickly from your "Formula" workbook to one that will be dispersed "w/out the formulas" only the values, the the basic _ExceReadCell and _ExcelWriteCell will work fine.

They do not copy the formulas, only the values.

For $i = 1 To 10 ;Loop
    $sCellValue = _ExcelReadCell($oExcel, $i, 1)
    _ExcelWriteCell($oExcel_0, $sCellValue, $i, 1) ;Write to the Cell
Next

Share this post


Link to post
Share on other sites
RickB75

Sorry for the late reply guys. Thanks for the input. I guess what I was wanting to do was to everything in one motion. Write to the cells as the sheet  is being written and once it gets to the end of the data for that sheet, convert the data / formula in the cells to a value. Kinda of the same way I auto size all the columns.

$oExcel.ActiveSheet.Columns("A:L").AutoFit

I was thinking there was something similar for converting a formula to a value.

Share this post


Link to post
Share on other sites
water

To display the formulas and print them select "Formulas" (or something similar - translated from German) and click "Display Formulas" or use Alt+M, O. Then print the workbook.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

If you want to use AutoIt run:

$oExcel.ActiveWindow.DisplayFormulas = True

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

Sooo.... If I want to hide the formula and just display the value it would be this???

$oExcel.ActiveWindow.DisplayFormulas = False

I guess my question is, after the sheet is written and saved, will I be able to email the spreadsheet to my boss and he can see the values. He doesn't have all the workbooks on his pc that my pc has on it that the vlookup formula is using / referencing.

Share this post


Link to post
Share on other sites
water

 

He doesn't have all the workbooks on his pc that my pc has on it that the vlookup formula is using / referencing.

This changes the whole story! In this case you need to create a new workbook and translate the formulas to values.

Like I've written in post #2.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

It didn't work guys. I did find this info here for using VBA but I don't know how to implement it into my script.

VBA for replacing formulas with calculated values:

Sub Range_Example_1()
With Range("D2:D6")
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End Sub

Share this post


Link to post
Share on other sites
RickB75

I just saw your recent post Water. I'll give post #2 a quick try.

Share this post


Link to post
Share on other sites
water

I can't test at the moment but will post a working example tomorrow.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

RickB75,

  If I understand the scenario correctly, you are a numbers cruncher and have  all of your files tied together over drives and/or networks.

You need to send your boss/colleague a copy of your work and know that they do not have access to your connection sources.

  The ability to copy and "Paste Special" choosing values only is one way.

The other is a script based on the above code which as I mentioned copies only the values and not the Formulas.

Just my two ($0.02) cents....

....I have been wrong before ;-)

Bill

Share this post


Link to post
Share on other sites
RickB75

Billo,

       Your .02 cents is very, very much appreciated!!!  I was thinking that I could use something like This in my Autoit script

$oExcel.ActiveSheet.Columns("J:S").Copy

To copy all the data in those columns. And, use something like this

$oExcel.ActiveSheet.Columns("J:S").Paste

to paste the values only back into the same cells. I've used different variations of this

$oExcel.ActiveSheet.Columns("J:S").PasteSpecial xlPasteValues

but it is not working. I get a syntax error. I don't know the correct syntax to use for the "PasteSpecial".

Share this post


Link to post
Share on other sites
l3ill

Looks like Water is gone for the night but if you want to get a jump on what he is probably going to show you...

Check out his Excel rewrite UDF (located in his sig) especially "_Excel_RangeCopyPaste" 

Allows for several options for pasting (parameters) to include values only.

cya,

Bill

Share this post


Link to post
Share on other sites
water

Looks like Water is gone for the night ...

Correct. I have been away for some heavy beer drinking :D

But I'm sure I can come up with an easy solution in about 12 to 24 hours.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
RickB75

Still not having any luck with this task guys. I guess I'm gonna create loop using _ExcelReadCell and _ExcelWriteCell for the columns with data.

Share this post


Link to post
Share on other sites
RickB75

Guys, I got it working using the Excelreadcell and Excelwritecell. My hesitation prior to using this is that I thought I was going to have to build another loop and make another counter to find those specific cells but I just added those functions into my current loop using the same counter and it works perfect. Immediately after the script writes the VLookup formula, I use the ExcelReadCell and then ExcelWriteCell using the same counter and it writes the correct data into the correct cell without the VLookup formula all in one motion. Thanks for your tips and advice. I guess my approach to fix the issue was completely wrong.  Once again I appreciate all the tips and advice from both of you guys!

Share this post


Link to post
Share on other sites
water

How about this? I've posted two different solutions >here.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×