COM EXCEL copy certain Rows - array

[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

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

[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

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

[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 	

[hertzrider2000]

flyingDman, simple and clean, thank you a lot man.

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

[hertzrider2000]

s1 can help me out?

[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 	    

[hertzrider2000]

this one works too, thank u again bro