RickB75 Posted November 4, 2013 Share Posted November 4, 2013 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 More sharing options...
water Posted November 4, 2013 Share Posted November 4, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
l3ill Posted November 4, 2013 Share Posted November 4, 2013 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 My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
RickB75 Posted November 5, 2013 Author Share Posted November 5, 2013 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 More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted November 5, 2013 Author Share Posted November 5, 2013 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 More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted November 5, 2013 Author Share Posted November 5, 2013 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).SelectEnd WithApplication.CutCopyMode = FalseEnd Sub Link to comment Share on other sites More sharing options...
RickB75 Posted November 5, 2013 Author Share Posted November 5, 2013 I just saw your recent post Water. I'll give post #2 a quick try. Link to comment Share on other sites More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
l3ill Posted November 5, 2013 Share Posted November 5, 2013 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 My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
RickB75 Posted November 5, 2013 Author Share Posted November 5, 2013 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 More sharing options...
l3ill Posted November 5, 2013 Share Posted November 5, 2013 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 My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 Looks like Water is gone for the night ... Correct. I have been away for some heavy beer drinking 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
RickB75 Posted November 6, 2013 Author Share Posted November 6, 2013 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. Link to comment Share on other sites More sharing options...
RickB75 Posted November 7, 2013 Author Share Posted November 7, 2013 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 More sharing options...
l3ill Posted November 7, 2013 Share Posted November 7, 2013 Glad you got it working! Sometimes the forest gets in the way.....;-) My Contributions... SnippetBrowser NewSciTE PathFinder Text File Manipulation FTP Connection Tester / INI File - Read, Write, Save & Load Example Link to comment Share on other sites More sharing options...
water Posted November 7, 2013 Share Posted November 7, 2013 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 - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now