Sign in to follow this  
Followers 0
joeloyzaga

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

5 posts in this topic

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



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

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - 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  
Followers 0

  • Similar Content

    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi all,
      i have an empty csv file, i have a non formatted text file.
      What do i want to do?
      I want to automate the process "get external data" in Excel, i want to import the data from the text file and basically create a csv file with a specific character encoding.
      Is it possible with AutoIT?
       
    • breakbadsp
      By breakbadsp
      I  want to create a excel file from my script if it does not exist.
      _ExcelBookOpen throws error=2 if file does not exist, after this error i want to create new file at this point.
      can i use _FileCreate()?
      _Logger($sLogPath, "{INFO}------: Opening Excel File: " & $sExcelPath& "") While 1 Local $oExcelTestResult = _ExcelBookOpen($sExcelPath) If @error = 2 Then If not _FileCreate($sResExcelPath) Then MsgBox(0, "Error", "Error In Opening REsult Excel File: Error: " & String(@error)) _Logger($sLogPath, "{ERROR}------: Result Excel File does not exist.. tried to create new but :ERROR : " & String(@error) & "") ExitLoop Else _Logger($sLogPath, "{INFO}------: Result Excel File does not exist.. **Created New**: ") EndIf Else ExitLoop EndIf WEnd  
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.