Tigerweld Posted August 3, 2017 Share Posted August 3, 2017 I have a csv file I need to format column A and G as number and save the change and I need to add this to a scheduled task on a server so I need to be able to do this without someone having to do this manually. I've looked around and came up with this, but something tells me I'm way off. #include <Excel.au3> $oExcel = _Excel_Open() $oExcel.Range("A1").Select $oWorkbook = "c:\temp\Cards.csv" $oExcel.Selection.NumberFormat = "$#,##0.00" Link to comment Share on other sites More sharing options...
spudw2k Posted August 4, 2017 Share Posted August 4, 2017 Here's a way/demo: #include <Excel.au3> $oExcel = _Excel_Open(False) ;Open Excel hidden $oWorkbook = _Excel_BookOpenText($oExcel, "C:\temp\cards.csv") ;Open CSV $oWorkbook.Worksheets(1).Columns("A").NumberFormat = "#,##0.00" ;Set Column A Number Format $oWorkbook.Worksheets(1).Columns("G").NumberFormat = "#,##0.00" ;Set Column G Number Format _Excel_BookSave($oWorkbook) ;Save CSV _Excel_Close($oExcel) ;Close Excel Tigerweld 1 Spoiler Things I've Made: Always On Top Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Junos Configuration Explorer ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System Scan Report Tool ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Link to comment Share on other sites More sharing options...
Tigerweld Posted August 4, 2017 Author Share Posted August 4, 2017 thanks spudw2k. That works great! Link to comment Share on other sites More sharing options...
junkew Posted August 6, 2017 Share Posted August 6, 2017 Excel installed on a server? Not sure but you cannot assume office is installed on all servers. FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets Link to comment Share on other sites More sharing options...
spudw2k Posted August 7, 2017 Share Posted August 7, 2017 7 hours ago, junkew said: Excel installed on a server? I was going to bring that up myself. Not to say it won't work, but having Office installed on a server increases security risk...only more-so than a desktop because it is likely the server is providing other services than a mere desktop environment. You might consider, if your environment allows it, running the task on a workstation or less critical machine that can then copy the result CSV to the server. Spoiler Things I've Made: Always On Top Tool ◊ AU History ◊ Deck of Cards ◊ HideIt ◊ ICU ◊ Icon Freezer ◊ Ipod Ejector ◊ Junos Configuration Explorer ◊ Link Downloader ◊ MD5 Folder Enumerator ◊ PassGen ◊ Ping Tool ◊ Quick NIC ◊ Read OCR ◊ RemoteIT ◊ SchTasksGui ◊ SpyCam ◊ System Scan Report Tool ◊ System UpTime ◊ Transparency Machine ◊ VMWare ESX BuilderMisc Code Snippets: ADODB Example ◊ CheckHover ◊ Detect SafeMode ◊ DynEnumArray ◊ GetNetStatData ◊ HashArray ◊ IsBetweenDates ◊ Local Admins ◊ Make Choice ◊ Recursive File List ◊ Remove Sizebox Style ◊ Retrieve PNPDeviceID ◊ Retreive SysListView32 Contents ◊ Set IE Homepage ◊ Tickle Expired Password ◊ Transpose ArrayProjects: Drive Space Usage GUI ◊ LEDkIT ◊ Plasma_kIt ◊ Scan Engine Builder ◊ SpeeDBurner ◊ SubnetCalcCool Stuff: AutoItObject UDF ◊ Extract Icon From Proc ◊ GuiCtrlFontRotate ◊ Hex Edit Funcs ◊ Run binary ◊ Service_UDF Link to comment Share on other sites More sharing options...
water Posted August 7, 2017 Share Posted August 7, 2017 To avoid Excel I suggest to read the CSV file as a text file into an array, manually format the numbers using Stringformat and then re-create the CSV file. spudw2k 1 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