Page 1 of 1
COM EXCEL copy certain Rows - array
Posted: 17 May 2021, 20:23
by hertzrider2000
Hello
got the code below,
it takes the entires from certain columns, (Columns := ["B","C","F","E","Q"])
how i can now use the variables/arrays frow each column (Display .= Index "`t" Value "`n")
and copy the content into a new excel file,coloumn for column
example the content from (Excel File 1) Row B -> into (Excel file 2) Row A
Code: Select all
; Create a connection between AHK and an open active Excel file
; MsgBox % XL.range("A1").text
Columns := ["B","C","F","E","Q"]
Data := {}
; Get all cell values in columns and put in an array
for Index, Column in Columns
for xlCell in XL.ActiveSheet.UsedRange.Columns(Column).Cells
Data[Column, A_Index] := xlCell.Value
; Display all the values in Column Q
for Index, Value in Data.Q
Display .= Index "`t" Value "`n"
msgbox % Display
Re: COM EXCEL copy certain Rows - array
Posted: 17 May 2021, 23:07
by flyingDman
I would suggest you look into using safearrays for copying Excel ranges. The following shows how to copy all data in column Q from one workbook to column A in another file (both sheets are assumed to be open here; the source is the topmost).
Code: Select all
xl := ComObjActive("Excel.application")
lstrw := xl.Range("Q" xl.Rows.Count).End(-4162).Row ; determine the last row in column Q
sarr := xl.activesheet.range("Q1:Q" lstrw).value ; create safearray
xl.Workbooks("file2.xlsx").Activate ; activate the second file
xl.range("A1:A" lstrw) := sarr ; paste the data
Note the clipboard is not used here!
BTW
XL.ActiveSheet.UsedRange.Columns(Column).Cells does not always give the wanted result. If column A, for instance is blank, usedrange does not start at A1 and UsedRange.Columns("P") will read the cells in column Q.
Re: COM EXCEL copy certain Rows - array
Posted: 18 May 2021, 03:10
by Xeo786
you can simple transfere whole coloumn data to another coloumn like this
sheet2.Columns(toCol).Value := sheet1.Columns(fromCol).Value
Code: Select all
file2 := "C:\file2location\wb2.xlsx" ; second excel file you need data to be putin you can open new workbook too just change [c] xl.workbooks.Add()[/c]
xl := ComObjActive("Excel.application") ; condition when initial file1 is open in excel
wb1 := xl.activeworkbook
wb2 := xl.workbooks.open(file2) ; and file getting data from initial file is now getting open
sheet1 := wb1.sheets(1) ; defining which sheet we take column from
sheet2 := wb2.sheets(1) ; defining which sheet we put column to
Columns := {"B" : "A","C" : "B"} ; making an object having bothing colomn define {fromcol2:tocol1, fromcol3:tocol2, ... and so on }
for fromCol, toCol in Columns
sheet2.Columns(toCol).Value := sheet1.Columns(fromCol).Value ; simply pasting whole coloumn at once
return
Re: COM EXCEL copy certain Rows - array
Posted: 19 May 2021, 19:54
by hertzrider2000
Perfect guys, thank you first of all.
can we do one more step,
assume there is already some values in the new excel file 2 (column A),
i wana jump to the last blank cell and paste it there, instead of the whole column.
Re: COM EXCEL copy certain Rows - array
Posted: 19 May 2021, 20:41
by flyingDman
You can do something like this:
Code: Select all
xl := ComObjActive("Excel.application")
lstrw1 := xl.Range("Q" xl.Rows.Count).End(-4162).Row ; determine the last row in column Q
sarr := xl.activesheet.range("Q1:Q" lstrw1).value ; create safearray
xl.Workbooks("file2.xlsx").Activate ; activate the second file
lstrw2 := xl.Range("A" xl.Rows.Count).End(-4162).Row ; determine the last row in column A in file2
xl.range("A" lstrw2 + 1 ":A" lstrw1 + lstrw2) := sarr ; paste the data
Re: COM EXCEL copy certain Rows - array
Posted: 19 May 2021, 21:02
by hertzrider2000
Re: COM EXCEL copy certain Rows - array
Posted: 19 May 2021, 22:08
by hertzrider2000
Harrr, okay 1 more point guys,
i have filtered excel file 1 column Q,
when i use the script it paste the whole columns Q from excel 1 into excelfile 2
i there a way just to copy the filtered column?
Re: COM EXCEL copy certain Rows - array
Posted: 22 May 2021, 21:30
by hertzrider2000
s1 can help me out?
Re: COM EXCEL copy certain Rows - array
Posted: 23 May 2021, 16:23
by flyingDman
I do not believe that is possible using a safearray. But using the clipboard the following should work:
Code: Select all
xl := ComObjActive("Excel.application")
lstrw1 := xl.Range("Q" xl.Rows.Count).End(-4162).Row ; determine the last row in column Q
sarr := xl.activesheet.range("Q2:Q" lstrw1).SpecialCells(12).copy ; assumes the header is row 1
xl.Workbooks("file2.xlsx").Activate ; activate the second file
lstrw2 := xl.Range("A" xl.Rows.Count).End(-4162).Row ; determine the last row in column A in file2
xl.range("A" lstrw2 + 1).pastespecial(-4104) ; paste the data
Re: COM EXCEL copy certain Rows - array
Posted: 25 May 2021, 05:03
by hertzrider2000
this one works too, thank u again bro
