Jump to content
Sign in to follow this  
Mercy

How to find the last column of an excel and append a column

Recommended Posts

I need to add a column with data daily at the end of the column and calculate the difference between last two columns in put it in a seperate column. So i need to get the  position of the last column.

Local $count = $oWorkbook2.Worksheets(1).UsedRange.Columns.Count

 MsgBox(0,"",$count)
; *****************************************************************************
; Copy a single cell from another workbook. Pass the source range as object.
; *****************************************************************************
Local $oRange = $oWorkbook2.Worksheets(1).Range("A1:A15")
_Excel_RangeCopyPaste($oWorkbook1.Worksheets(1), $oRange,"H1:H15")
_Excel_RangeWrite($oWorkbook1, $oWorkbook1.Worksheets(1), "=C2-H2", "I2:I15", False)

this code copies to a specific column H with a range 15. But i need to keep that column range to update automatically when columns are added.

Share this post


Link to post
Share on other sites

Example to get the last used column:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls")

Global $LastColumn = $oExcel.Range("A1").SpecialCells($xlCellTypeLastCell).Column
ConsoleWrite($LastColumn & @CRLF)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Yes , i can i get that using the below code also: the output is in number. if it is a row then it will be fine. but it is column and when specifying the range it should be in A,B,C etc . Is there any way to get the column name as A,B,AA, etc

Local $count = $oWorkbook2.Worksheets(1).UsedRange.Columns.Count

Share this post


Link to post
Share on other sites
Local $count = $oWorkbook2.Worksheets(1).UsedRange.Columns.Count

returns the correct column number when the used range starts with column A. If it starts with another column then the result is wrong.
Try:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls")

Global $iLastColumn = $oExcel.Range("A1").SpecialCells($xlCellTypeLastCell).Column
ConsoleWrite($iLastColumn & @CRLF) ; Last used column as number
ConsoleWrite(_Excel_ColumnToLetter($iLastColumn) & @CRLF) ; Last used column as letter

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

Get first unused column:

#include <Excel.au3>

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls")

Global $oLastUsedColumn = $oExcel.Range("A1").SpecialCells($xlCellTypeLastCell)
Global $iFirstUnusedColumn = $oLastUsedColumn.Offset(0, 1).Column
ConsoleWrite("First unused column: " & $iFirstUnusedColumn & " - " & _Excel_ColumnToLetter($iFirstUnusedColumn) & @CRLF)

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-09-05 - Version 1.5.1.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-27 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - 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  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By learner123
      Hi All,
       
      I am new to this AUTO IT and I have created a script that will open an app,enter pin and copy the code generated to clipboard. My java code call this autoIT script and use the copied generated code from clipboard.
      This works fine when server  window is on focus. My server is an windows server. 
      But when I minimize or disconnect the server, the script opens the app.exe but doesn't copy any value to clipboard.  
      Can anyone help me on this 😐
       
      Run("C:\Program Files (x86)\RSA SecurID Software Token\SecurID.exe")
      Local $hWnd=WinWait("abc - RSA SecurID Token") ; waits until the window is the active window
      $hWin = WinGetHandle("abc - RSA SecurID Token");
      ControlSend($hWnd,"","","1111") ; simulates pressing the Home key
      ControlSend($hWnd,"","","{ENTER}");
      ControlSend($hWnd,"","","^c");
      Sleep(1000) ;
      ControlSend($hWnd,"","","^c");
       
    • By learner123
      Hi All,
      So I have created a small autoIT script to enter pin into a RSA token(app which generate new code every 30 second), and copy the generated code.
      I have a java application which requires this code so every time my java-code requires this RSA code, it runs the autoIT script and the copied generated code is then used in my java application. 
      I have deployed this code on a windows server and it works fine when I am logged in and the window is on focus, But as soon as I schedule task and disconnect the server (not logged out only disconnect), or even minimize the server window, the autoIT scripts fails and its not able to copy the value.
       
      Please find below the code for AUTOIT.
       
      WinActivate("rsa - RSA SecurID Token") ; activates the window that has old in the tilte bar
      WinWaitActive("rsa - RSA SecurID Token") ; waits until the window is the active window
      Send("1111") ; simulates pressing the Home key, enters password to get the code
      Send("{ENTER}") ; simulates pressing the Enter key
      Sleep(1000) ;
      Send("^c") ; simulates pressing the CTRL+c keys (copy)
       
      Also I saw some post regarding that WINACTIVE only works when window is active. But my below AUTO IT script to handle windows pop up  works perfectly fine when the server is disconnected. 
       
      Opt("WinTitleMatchMode", 1)
      WinWait("https://url","","10")
      WinWaitActive("https://url","","10")
      Sleep(2000)
      Send("userid")
      Sleep(1000)
      Send("{TAB}")
      Sleep(1000)
      Send("passwrd")
      Send("{TAB}")
      Sleep(500)
      Send("{ENTER}")
       
       
    • By JuanFelipe
      Hello gyus, I try to make a code to login in a web page in my job, buy i can’t understand why my form is blocked, this is my code: 
      #include <ButtonConstants.au3> #include <EditConstants.au3> #include <GUIConstantsEx.au3> #include <StaticConstants.au3> #include <StringConstants.au3> #include <WindowsConstants.au3> #include <WinAPIFiles.au3> #include <FileConstants.au3> #include <File.au3> #include <Array.au3> #include <IE.au3> #include <Excel.au3> #include <GuiEdit.au3> #include <GuiStatusBar.au3> #include <DateTimeConstants.au3> #include <Date.au3> #Region ### START Koda GUI section ### Form=C:\noentry\koda_1.7.3.0\Forms\spoa.kxf $Form1 = GUICreate("Eva y Vehículos", 1200, 1000, 10, 10) $prueba = GUICtrlCreateButton("Prueba",10,990,50,20) ;VARIABLES ========================================================================================== $psi = "https://psi.policia.gov.co/PSI/Login.aspx?ReturnUrl=%2fPSI#no-back-button" $chequeVehiculos = "https://psi.policia.gov.co/PSI/frm_lista_chk.aspx" $evaluacionEva = "https://psi.policia.gov.co/PSI/eva_frmver.aspx" $oIE = ObjCreate("Shell.Explorer.2") $GUIActiveX = GUICtrlCreateObj ($oIE, 10, 10, 1180, 980) ;GUICtrlSetState(-1, $GUI_DISABLE) GUISetState(@SW_SHOW) $oIE.navigate($psi) ;_IELoadWait($oIE) _InicioSesion() #EndRegion ### END Koda GUI section ### While 1 $nMsg = GUIGetMsg() Switch $nMsg Case $GUI_EVENT_CLOSE Exit Case $prueba _InicioSesion() EndSwitch WEnd Func _InicioSesion() Local $username = _IEGetObjByName($oIE, "txtUsuario") Local $pass = _IEGetObjByName($oIE, "txtClave") Local $logina = _IEGetObjByName($oIE, "btnIngresar") $username.value="example" $pass.value="examplepass" _IEAction($logina, 'click') _IELoadWait($oIE) MsgBox(16,"","") EndFunc  
    • By shelly
      Here is the below code for handling pop-up when window is  inactive ..but I don't know how to change sleep and when i run this script it runs sometimes and sometimes it stops .
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{SPACE}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{DOWN}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{ENTER}")
      --- these 3 lines never worked while TAB lines works sometimes but not in accurate way
      I am new too AutoIt .. help me out why this script behaves in strange way
      ControlFocus("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog", "", "Internet Explorer_Server1","1")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog", "", "Internet Explorer_Server1","the request is send")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{SPACE}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{DOWN}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      Sleep(3000)
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{TAB}")
      ControlSend("Policy Decisions -- Webpage Dialog","","Internet Explorer_Server1","{ENTER}")
       
×
×
  • Create New...