SkysLastChance Posted October 12, 2021 Posted October 12, 2021 (edited) I am having a a heck of a time trying to get this format changed Appointment Date 10/9/2021 8:05:58AM 10/12/2021 8:06:24AM 10/6/2021 8:19:13AM 10/12/2021 8:23:48AM 10/5/2021 8:27:36AM I want it to look like this. Appointment Date 10/9/2021 10/12/2021 10/6/2021 10/12/2021 10/5/2021 Here is what I have tried. I can't even get it to format manually. to be honest. something about how the time is I am guessing. #include <Excel.au3> Excel() Func Excel() ;Attaches to excel Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.csv)") If FileExists($sExcelFile) Then $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen (*.csv) file. Else MsgBox(0,"No Excel File Found","Please locate excel file and try again") Exit EndIf $oWorkbook.Activesheet.range("A2:A6").NumberFormat = "dd/mm/yyyy" EndFunc I could do this with a formula however, I would rather just format it. I have tried manually doing text to columns and still doesn't change. What am I doing wrong. What is going to be the simplest way to do this? Am I going to have to edit the string and paste it back in? There has to be a simpler way. excel example.csv Edited October 12, 2021 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott
water Posted October 12, 2021 Posted October 12, 2021 Did you have a look at the wiki? More details about formatting can be found here. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.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 (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
SkysLastChance Posted October 12, 2021 Author Posted October 12, 2021 (edited) I did look at the Wiki. I think the problem is there is no space between the time and AM/PM. That seems to fix it. Not sure I can do anything with the time how it is. I am guessing I would have to do some type of search and replace. Kind of annoying to be honest Edited October 12, 2021 by SkysLastChance You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott
kjpolker Posted October 12, 2021 Posted October 12, 2021 (edited) It is because there no space between the AM/PM and the timestamp as you suggested. Are you open to solving this in Excel or only via AutoIt? Do you have a large amount of information and/or worksheets? If wanting to use AutoIt I suggest getting the values of cells with information and inserting a space between the time stamp and AM/PM. This can be done with _Excel_RangeRead() _StringInsert () ;using negative values at the position will insert the string from the right. Luckily AM/PM is always 2 characters. Edited October 12, 2021 by kjpolker SkysLastChance 1
SkysLastChance Posted October 12, 2021 Author Posted October 12, 2021 Yeah, I think it is going to be easier to fix the output of the report in this case instead of trying to work around it. Thanks all. You miss 100% of the shots you don't take. -Wayne Gretzky -Michael Scott
water Posted October 12, 2021 Posted October 12, 2021 What you import from the file is a string, not a date. So you need function to convert this string. Something like this. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.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 (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
Solution Subz Posted October 13, 2021 Solution Posted October 13, 2021 You could just replace "AM" with " AM" as well as the PM and then format, example: #include <Excel.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\excel example.csv") $oWorkbook.ActiveSheet.Columns("A:A").Select $oExcel.Selection.Replace("AM", " AM") $oExcel.Selection.Replace("PM", " PM") $oExcel.Selection.NumberFormat = "m/d/yyyy" SkysLastChance 1
seadoggie01 Posted October 13, 2021 Posted October 13, 2021 I highly suggest avoiding .Select and .Selection in VBA/COM whenever possible for performance, maintainability, and more. You can write it like this instead: #include <Excel.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\excel example.csv") Local $oRange = $oWorkbook.ActiveSheet.Columns("A:A") $oRange.Replace("AM", " AM") $oRange.Replace("PM", " PM") $oRange.NumberFormat = "m/d/yyyy" SkysLastChance 1 All my code provided is Public Domain... but it may not work. Use it, change it, break it, whatever you want. Spoiler My Humble Contributions:Personal Function Documentation - A personal HelpFile for your functionsAcro.au3 UDF - Automating Acrobat ProToDo Finder - Find #ToDo: lines in your scriptsUI-SimpleWrappers UDF - Use UI Automation more Simply-erKeePass UDF - Automate KeePass, a password managerInputBoxes - Simple Input boxes for various variable types
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