Jump to content
Sign in to follow this  
joeloyzaga

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

joeloyzaga

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

 

Joe

Share this post


Link to post
Share on other sites
computergroove

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
joeloyzaga

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 
NewWorkBook.SaveAs(NewXL) 
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)
            .Tab.ColorIndex=53
            .Cells.Font.Name = "Comic Sans"
            .Cells.Font.Size = 12   
            End With

Share this post


Link to post
Share on other sites
computergroove

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
water

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
    .Tab.ColorIndex=53
    .Cells.Font.Name = "Comic Sans"
    .Cells.Font.Size = 12   
EndWith

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
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

    • AzgarD
      By AzgarD
      Hi guys. I know this is a newbie topic, very newbie, but i've read a lot of stuff and still don't get it. I just need to copy something from Excel cell, paste this in other program, copy something in this program and paste in other Excel cell. Something like...
      Copy A2 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C2 Then Copy A3 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C3 ... And it goes on The problem is, how can i "communicate" with Excel and do this row change? Like A2 to C2 and A3 to C3 ... In a efficient way that can be done like hundreds of times.
      Very newbie question but still not understanding this.
       
      Ty guys.
    • Siryx
      By Siryx
      So what i need is a function that I pass a starting date into and it counts up to the current date. Example:
      2009-05-30
      2009-05-31
      2009-06-01
      ...
      2018-06-18
      How would I do that? I found the Date.au3 in the includes but I can't find anything close to a time object like I'm used to working with in Java. I just need some $date = setDate(2009-05-30) and from there I could just add a day every time. I need this to be in the very format I stated earlier and from what I can see everytime related to date and time is automatically changed to my german locale.
      €: I already tried setDate but instead it changed my PCs clock. Not quite what i was looking for  
    • nacerbaaziz
      By nacerbaaziz

      Hello
      I have a question please
      How to show Islamic date in Autoit
      I mean
      for example
      Ramadan month
      And moharam month ... etc
      I hope my question is clear for you
      Thanks in advance
    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
    • MrCheese
      By MrCheese
      Hi guys,
      without including everything (unless you want it)
      I am copying data from a table in chrome and wanting to paste it into excel.
      Copying in Chrome works.
      I can paste it into the field i want by emulating goto -> ctrl V:
      WinActivate($dataload) WinWaitActive($dataload) Sleep(500) $oWorkbook1.Sheets("ItemReturn").Activate Sleep(500) $msg = "Measuring Sheet" conwrite() ttips2() Local Const $xlUp = -4162 With $oWorkbook1.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, "B")).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, "B").End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number EndWith $NewStartCell = $iLastCell + 2 $msg = "moving to location" conwrite() ttips2() Sleep(250) Send("^g") WinWait("Go To") Sleep(100) Send("B" & $NewStartCell) Sleep(100) Send("{ENTER}") Sleep(500) Send("^v")  
      But, I want to use _excel_rangecopypaste, pasting from the clipboard
      _Excel_RangeCopyPaste($oWorkbook1.ActiveSheet, default, "B" & $NewStartCell,default,$xlPasteValuesAndNumberFormats) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 2", "Error pasting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) however, this gives me error 4 , extended@:  -2147352567
      How can i fix this or find out how to debug this error?
       
      Thanks
×