Reading Excel values into variables

Get help with using AutoHotkey and its commands and hotkeys
mattkingston
Posts: 18
Joined: 14 Oct 2016, 13:17

Reading Excel values into variables

17 Nov 2020, 19:02

Hi,
Apologies of similar questions have been asked but from the few snippets of info out that relating to AHK reading an Excel file, I'm struggling to piece them together.

I'm wanting to have a script that opens an Excel file of my choosing and copy each cells contents into their own variable.
Something like A1 = varA1, D4 = varD4 etc etc.

Is this possible? I found this code that does this but, it also creates the Excel sheet first, I want it to read an existing sheet.

Code: Select all

oExcel := ComObjCreate("Excel.Application") ; create Excel Application object
oExcel.Workbooks.Add ; create a new workbook (oWorkbook := oExcel.Workbooks.Add)

oExcel.Range("A1").Value := 3 ; set cell A1 to 3
oExcel.Range("A2").Value := 7 ; set cell A2 to 7
oExcel.Range("A3").Formula := "=SUM(A1:A2)" ; set formula for cell A3 to SUM(A1:A2)

oExcel.Range("A1:A3").Interior.ColorIndex := 19 ; fill range of cells from A1 to A3 with color number 19
oExcel.Range("A3").Borders(8).LineStyle := 1 ; set top border line style for cell A3 (xlEdgeTop = 8, xlContinuous = 1)
oExcel.Range("A3").Borders(8).Weight := 2 ; set top border weight for cell A3 (xlThin = 2)
oExcel.Range("A3").Font.Bold := 1 ; set bold font for cell A3

A1 := oExcel.Range("A1").Value ; get value from cell A1, and store it in A1 variable
oExcel.Range("A4").Select ; select A4 cell
oExcel.Visible := 1 ; make Excel Application visible
MsgBox % A1 "`n" oExcel.Range("A2").Value ; check. You can use Round() function to round numbers to the nearest integer
ExitApp
[Mod edit: [code][/code] tags added.]


Many thanks in advance.
Regards.
User avatar
Chunjee
Posts: 754
Joined: 18 Apr 2014, 19:05
GitHub: Chunjee

Re: Reading Excel values into variables

17 Nov 2020, 19:09

Instead of oExcel.Workbooks.Add; do oExcel.Workbooks.Open(filePath)

Examples on my github: https://github.com/search?q=user%3AChunjee+workbooks.open&type=code
User avatar
Xeo786
Posts: 334
Joined: 09 Nov 2015, 02:43
Location: Karachi, Pakistan

Re: Reading Excel values into variables

18 Nov 2020, 00:55

ComObjGet read files

Code: Select all

xl := ComObjGet(inputfile) ; this will read file but excel actually open it in background 
for sheet in xl.sheets
{
	msgbox, %	"sheet Name:	" sheet.name 
	. "`n Cell address :	" sheet.range("a1").address[0,0]
	. "`n Cell Value :	" sheet.range("a1").value
	. "`n Cell Text  :	" sheet.range("a1").text
}
xl := "" ; this will simply close the file
"When there is no gravity, there is absolute vacuum and light travel with no time" -Game changer theory

Return to “Ask For Help”

Who is online

Users browsing this forum: AHKStudent, Bing [Bot], mikeyww, Spawnova, teadrinker, Xtra, Yamiko_Hikari and 49 guests