4 Column Variable output from Excel Spreadsheet

[agags333]

Hi

I hope i explain this correctly
How does one have read in 4 columns of a spreadsheet and assign them to 4 variabls
eg
if the input of a variable ( from dropdown menu gui ) = XXX
Lookup XXX in Column A of spreadsheet,
And then assign Variable b the value adjacent to that Column, and Variable c the one adjacent to that, for 4 total columns

kinda like a vlookup, and then go 1, 2 and 3 columns and assign those to variables to be used later in the script.

[grant]

This will work with a spreadsheet that is open and active.

Code: Select all

Xl := ComObjActive("Excel.Application") ;creates a handle to your currently active excel sheet

cellvalue1 := Xl.Range("c2").Value ;sets your variable to the value of cell c2
cellvalue2 := Xl.Range("c3").Value ;sets your variable to the value of cell c3

If you want to use an excel file that is closed you will need to do a bit of reading. You might find something here https://autohotkey.com/board/topic/6903 ... for-excel/ or here https://autohotkey.com/board/topic/6545 ... ons-excel/

[FanaticGuru]

Hi

I hope i explain this correctly
How does one have read in 4 columns of a spreadsheet and assign them to 4 variabls
eg
if the input of a variable ( from dropdown menu gui ) = XXX
Lookup XXX in Column A of spreadsheet,
And then assign Variable b the value adjacent to that Column, and Variable c the one adjacent to that, for 4 total columns

kinda like a vlookup, and then go 1, 2 and 3 columns and assign those to variables to be used later in the script.

Code: Select all

Xl := ComObjActive("Excel.Application")

InputBox, Needle, Search, % "Workbook:`t" Xl.ActiveWorkbook.Name "`nSheet: `t`t" Xl.ActiveSheet.Name "`n`n" "Enter Search String for Column A"

Found := Xl.Columns("A").Find(Needle)
if !Found
{
	MsgBox NOT FOUND
	ExitApp
}

; Example of Assigning to Separate Variables
a := Found.Value 
b := Found.Offset(0,1).Value
c := Found.Offset(0,2).Value
d := Found.Offset(0,3).Value

MsgBox % a "`t" b "`t" c "`t" d

rng := Xl.Range(Found.Address ":" Found.Offset(0,3).Address)	; Example of Assigning a Range

for Cell in rng ; Example of Looping through a Range
	MsgBox % Cell.Value

FG

[kon]

Also
rng := Xl.Range(Found, Found.Offset(0,3)) ; Example of Assigning a Range

[FanaticGuru]

Also
rng := Xl.Range(Found, Found.Offset(0,3)) ; Example of Assigning a Range

Much preferred! Thanks for the refinement.

FG

[agags333]

Thank you all


How about referring to a csv on a server share?

[FanaticGuru]

Thank you all


How about referring to a csv on a server share?

Code: Select all

Xl := ComObjCreate("Excel.Application")
Xl.Visible := true ; change to false to have it all happen while hidden
Wb := Xl.Workbooks.Open("D:\User\FG\Documents\Test.csv") ; should work with a path like "\\READYNAS\Documents\Test.csv"
Msgbox % Wb.Range("A1")

FG