Jump to content

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


RickB75
 Share

Recommended Posts

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

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

If you want to use AutoIt run:

$oExcel.ActiveWindow.DisplayFormulas = True

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

 

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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".

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...