autorobot Posted September 29, 2011 Share Posted September 29, 2011 I'm new to using autoit, but I would like to use it with excel. I'm trying to open an excel file, insert and name that column in (1,1). Then in row 2, column 1, I need to concatenate (C2 and K2). Up to this point I'm okay. How do I propagate the concatenate to row3, 1, row4, 1, row5, 1 and so on until the end of the spreadsheet? Here is my code: #include <Excel.au3> DriveMapAdd ("Z:", "\") $FilePath="Z:\SalesOrders_and_BackOrders.xls" $oExcel = _ExcelBookOpen($FilePath) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf _ExcelColumnInsert($oExcel, 1, 1) ;Inserting Unique ID Column _ExcelWriteCell($oExcel, "Unique ID", 1, 1) ;Labeling as Unique ID _ExcelWriteCell($oExcel, "=CONCATENATE(C2,K2)", 2, 1) ;Concatenate Build and Stock Code Columns So this is where I stand, I don't know how to increment the row # if I were going to use a For loop or do..until. Thanks in advance for the assistance. Link to comment Share on other sites More sharing options...
JoHanatCent Posted September 29, 2011 Share Posted September 29, 2011 Try: #include <Excel.au3> ;DriveMapAdd ("Z:", "\") $FilePath = "Z:\SalesOrders_and_BackOrders.xls" $oExcel = _ExcelBookOpen($FilePath ) If @error = 1 Then MsgBox(0, "Error!", "Unable to Create the Excel Object") Exit ElseIf @error = 2 Then MsgBox(0, "Error!", "File does not exist - Shame on you!") Exit EndIf Global $iLastRow = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Row _ExcelColumnInsert($oExcel, 1, 1) ;Inserting Unique ID Column _ExcelWriteCell($oExcel, "Unique ID", 1, 1) ;Labeling as Unique ID For $i = 2 To $iLastRow _ExcelWriteFormula($oExcel, "=CONCATENATE(R" & $i & "C3,R" & $i & "C11)", $i, 1) Next ; _ExcelWriteCell($oExcel, "=CONCATENATE(C2,K2)", 2, 1) ;Concatenate Build and Stock Code Columns Link to comment Share on other sites More sharing options...
autorobot Posted September 29, 2011 Author Share Posted September 29, 2011 You're awesome!! That's exactly what I wanted. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now