Keep tab delimited format

[Scottzj]

New to AHK. Storing values and sending output to excel. It's pasting into one column in excel instead of several. Is there anything I can update below to keep it tab delimited? Or can't do this using an array.

Code: Select all

^o::
path := "C:\Users\zirje\OneDrive\Desktop\StratAHK.xlsx"   ;<--- enter your book path

XL := ComObjCreate("Excel.Application")
XL.Workbooks.Open(path)
XL.Visible := True

Sleep, 300
result := []  ; declare array
Loop, 2
{
    Sleep, 300
    WinActivate, **********[Longs] -> MODE: LIVE ***********ahk_class RAIL_WINDOW
    Sleep, 333
    Sleep, 300
    MsgBox, 0, , Highlight Strategy
    Sleep, 1000
    Send, {Control Down}{a}{Control Up}
    Sleep, 200
    Sleep, 300
    Send, {Control Down}{c}{Control Up}  ; Copies Output
    Sleep, 200
    output := clipboard 
    result.Push(output)  ; array.push() appends values to the end of an array
}
For cell in XL.Range("A1:A2")
{  
    cell.Value :=result[A_Index]  ; Pastes Output into Excel
    Sleep, 200
    MsgBox %  result[A_Index]
}
Return

[mikeyww]

I did not try your script, but it seems like you could just navigate to the first target cell, and then just issue a paste. An alternative could be parsing your clipboard (e.g., StrSplit) before you issue the paste.

Code: Select all

result := StrSplit(Trim(Clipboard, "`r`n"), "`r`n")
For each, item in result
 MsgBox %item%

[Scottzj]

I'm not certain what the code you posted is doing, wish I knew. I should probably explain better what I'm attempting to do. Basically..
1. having it activate a window in a program I'm using
2. message box pops up to select the row I need to copy to clipboard
3. click the mouse on the row
4. save the data on the clipboard to first spot in the array
5. message box pops up again to select the next row to copy
6. click the mouse on the new row
7. save the data for this new row to the second spot in the array
8. once it's looped through and saved all the rows I need, the next section of the code pastes the first array value into row 1 in excel, then pastes the second array value into row 2 etc.

It does this fine but it's pasting the first item I copied and jamming it all into row 1 column A instead of row 1 column A,B,C and D. If I copy it manually and paste it into excel it pastes in as needed. I think it's a matter of just using PasteSpecial() but I just don't know how to implement it into my code above.

I tried this for instance which puts the info in row 1 column A,B,C and D as needed but it just keeps using the first copied item over and over instead of moving onto the next item in the array.

Code: Select all

cell.PasteSpecial() :=result[A_Index]  ; Pastes Inspector Output into Excel
    Sleep, 200

[mikeyww]

OK. Thank you for explaining it. I was providing an example of how to split a string into an array, but after reading your explanation, it might not meet your needs. Sorry about that. You may need someone here who knows more about COM, as that seems likely to be your answer.

[AlphaBravo]

Code: Select all

result := ["1`t2`t3", "4`t5", "6`t7"]

for r, line in result
	for c, v in StrSplit(line, "`t")
		xl.cells(r, c).value := v

[Scottzj]

That is all greek to me, I will have to research what that is doing. Thanks

[Scottzj]

Code: Select all

result := ["1`t2`t3", "4`t5", "6`t7"]

for r, line in result
	for c, v in StrSplit(line, "`t")
		xl.cells(r, c).value := v

Oh wow that does it. Thanks! Why did you have to confuse me lol and not just put...

Code: Select all

result := []
Your Loop code here
for r, line in result
	for c, v in StrSplit(line, "`t")
		xl.cells(r, c).value := v

[AlphaBravo]

Oh wow that does it. Thanks! Why did you have to confuse me lol and not just put...

Because I wanted you to figure it out on your own and you did

[Scottzj]

Tacking onto the code above, after it pastes the info into rows 1 and 2 how can I make it shift it down to rows 4 and 5 so that the next day rows 1 and 2 will be available to paste into. Or maybe you would just create 3 new rows above if there is already data in row 1.

It will be more like 10 or more new rows each day but just trying to keep it simple.

[AlphaBravo]

Tacking onto the code above, after it pastes the info into rows 1 and 2 how can I make it shift it down to rows 4 and 5 so that the next day rows 1 and 2 will be available to paste into. Or maybe you would just create 3 new rows above if there is already data in row 1.

It will be more like 10 or more new rows each day but just trying to keep it simple.

to insert 2 rows at the top xl.Range("A1:A2").EntireRow.Insert

[Scottzj]

Thank you! Is there a easy way to delete all blank rows? I found a few videos on it but he is using AHK studio and I can't see the code.

[flyingDman]

see here: viewtopic.php?f=76&t=68840

[Scottzj]

Ha ya that's one I was looking at but can't get it to work. Tried something like...

Code: Select all

for Cell in Xl.Range("A1:A100").Cells
		if !(Cell.Value="")
			XL.Rows(row).EntireRow.Delete

Tried replacing row with Cell. Tried replacing Cell and Cells with row and Rows etc etc

[flyingDman]

That's one I was looking at but can't get it to work

Any error message? What does it do? It works fine here (obvioulsy )
Try this one instead:

Code: Select all

xl := ComObjActive("excel.application")
lstrw := xl.activesheet.usedrange.rows.count 
loop, %lstrw%
	{
	rw := lstrw - a_index + 1
	if (xl.WorksheetFunction.counta(xl.rows(rw).entirerow) = 0)
		xl.rows(rw).entirerow.delete
	}

[Scottzj]

Ya I get an error message, could be where I'm placing it in the body of the code. Either way yours works great. I just had to do a_index + 2 instead because of the header. Thanks again!

[Scottzj]

I've tried a million things, have an array with length 12 how can I make it paste into excel just element 3 and 9 of the array incorporating it into the same code below that i've been using above..

Code: Select all

result := []
output := clipboard
result.Push(output)
for r, line in result
	for c, v in StrSplit(line, "`t")
		xl.cells(r, c).value := v

I tried something using myVar:= s.3 which grabs the 3rd element in the array but it pushes it into the 12 column in excel. And wouldn't know how to make it also grab the 9th element anyhow.

[AlphaBravo]

translation of Macro 2. Delete all blank rows in Excel, works great!

Code: Select all

UsedRng := xl.ActiveSheet.UsedRange
LastRowIndex := UsedRng.Row - 1 + UsedRng.Rows.Count
xl.Application.ScreenUpdating := False
RowIndex := LastRowIndex
while (RowIndex >=1)
{
	If xl.Application.CountA(xl.Rows(RowIndex)) = 0
		xl.Rows(RowIndex).Delete
	RowIndex--
}
xl.Application.ScreenUpdating := True

[Scottzj]

Is that for my message above for only keep array elements 3 & 9 (which would currently be excel columns 3 & 9)?

[AlphaBravo]

Is that for my message above for only keep array elements 3 & 9 (which would currently be excel columns 3 & 9)?

that was for deleting empty rows, for columns 3 & 9 try this:

Code: Select all

for r, line in result
{
	x := StrSplit(line, "`t")
	xl.cells(r, 1).value := x.3	; row r, column 1
	xl.cells(r, 2).value := x.9	; row r, column 2
}

[flyingDman]

A couple of notes re: the "delete empty rows" code. @AlphaBravo uses xl.application.counta() while I used
xl.WorksheetFunction.counta(). Both seem to work but be aware that application.counta() does not appear to be documented.
See here https://stackoverflow.com/questions/25890571/worksheetfunction-counta-not-working-post-upgrade-to-office-2010

I do not believe that rw := lstrw - a_index + 2 is necessary unless row 1 is blank and you do not want to delete it.
If it is a header row, one can assume it's not blank.

Re: LastRow. I used lstrw := xl.activesheet.usedrange.rows.count . This is fine when assumed that there are no blank rows above the UsedRange but a better way to do it is probably lstrw := xl.activesheet.Cells.Find("*",,,,1,2).Row