store excel array as a variable Topic is solved

[JKnight_xbt33]

Dear all,
I have an excel table with 5 columns (A to E) and 5 rows (1 TO 5)

I want to store the output of each individual cell as a variable.

I know how to output the array of columns but not each individual cell (see example below)

Does anyone know how to do this. for example if I want to store the value of A2 into a variable?

Code: Select all

file2open := "\\private\Users\JK\Documents\8-IT\AHK scripts\Preventing mistakes pdf check\test excel data.xlsx"
arr := {}
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

For c In xl.ActiveSheet.UsedRange.cells    
	arr[c.address(0,0)] := c.Value

loop, % xl.Activesheet.UsedRange.rows.count
comp := arr["a" a_index
conv := arr["c" a_index]
serial := arr["d" a_index]

msgbox % arr["a" a_index] arr["c" a_index] arr["d" a_index]

appreciated
J

[w0z]

Example using ComObjGet

Code: Select all

F1::
	FilePath := "\\private\Users\JK\Documents\8-IT\AHK scripts\Preventing mistakes pdf check\test excel data.xlsx"
	XL := ComObjGet(FilePath)

	a2cell := XL.Sheets("Sheet1").Range("A2").Text
	MsgBox, % a2cell
	XL=
Return

[w0z]

Code: Select all

#SingleInstance, force
F2::
	FilePath := "\\private\Users\JK\Documents\8-IT\AHK scripts\Preventing mistakes pdf check\test excel data.xlsx"
	xlApp := ComObjGet(FilePath)

	arr := {}
	sheetname := "Sheet1"

	For c In xlApp.Sheets(sheetname).UsedRange.cells    
		arr[c.address(0,0)] := c.Text

	comp=
	conv=
	serial=

	loop, % xlApp.Sheets(sheetname).UsedRange.rows.count
	{
		comp .= arr["a" a_index] "`n"
		conv .= arr["c" a_index] "`n"
		serial .= arr["d" a_index] "`n"

		msgbox,,, % arr["a" a_index] arr["c" a_index] arr["d" a_index],1
	}
	
	MsgBox, % comp conv serial
	
	a2cell :=  arr["A2"]
	MsgBox, % a2cell
	
	xlApp.Close()
	xlApp=
Return

[FanaticGuru]

Dear all,
I have an excel table with 5 columns (A to E) and 5 rows (1 TO 5)

I want to store the output of each individual cell as a variable.

I know how to output the array of columns but not each individual cell (see example below)

Does anyone know how to do this. for example if I want to store the value of A2 into a variable?

Code: Select all

file2open := "\\private\Users\JK\Documents\8-IT\AHK scripts\Preventing mistakes pdf check\test excel data.xlsx"
arr := {}
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

For c In xl.ActiveSheet.UsedRange.cells    
	arr[c.address(0,0)] := c.Value

loop, % xl.Activesheet.UsedRange.rows.count
comp := arr["a" a_index
conv := arr["c" a_index]
serial := arr["d" a_index]

msgbox % arr["a" a_index] arr["c" a_index] arr["d" a_index]

appreciated
J

You can also use Excel's native SafeArray:

Code: Select all

File2Open := A_Desktop "\Test\specificExcelFile.xlsx"
xlApp := ComObjCreate( "Excel.Application" )
xlWB := xlApp.Workbooks.Open(File2Open)
xlApp.Visible := true

SafeArray := xlWB.ActiveSheet.Range("B2:C4").Value

MsgBox % SafeArray[1,2] ; First Row, Second Column of Range

If you really just want A2 then:

Code: Select all

File2Open := A_Desktop "\Test\specificExcelFile.xlsx"
xlApp := ComObjCreate( "Excel.Application" )
xlWB := xlApp.Workbooks.Open(File2Open)
xlApp.Visible := true

xlCell_A2 := xlWB.ActiveSheet.Range("A2").Value

MsgBox % xlCell_A2

Then you have to think if you really need the values in a variable or array. I mean unless you are doing something very repetitive with the value it is hardly worth storing all the information, you can just access it as you need it.

Code: Select all

File2Open := A_Desktop "\Test\specificExcelFile.xlsx"
xlApp := ComObjCreate( "Excel.Application" )
xlWB := xlApp.Workbooks.Open(File2Open)
xlApp.Visible := true

for Cell in xlWB.ActiveSheet.Range("B2:C4")
		MsgBox % Cell.Value

Loop through all cells in a range.

FG

[JKnight_xbt33]

All the answers were good, I chose the the loop script below because I wont have to hard code each cell into the script I can just specify a range.

Code: Select all

File2Open := A_Desktop "\Test\specificExcelFile.xlsx"
xlApp := ComObjCreate( "Excel.Application" )
xlWB := xlApp.Workbooks.Open(File2Open)
xlApp.Visible := true

for Cell in xlWB.ActiveSheet.Range("B2:C4")
		MsgBox % Cell.Value

The next step will be to take the values in all the cells in a specified range and compare it to the values outputted from a pdf file elsewhere then it will let me know if there's a discrepancy.

appreciated
J