Basic Excel Com Obj script Topic is solved

[eblanc]

I've searched and read a couple of different things on Excel Com Obj, I usually feel like I have an information overload. What I need to do is fairly simple. I just need to read the data from couple of cells in an excel workbook, and I'll be ouputting them out to a webpage browser later,

For now, I'm just using notepad to test that these values are actually coming out. That would be step one. How do I copy/extract data in 1 cell and send it as text?

Code: Select all

::#cam1::
; Gets a reference to the workbook
; Change the path to the path of your workbook
xl := ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

x1.Range("B3").value := "hello world" 
;set cell to 'B3' to string
xl.Range("B11").Value := variable1 ;set cell to a variable

send, %B3%
send, %variable1%
send, B3
return

this is super rough. But it is where I am today.

[Kobaltauge]

I did a similar script to read from Excel and write in SAP GUI.
To read from Excel object I use the cells function, because it uses number and you can use a loop.

But I don't understand your code. First you're setting the cells, then you send undefined variables. If I understand your question, then you have information in the Excel cells and you want to send them.

So replace your code

Code: Select all

x1.Range("B3").value := "hello world" 
;set cell to 'B3' to string
xl.Range("B11").Value := variable1 ;set cell to a variable

with

Code: Select all

B3 := xl.ActiveSheet.cells.(2,3).text
variable1 := xl.ActiveSheet.cells.(2,11).text

[eblanc]

I did a similar script to read from Excel and write in SAP GUI.
To read from Excel object I use the cells function, because it uses number and you can use a loop.

But I don't understand your code. First you're setting the cells, then you send undefined variables. If I understand your question, then you have information in the Excel cells and you want to send them.

So replace your code

Code: Select all

x1.Range("B3").value := "hello world" 
;set cell to 'B3' to string
xl.Range("B11").Value := variable1 ;set cell to a variable

with

Code: Select all

B3 := xl.ActiveSheet.cells.(2,3).text
variable1 := xl.ActiveSheet.cells.(2,11).text

This is my excel file. A template. I pre-populate it with other information. And later on I have to copy and paste this same information into the browser, that is what I am trying to automate.

Excel Template

Excel Picture.jpg (173.03 KiB) Viewed 7873 times

I tried your script and this is what I get when I type #cam1

Code: Select all

Camera 2B3

It's kind of odd to me, I don't understand where it's pulling the information from. I tried changing the digits, and I get blank instead. Let me know if you understand and if there's anything else you'd like me to try.

[Kobaltauge]

Unfortunately you didn't provide the Excel file, so I did one by myself in the same "order" (see attached file).
If you place the Excel in c:\temp and run this script, a message box will provide you in every loop the content of column 1-5
Use this a blueprint.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("c:\temp\excel.xlsx")

rows := xl.Application.ActiveSheet.UsedRange.Rows.Count
title := 1
cam1 := 2
cam2 := 3
cam3 := 4
cam4 := 5

Loop, %rows%
{
	
	dataname := xl.ActiveSheet.cells(A_Index,title).text
	datacam1 := xl.ActiveSheet.cells(A_Index,cam1).text
	datacam2 := xl.ActiveSheet.cells(A_Index,cam2).text
	datacam3 := xl.ActiveSheet.cells(A_Index,cam3).text
	datacam4 := xl.ActiveSheet.cells(A_Index,cam4).text
	
	MsgBox % dataname " : " datacam1 " : " datacam2 " : " datacam3 " : " datacam4
}

[eblanc]

Unfortunately you didn't provide the Excel file, so I did one by myself in the same "order" (see attached file).
If you place the Excel in c:\temp and run this script, a message box will provide you in every loop the content of column 1-5
Use this a blueprint.

Sweet Man! This is getting a lot closer to what I need. I'm still dissecting your script to understand it better. How would I go about grabbing just single value from cells instead of rows.

For example, If I needed only information from Cam1, also not all the information is in order.

Example: I type #cam1, should give me (value only):

B7
B8
B4
B22
B24
B33
B34

If i can get those values individually, I should be able to complete phase 1. Which has been the hardest thing for me. After that It will just be a matter of making two of those values variable for some math, and that will be it.

[Kobaltauge]

Yeah, I should have put some comments in the script. =)

With xl.ActiveSheet.cells("row","coulumn").text you can acces every cell. As I wrote the before, the advantage is, that you can use numbers as row and column instead of the "letters" Excel is using. I though you wanted to access all cells in a sequence.

If you don't want a sequence and you need a special order it's not so easy. The simplest hack would be a command for every single cell.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("c:\temp\excel.xlsx")

text_of_B7 := xl.ActiveSheet.cells(2,7).text
text_of_B8 := xl.ActiveSheet.cells(2,8).text
text_of_B4 := xl.ActiveSheet.cells(2,4).text
text_of_B22 := xl.ActiveSheet.cells(2,22).text
text_of_B24 := xl.ActiveSheet.cells(2,24).text
text_of_B33 := xl.ActiveSheet.cells(2,33).text
text_of_B34 := xl.ActiveSheet.cells(2,34).text

[sinkfaze]

Code: Select all

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

[eblanc]

Code: Select all

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

I get an error.

Error 0x80020006 - Unknown Name.

Specificially: Range.

---> 014: Send, x1.range("B11").value


This is the code

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

[awel20]

Send, % xl.Worksheets("Sheet1").Range("B11").Value

ComObjGet gets a workbook not an applicaiton

[eblanc]

Send, % xl.Worksheets("Sheet1").Range("B11").Value

ComObjGet gets a workbook not an applicaiton

Thanks Awel!

You've solved my problem, kind of feel like a ghost that is gonna fade away after completing it's unfinished business on this world

*thanks awel....* Voice Fades away...





Hahaha, this will get me started. And it was frankly the most difficult thing. The rest should be easy.

2 last questions.

What stuff from the beginning of the code is throwaway, not useful for what I need.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

2. how can I close the worksheet through OBj com at the end, so that it doesn't linger. When I open the excel file, it tells me it's in use and can only get a READ-only copy of the file.

That's pretty much it. I have enough to get started on what I need. Seriously. thanks, I've been dreaming about this for a month or two.

[eblanc]

@awel20 Also forgot to mention, you've helped me a couple of times. Lots actually. I really appreciate it. Something I aspire to do once I understand this language better.

[awel20]

What stuff from the beginning of the code is throwaway, not useful for what I need.
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")[/code]

2. how can I close the worksheet through OBj com at the end, so that it doesn't linger. When I open the excel file, it tells me it's in use and can only get a READ-only copy of the file.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

Before you test anymore, check your Task Manager for any Excel processes that are open in the background that are invisible. You may have started Excel without ever closing it by using:

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Visible := false

Or you could just restart your computer.

Try this.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.

; This is why your workbook was already open when you used ComObjGet
; You don't need to open the workbook when using ComObjGet. I usually prefer to use ComObjCreate
; and not ComObjGet. Also if you use ComObjCreate, always make excel visible when testing. You can
; make it invisible after you confirm that your script closes the workbook and exits excel.
;xl := ComObjCreate("Excel.Application")
;xl.Visible := false                          		    ; Make the application visible
;xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Worksheets("Sheet1").Range("B3").value
Send, %	xl.Worksheets("Sheet1").Range("B11").Value
xl := "" ; Clear global com vars when you are done with them.
return

Regarding your second question, if you are using ComObjGet then clearing the variable at the end should allow the excel process to close. (xl := "")

If you are using ComObjCreate or ComObjActive then you need to close the workbook (maybe save any changes, if that's what you want), then close excel itself. Something like this.

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Visible := true                          		    ; Make the application visible
MyWorkbook := xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
;...
MyWorkbook.Save
MyWorkbook.Close ; or MyWorkbook.Close(0) to save without changes
xl.Quit
xl := MyWorkbook := ""

Cheers

[flyingDman]

Lots of confusion here. This code:

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

defines a handle (XL) on 2 different objects. Either use ComObjCreate("Excel.Application") OR ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx"). But not both. In the latter case awel20 's comment is correct (or use xl.activesheet.Range("B3").Value). I you use the former method, Send, % xl.Range("B11").Value will work just fine.

[eblanc]

Lots of confusion here. This code:

Code: Select all

xl := ComObjCreate("Excel.Application")
xl.Visible := false                          		    ; Make the application visible
xl.Workbooks.Open("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
Send, %	x1.Range("B3").value
Send, %	xl.Range("B11").Value
return

defines a handle (XL) on 2 different objects. Either use ComObjCreate("Excel.Application") OR ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx"). But not both. In the latter case awel20 's comment is correct (or use xl.activesheet.Range("B3").Value). I you use the former method, Send, % xl.Range("B11").Value will work just fine.

Got it. Don't use both.

In the last code I got from awel, he commented out the Com Obj create.

To be honest, I always get confused between both of them, I think com obj get it's better for my situation. The excel file will be populated a week prior, by hand. And for this instance, I only need to read the information from excel, don't need to make any changes at all.

[eblanc]

@awel20

Sweet, thanks!

I had like 14 excel processes running in the background, burning my CPU. hahaha


I tried this. And it works great. Except that I'm not getting the first values of the sheet. Kind of odd. Just thought I'd bring it up in case you might know what's causing it. Cause I might just work around the issue by adding extra stuff that might not be necessary. Basically, I did a quick test sheet. It seems that the script is always eating the first (and sometimes second)value

Code: Select all

::#cam1::
xl :=	ComObjGet("C:\Users\Enrique\Desktop\Codes\Excel COM\TEST - 00000 - Comobject Test Sheet.xlsx")
sleep, 1000
Send, %	x1.Worksheets("Sheet1").Range("B3").value
send, {enter}
Send, %	x1.Worksheets("Sheet1").Range("B4").value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B5").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B6").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B12").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B14").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B16").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B33").Value
send, {enter}
Send, %	xl.Worksheets("Sheet1").Range("B34").Value
send, {enter}
xl := "" ; Clear global com vars when you are done with them.
return

This is the output I'll get.

Code: Select all

NA99
 55555
 pass12345
 pass99999
 pass12345
 359225055900000
 8914800000017000000

I'm missing Cell B3 & B4. If I change them values they will still not show up, if I put those cells at the bottom, they will show up. Any ideas?

[flyingDman]

It is XL not X1!

[eblanc]

It is XL not X1!

Damn!! Very good eye!!!! how details like that are hard to see.

xl vs x1 in ahk, look exactly 100% the same.

Output now is correct:

Code: Select all

KILO
Enrique Iglesias
NA99
 55555
 pass12345
 pass99999
 pass12345
 359225055900000
 8914800000017000000
 

Thanks @flyingDman