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

[Mercy]

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.

[water]

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)

 

[Mercy]

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

[Subz]

_Excel_ColumnToLetter

[water]

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

 

[water]

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)

 

[Mercy]

Thanks!!