COM script help, copy and paste from excel to different program

[cbruffin]

Hello! I am new here but really could use your help. I have been forum browsing for about 5 hours and am at somewhat of a dead end.

I need a script that does the following. I am trying to figure this out but can't find complete enough documentation on COM to write myself. If it exists, sorry I couldn't find it! I just found some basic tutorials in the forums.

I have an excel file named PMU.xlsx with variables in column A and B. I don't know the amount of rows in advance (non blank cells in column A/B) because that will change each month. The script should:

1. Copy cell A1 from open excel file
2. Switch applications (CDK is the program name), then wait for it to activate (OR send the data in the background!)
3. Paste the contents just copied from cell A1
4. Hit "enter" 7 times
5. copy cell B1 from open excel file
6. Switch back to CDK(OR send the data in the background)
7. Enter the value just copied from cell B1 into CDK

8. Loop through all rows. There will always be a value in B if there is a value in A, so knowing the number of rows in either column will be perfect to determine how many times to loop.


Thanks for the help! This is the code I have started trying to piece together from forum posts, but it doesn't come close to accomplishing the goal yet:
_______________________________

Code: Select all

MyFilePath := A_Desktop "\PMU.xlsx"

Try oExcel := ComObjActive("Excel.Application") ; Get the active Excel application object if an instance of Excel is open.
if (!oExcel)    ; If there was no instance of excel open...
{
    oExcel := ComObjCreate("Excel.Application") ; Create an Excel Application object
    oWorkBook := oExcel.Workbooks.Open(MyFilePath) ; Open a workbook with the file path in the variable "MyFilePath"
}
oExcel.Visible := true    ; Make excel visible (Optional)

MyCells := while (Xl.Range("A" . A_Index).Value != "") {
Xl.Range("A" . A_Index).Value := value
}
 
^j::    ; Ctrl+J Hotkey
for key, in MyCells
{
    Send, % key.Value "{Enter}"
    Sleep, 500
}


return

[FanaticGuru]

Here is an example of how to loop through all the cells in Columns A and B.

Code: Select all

MyFilePath := A_Desktop "\PMU.xlsx"

try 
	oExcel := ComObjActive("Excel.Application") ; Get the active Excel application object if an instance of Excel is open.
catch
	{
		oExcel := ComObjCreate("Excel.Application") ; Create an Excel Application object
		oExcel.Visible := true    ; Make excel visible (Optional)
		oWorkBook := oExcel.Workbooks.Open(MyFilePath) ; Open a workbook with the file path in the variable "MyFilePath"
	}

For oCell in oExcel.Columns("A").Cells
{
	if !oCell.Value
		break
	; Here is where you would do all your stuff to paste to other applications
	MsgBox % oCell.Value "`t" oCell.OffSet(0,1).Value ; Values from Column A and B
}

MsgBox DONE

FG

[cbruffin]

Thanks FanaticGuru! I was able to solve my issue with a few tweaks to what you gave me. It would have taken me ages to figure this out without you, and I really appreciate you helping.