Jump to content
Sign in to follow this  

read the columns of worksheet and set any data in the colums named with "Date" in the title to format "dd/mm/yyyy"

Recommended Posts


I know to use _Excel_RangeRead to read the Column titles by: getting the list of Worksheets using  _Excel_SheetList, open each worksheet etc etc but could I do it by using _Excel_RangeFind. Anyway I don't know the command to set the Range to "dd/mm/yyyy"

Could anybody give me an example above? - setting the formatting please



Share this post

Link to post
Share on other sites

Maybe this:

@MDAY & "/" & @MON & "/" & @YEAR

Some example code and a snippet or example excel worksheet would be helpful for us to test here.

Get Scite to add a popup when you use a 3rd party UDF -> http://www.autoitscript.com/autoit3/scite/docs/SciTE4AutoIt3/user-calltip-manager.html

Share this post

Link to post
Share on other sites

I actually don't know how to set the formatting in autoit - I know how to set it in vbs like this 

Set NewWorkBook = oEngine.Workbooks.Add 
Set WorkBook = oEngine.Workbooks.Open(NewXL) 
Set InitSheet = WorkBook.Sheets.Item(1) 
    With InitSheet 
           .Range("A1","A1").NumberFormat = "@"
            .Range("A1:ZZ1").Font.Color = RGB(218,225,130)
            .Cells.Font.Name = "Comic Sans"
            .Cells.Font.Size = 12   
            End With

Share this post

Link to post
Share on other sites

I dont know vbs. In these forums we are more focused on pushing you in the right direction rather than writing the code for you. If I ask for come code snippets it's because I need to have something that is relevant to your problem that I can test from my side to get it to work. In your first post I am not sure if you are looking for a column or row or header or tag or whatever. Obviously the syntax is different for the autoit code for different data locations.

Get Scite to add a popup when you use a 3rd party UDF -> http://www.autoitscript.com/autoit3/scite/docs/SciTE4AutoIt3/user-calltip-manager.html

Share this post

Link to post
Share on other sites

Translating VBS to Autoit isn't too hard:

; $oWorkbook is returned by _Excel_WorkbookOpen etc.
With $oWorkBook.ActiveSheet 
    .Range("A1").NumberFormat = "@"
;    .Range("A1:ZZ1").Font.Color = RGB(218,225,130) ; RGB is a VBS function and not available in AutoIt. If needed I can provide an example
    .Cells.Font.Name = "Comic Sans"
    .Cells.Font.Size = 12   

My UDFs and Tutorials:


Active Directory (NEW 2017-04-18 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version - Download - General Help & Support

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  

  • Similar Content

    • rudi
      By rudi
      I used the various "_Excel_*()" funktions to open workbooks, read and write cells.
      I was looking through the helpfile for the _EXCEL_* funktions, if there is a direkt way to set background color or text color -- if there is one, then I missed it?
      Searching the forum I found this posting demonstrating how to do this task using ...
      Is there a way to set the background color etc. with the native "_EXCEL_*" as well?
      Regards, Rudi.
    • MrCheese
      By MrCheese
      Hi guys,
      Wondering, is there a better way, likely to use 'for...next' to add a letter to each range, by moving right -> along a range of columns in excel.
      I currently use this, but its clunky.
      If $run = 1 Then $range = "B6:B41" If $run = 2 Then $range = "C6:C41" If $run = 3 Then $range = "D6:D41" If $run = 4 Then $range = "E6:E41" If $run = 5 Then $range = "F6:F41" If $run = 6 Then $range = "G6:G41" If $run = 7 Then $range = "H6:H41" If $run = 8 Then $range = "I6:I41" If $run = 9 Then $range = "J6:J41" If $run = 10 Then $range = "K6:K41" If $run = 11 Then $range = "L6:L41" If $run = 12 Then $range = "M6:M41" If $run = 13 Then $range = "N6:N41" If $run = 14 Then $range = "O6:O41" If $run = 15 Then $range = "P6:P41" If $run = 16 Then $range = "Q6:Q41" If $run = 17 Then $range = "R6:R41" If $run = 18 Then $range = "S6:S41" If $run = 19 Then $range = "T6:T41" If $run = 20 Then $range = "U6:U41" If $run = 21 Then $range = "V6:V41" If $run = 22 Then $range = "W6:W41" If $run = 23 Then $range = "X6:X41" If $run = 24 Then $range = "Y6:Y41" If $run = 25 Then $range = "Z6:Z41" If $run = 26 Then $range = "AA6:AA41" If $run = 27 Then $range = "AB6:AB41" If $run = 28 Then $range = "AC6:AC41" If $run = 29 Then $range = "AD6:AD41" If $run = 30 Then $range = "AE6:AE41" If $run = 31 Then $range = "AF6:AF41" If $run = 32 Then $range = "AG6:AG41" If $run = 33 Then $range = "AH6:AH41" If $run = 34 Then $range = "AI6:AI41" If $run = 35 Then $range = "AJ6:AJ41" If $run = 36 Then $range = "AK6:AK41" If $run = 37 Then $range = "AL6:AL41" If $run = 38 Then $range = "AM6:AM41" If $run = 39 Then $range = "AN6:AN41" If $run = 40 Then $range = "AO6:AO41" If $run = 41 Then $range = "AP6:AP41" If $run = 42 Then $range = "AQ6:AQ41" If $run = 43 Then $range = "AR6:AR41" If $run = 44 Then $range = "AS6:AS41" If $run = 45 Then $range = "AT6:AT41" If $run = 46 Then $range = "AU6:AU41" If $run = 47 Then $range = "AV6:AV41" If $run = 48 Then $range = "AW6:AW41" If $run = 49 Then $range = "AX6:AX41" If $run = 50 Then $range = "AY6:AY41"  
      Normally, if it was going down the rows, i'd use this:
      For $i = 0 To UBound($iRowCount) - 1 $row = $i + 1 $range = "B"&$row&":B"&$row+1 Next  
      so something like this, but i don't know how to code sequential columns:
      For $i = 0 To UBound($iColCount) - 1 $col = $i + 1 $range = $col&"1:"&$col&"40" Next  
      If I don't make sense, let me know.
      Any help would be great. thanks
    • ShawnW
      By ShawnW
      I have a script that takes a large excel file, pulls out and reorganizes certain information I need, and spits out a trimmed down csv file which I uses to upload the information on my website. Some of this information contains characters with accents or em dashes. By default it would create a csv file in ANSI which I then uploaded but had to tell my website import system it was windows-1252 in order for it to look correct.
      This was all working fine except now I need to add in a non-breaking space and non-breaking hyphen into parts of my output. At first I tried using ChrW(0xA0) and ChrW(0x2011) as replacements. A quick test in the console looked correct, however opening the csv output in notepad++ showed the space correctly but a ? for the hyphen and the file was still encoded as ANSI. I tried to view it as UTF-8 instead but this just made the space appear as xAO and also other characters appeared that way like my em dashes appeared as x97 and another symbol as xA7 etc.
      If I instead do a convert to UTF-8 from notepad++ then those problems go away except the hyphen still displays as ?. I then noticed on the page I linked for the non-breaking hyphen it lists the UTF-8 hex as 0xE2 0x80 0x91 (e28091). I was unsure how to enter this in autoit but several things i tried all failed to get the hyphen inserted.
      I need a way to get both the space and hyphen added correctly as either ANSI or UTF-8, but if it is UTF-8 then I need a way to convert all of the other data I extracted from the excel file.
      I've included a test excel file with a single line and test script to create a csv demonstrating the problem.
    • Shedunn
      By Shedunn
      I've been looking through some different functions(not sure if that's the right word) to get the Date and time and I can't find one that doesn't include "/ " or ":" in what is returned.

      I'm trying to create a file name with this format "QuickConfig_MMDDYYYY_HHMM"  (doesn't matter if 24 or 12hr format)
      #include <Date.au3> Func _SaveConfig MouseClick('primary',155, 46, 1, 0) ;clicks save as WinWait('Save bluePRINT Configuration As...','', 4) MouseClick('primary',632, 47, 1, 0) ;clicks the file path bar $Documents = @MyDocumentsDir $FilePath = $Documents & '\BP3 Configs' Send($FilePath) Send('{ENTER}') MouseClick('primary',166, 580, 1, 0) ;clicks file name bar $FileName = 'QuickConfig_' & _NowDate & '_' & _NowTime Send($FileName) Send('{ENTER}') EndFunc  
      I'm wondering if there is a function somewhere to do this?
    • Nareshm
      By Nareshm
      I try to activate my opened excel file using this code :
      #include <Excel.au3> $oExcel = _Excel_Open() $sCaption = $oExcel.Caption WinSetState($sCaption, "", @SW_MAXIMIZE) But when i edit cell in my excel file above code not working because it open new excel sheet.

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.