How to save safearray to excel?

[ktbjx]

ok this is a stupid question, but how will you save datas from safearray into excel????

i know how to put excel datas into safearray, but how do you save datas in safearray into excel????

like excel to safearray it would be:

Code: Select all

MainArray := oWorkbook.Range("A1:Z"lstrw).Value

i tried reversing it, but it doesnt work

Code: Select all

oWorkbook := ComObjCreate("Excel.Application")
oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "ASSAY"
oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "SURVEY"
oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "HEADER"
oWorkbook.Worksheets("HEADER").Activate

cHead := 2
loop, % HEADERArray.MaxIndex()
{

oWorkbook.Worksheets("HEADER").Range("A1:Z"cHead).Value := HEADERArray[cHead, 1]
		cHead ++
}
cSurv := 2
loop, % SURVEYArray.MaxIndex()
{
oWorkbook.Worksheets("SURVEY").Range("A1:D"cSurv).Value := SURVEYArray[cSurv, 1]
cSurv ++
}
cAssa := 2
loop, % ASSAYArray.MaxIndex()
{
oWorkbook.Worksheets("ASSAY").Range("A1:H"cAssa).Value := ASSAYArray[cAssa, 1]
cAssa ++
}

[sinkfaze]

Look at your first loop, for example:

Code: Select all

cHead :=	2
loop, %	HEADERArray.MaxIndex()
{
	oWorkbook.Worksheets("HEADER").Range("A1:Z"cHead).Value := HEADERArray[cHead, 1]
	cHead++
}

Your range changes but you're only pulling a single value from the SafeArray, so no matter what it will always push that one value into the specified range. If you want to push the entire SafeArray, you must have a call for a range which shares the SafeArray's same dimensions.

Example:

Code: Select all

xl :=	ComObjActive("Excel.Application")
 , xl.screenUpdating :=	0
For cell in xl.Range["C3:E5"]	; generate sample data block
	cell.Value :=	A_Index
sArr :=	xl.Range["C3:E5"].Value
xl.Range["G7:I9"].Value :=	sArr
xl.screenUpdating :=	1

Luckily, you can select one cell and use the dimensions of the array to resize the area to push it to:

Code: Select all

xl :=	ComObjActive("Excel.Application")
 , xl.screenUpdating :=	0
For cell in xl.Range["C3:E5"]
	cell.Value :=	A_Index
sArr :=	xl.Range["C3:E5"].Value
xl.Range["G7"].Resize[sArr.MaxIndex(1),sArr.MaxIndex(2)].Value :=	sArr
xl.screenUpdating :=	1

So in place of your previously mentioned loop:

oWorkbook.Worksheets["HEADER"].Range["A1"].Resize[HEADERArray.MaxIndex(1),HEADERArray.MaxIndex(2)].Value := HEADERArray

[kczx3]

Be careful using [ and ] with methods. Pretty sure that doesn’t work in v2 anymore.

[ktbjx]

Look at your first loop, for example:

Code: Select all

cHead :=	2
loop, %	HEADERArray.MaxIndex()
{
	oWorkbook.Worksheets("HEADER").Range("A1:Z"cHead).Value := HEADERArray[cHead, 1]
	cHead++
}

Your range changes but you're only pulling a single value from the SafeArray, so no matter what it will always push that one value into the specified range. If you want to push the entire SafeArray, you must have a call for a range which shares the SafeArray's same dimensions.

Example:

Code: Select all

xl :=	ComObjActive("Excel.Application")
 , xl.screenUpdating :=	0
For cell in xl.Range["C3:E5"]	; generate sample data block
	cell.Value :=	A_Index
sArr :=	xl.Range["C3:E5"].Value
xl.Range["G7:I9"].Value :=	sArr
xl.screenUpdating :=	1

Luckily, you can select one cell and use the dimensions of the array to resize the area to push it to:

Code: Select all

xl :=	ComObjActive("Excel.Application")
 , xl.screenUpdating :=	0
For cell in xl.Range["C3:E5"]
	cell.Value :=	A_Index
sArr :=	xl.Range["C3:E5"].Value
xl.Range["G7"].Resize[sArr.MaxIndex(1),sArr.MaxIndex(2)].Value :=	sArr
xl.screenUpdating :=	1

So in place of your previously mentioned loop:

oWorkbook.Worksheets["HEADER"].Range["A1"].Resize[HEADERArray.MaxIndex(1),HEADERArray.MaxIndex(2)].Value := HEADERArray

well, i was trying to compute stuffs in memory so i thought safearray would do that faster rather than working on COM
so I thought why not get all the data from excel and close the COM. then work from the SAFEARRAY data collected in excel
in your example sir, you are getting all the data and pushing it all back... so does this mean that i cannot manipulate a single cell like this?

Code: Select all

HEADERArray[1, 1]:= "HOLE-ID"
HEADERArray[1, 2]:= "LOCATIONX"
HEADERArray[1, 3]:= "LOCATIONY"
HEADERArray[1, 4]:= "LOCATIONZ"

or pass the data from one array to another? like this?

Code: Select all

HEADERArray[1,1]:= MainArray[cc, 12]
HEADERArray[1,2]:= MainArray[cc, 1]
HEADERArray[1,3]:= MainArray[cc, 2]
HEADERArray[1,4]:= MainArray[cc, 6]
HEADERArray[1,5]:= MainArray[cc, 14]
HEADERArray[1,6]:= MainArray[cc, 26]
HEADERArray[1,7]:= 1900
HEADERArray[1,8]:= QSOURCE

[sinkfaze]

Manipulating the data within a range of cells will be much faster and easier in a SafeArray as opposed to looping through cells via COM. I thought your question pertained to how to put the data back into Excel when you are finished doing that.

[ktbjx]

Manipulating the data within a range of cells will be much faster and easier in a SafeArray as opposed to looping through cells via COM. I thought your question pertained to how to put the data back into Excel when you are finished doing that.

Yes sir, my thought exactly, thats why im trying to research more about safearrays and manipulating them..
And yes you got it right, thats my question... but I figured, Since im on the topic of SafeArrays, maybe i could still ask some experts about manipulating it.
like, adding data into a specific space of a safeArray.
like

Code: Select all

HEADERArray[1, 10]:= "PNL_WIDTH"

or getting a specific cell in a safeArray, and passing it on to another specific space on a safearray
like

Code: Select all

HEADERArray[1,5]:= MainArray[2, 14]

coz it doesnt seem to work. I havent have much clue on this Autohotkey programming and Im just learning along the way.
i know my syntax is wrong. Bottom line, please teach me.

If you need my whole code. i could show it. But it doesnt work ATM
because of those problems i have

[sinkfaze]

I can modify elements within a SafeArray in exactly the manner that you demonstrate, so I think posting your code would be appropriate in this instance.

[ktbjx]

I can modify elements within a SafeArray in exactly the manner that you demonstrate, so I think posting your code would be appropriate in this instance.

@sinkfaze Here you go... Its not Working.

Code: Select all

#SingleInstance, Force
#NoEnv
ListLines Off
Process, Priority,%PID% , H


fileselectfile, path,,%A_ScriptDir%,Choose File to Generate your ChCut,(*.xls; *.xlsx)
if ERRORLEVEL
{
exitapp
}
else

Gui +LastFound +OwnDialogs +AlwaysOnTop 
InputBox, FileNaming,Enter Output FileName!,,,200,100,880,540

cc:=2


SetBatchLines, -1
SetConTrolDelay, 0


;oWorkbook :=  ComObjGet(path)
xl:= ComObjCreate("Excel.Application")
oWorkbook :=xl.Workbooks.Open(path)
oWorkbook.Application.Windows(oWorkbook.Name).Visible := True
oWorkbook.Application.DisplayAlerts := 0

lstrw := oWorkbook.ActiveSheet.UsedRange.rows.count
	
oWorkbook.Application.ScreenUpdating:=False

xlDown := -4121
xlPasteAll := -4104
xlPasteSpecialOperationMultiply := 4

oWorkbook.ActiveSheet.Range("L1").Value := 1
xlApp := ComObjActive("Excel.Application")
xlApp.Range("L1").Copy
rngD2 := xlApp.Range("G2:H2")
rng := xlApp.Range(rngD2, rngD2.End(xlDown))
rng.PasteSpecial(xlPasteAll, xlPasteSpecialOperationMultiply)

	
xlApp.ActiveSheet.Range("L1").Delete


neg1 = =IF($K2=$K1,L1,A2)
neg2 = =IF($K2=$K1,M1,B2)
neg3 = =IF($K2=$K3,N3,C2)
neg4 = =IF($K2=$K3,O3,D2)
neg8 = =ROUND(SQRT((L2-N2)^2+(M2-O2)^2),4)
neg9 = =ROUND(P2/(COUNTIF(K:K,K2)),4)
neg10 = =IF(K1=K2,Q2+R1,Q2)
neg11 = =ROUND(IF((ATAN2((O2-M2),(N2-L2))*180/PI())<0,(ATAN2((O2-M2),(N2-L2))*180/PI())+360,(ATAN2((O2-M2),(N2-L2))*180/PI())),0)
lstrwq := oWorkbook.ActiveSheet.UsedRange.rows.count

oWorkbook.Activesheet.Range("L2").Value := neg1
oWorkbook.Activesheet.Range("M2").Value := neg2
oWorkbook.Activesheet.Range("N2").Value := neg3
oWorkbook.Activesheet.Range("O2").Value := neg4
oWorkbook.Activesheet.Range("P2").Value := neg8
oWorkbook.Activesheet.Range("Q2").Value := neg9
oWorkbook.Activesheet.Range("R2").Value := neg10
oWorkbook.Activesheet.Range("S2").Value := neg11
	
oWorkbook.Activesheet.Range("L2:S2").AutoFill(oWorkbook.Activesheet.Range("L2:S"lstrwq))
oWorkbook.Activesheet.Range("L2:S"lstrwq).Value := oWorkbook.Activesheet.Range("L2:S"lstrwq).value
	
oWorkbook.Activesheet.Range("L:O").Delete
oWorkbook.Activesheet.Range("M:M").Delete
oWorkbook.ActiveSheet.Columns("L:M").Insert
;###############################################yung ArcTangent ng angle
oWorkbook.ActiveSheet.Range("M:M").Value := oWorkbook.ActiveSheet.Range("P:P").Value
oWorkbook.Activesheet.Range("P:P").Delete




MainArray := oWorkbook.Activesheet.Range("A1:Z"lstrwq).Value
oWorkbook.Close(1)	
oWorkBook := "", oSheet := ""
xlApp.Quit()
xl.Quit()

current:= part:= 100 / lstrw
lstrw1 := lstrw-1

Gui, Destroy
Progress, b w200,, Generating ASSAY..., ScoRm
	
Loop, %lstrw1%
{
result = %a_index%
result :=result+1
result1 = %a_index%
ccc:=cc-1
var1:=Floor(MainArray[cc, 11])
var1:=SubStr("0000000" var1, -6)
var2 = 62-%var1%

MainArray[cc, 12]:=var2

QSOURCE = EXPLO_LAD
HEADERArray[cc,1]:= MainArray[cc, 12]
HEADERArray[cc,2]:= MainArray[cc, 1]
HEADERArray[cc,3]:= MainArray[cc, 2]
HEADERArray[cc,4]:= MainArray[cc, 6]
HEADERArray[cc,5]:= MainArray[cc, 14]
HEADERArray[cc,6]:= MainArray[cc, 26]
HEADERArray[cc,7]:= 1900
HEADERArray[cc,8]:= QSOURCE


SURVEYArray[cc, 1]:= MainArray[cc, 12]
SURVEYArray[cc, 2]:= 0
SURVEYArray[cc, 3]:= MainArray[cc, 13]
SURVEYArray[cc, 4]:= 0

ASSAYArray[cc, 1]:= MainArray[cc, 12]
;assayFrom
If (MainArray[result, 1] = MainArray[result1, 1])
{
	ASSAYArray[cc, 2]:= MainArray[result1, 3]
}
Else
{
ASSAYArray[cc, 2]:= 0
}

ASSAYArray[cc, 3]:= MainArray[cc, 15]
ASSAYArray[cc, 4]:= ASSAYArray[result, 3] - ASSAYArray[result, 2]
ASSAYArray[cc, 5]:= MainArray[cc, 9]
ASSAYArray[cc, 6]:= MainArray[cc, 10]
ASSAYArray[cc, 7]:= ROUND(MainArray[result, 5]+(MainArray[result, 6]*0.701),2)

cc:=cc+1
Progress, %current%
current +=part
}
MainArray[1, 12]:= "Hole ID"
MainArray[1, 13]:= "Count"
MainArray[1, 14]:= "Length"

HEADERArray[1, 1]:= "HOLE-ID"
HEADERArray[1, 2]:= "LOCATIONX"
HEADERArray[1, 3]:= "LOCATIONY"
HEADERArray[1, 4]:= "LOCATIONZ"
HEADERArray[1, 5]:= "LENGTH"
HEADERArray[1, 6]:= "CATEGORY"
HEADERArray[1, 7]:= "AREA"
HEADERArray[1, 8]:= "MINING_BLK"
HEADERArray[1, 9]:= "PANEL_ID"
HEADERArray[1, 10]:= "PNL_WIDTH"
HEADERArray[1, 11]:= "VEIN"
HEADERArray[1, 12]:= "ZONE"
HEADERArray[1, 13]:= "DATE"
HEADERArray[1, 14]:= "YEAR"
HEADERArray[1, 15]:= "AREA2"
HEADERArray[1, 16]:= "SOURCE"
HEADERArray[1, 17]:= "REMARKS"
HEADERArray[1, 18]:= "COMMENTS"

SURVEYArray[1, 1]:= "HoleID"
SURVEYArray[1, 2]:= "Distance"
SURVEYArray[1, 3]:= "Azimuth"
SURVEYArray[1, 4]:= "Dip"

ASSAYArray[1, 1] := "HoleID"
ASSAYArray[1, 2] := "From"
ASSAYArray[1, 3] := "To"
ASSAYArray[1, 4] := "Length"
ASSAYArray[1, 5] := "Cu"
ASSAYArray[1, 6] := "Au"
ASSAYArray[1, 7] := "CuEQ"
	

Progress, off
lstrw2 := HEADERArray.MaxIndex() + 1
lstrw3 := lstrw2-1
cc:=2
current:= part:= 100 / lstrw2
Progress, b w200,, Generating HEADER...,  


oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "ASSAY"
oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "SURVEY"
oSheet := oWorkbook.Worksheets.Add()
oSheet.Name := "HEADER"
oWorkbook.Worksheets("HEADER").Activate


cHead := 2
loop, % HEADERArray.MaxIndex()
{

oWorkbook.Worksheets("HEADER").Range("A1:Z"cHead).Value := HEADERArray
;oWorkbook.Worksheets("HEADER").Range("A1:Z"cHead).Value := HEADERArray[cHead, 1]
		cHead ++
}
cSurv := 2
loop, % SURVEYArray.MaxIndex()
{
oWorkbook.Worksheets("SURVEY").Range("A1:D"cSurv).Value := SURVEYArray
;oWorkbook.Worksheets("SURVEY").Range("A1:D"cSurv).Value := SURVEYArray[cSurv, 1]
cSurv ++
}
cAssa := 2
loop, % ASSAYArray.MaxIndex()
{
oWorkbook.Worksheets("ASSAY").Range("A1:H"cAssa).Value := ASSAYArray
;oWorkbook.Worksheets("ASSAY").Range("A1:H"cAssa).Value := ASSAYArray[cAssa, 1]
cAssa ++
}

file_path := A_Desktop "\" FileNaming ".xlsx"
oWorkbook.Application.DisplayAlerts := 1	
oWorkbook.Worksheets("HEADER").SaveAs(file_path, 51)

oWorkbook.Save()
oWorkbook.Close(1)	
oWorkBook := "", oSheet := ""
xlApp.Quit()
xl.Quit()
ExitApp

[DRocks]

Code: Select all

	SafeArray := xlCOM.Range("D"FirstRow ":N"LastRow).Value					; 2 dimensions array (ColonneXL, RangeeXL)
	
	;MsgBox % SafeArray.MaxIndex(1)									
	;MsgBox % SafeArray.MaxIndex(2)									
	MsgBox % SafeArray[1,1]											; ok
	
	For i, value in SafeArray
		MsgBox % SafeArray[i, value]									; why does this pop-up error (not implemented)
	For i in SafeArray
	MsgBox % SafeArray[i]										; why does this pop-up error (not implemented)

Why doesnt a for index, value loop work with safearray like normally?
Yeah, maybe k, value is dumb here but even just looping index pops up not implemented, any ideas why?

[sinkfaze]

ktbjx,

I'm not seeing where the SafeArrays that you're trying to modify the data for were actually created. Take HEADERArray, for example. Did you copy this data out of Excel as a SafeArray? Or did you manually create it as a SafeArray?

[sinkfaze]

DRocks,

For-loops cannot parse a SafeArray in that way.

Code: Select all

SafeArray :=	xlCOM.Range("D" FirstRow ":N" LastRow).Value

Loop %	SafeArray.MaxIndex(1)
{
	i :=	A_Index
	Loop %	SafeArray.MaxIndex(2)
			MsgBox %	SafeArray[i,A_Index]
}

Or:

Code: Select all

SafeArray :=	xlCOM.Range("D" FirstRow ":N" LastRow)	; no .Value

For row in SafeArray
	For cell in row
		MsgBox %	cell.Value

[DRocks]

Thank you very much for your examples. You have just shown me something I did not understand before and it will be super useful.

Thank you again

[DRocks]

I've been trying to implement Excel COM with Sinkfaze tips.
The issue I wanted to solve is this:
We get client's Excel files with about 15 different sheets.
Each of these sheets have a particular column naming order which clients choose to their likings.
I want to retrieve a row data and convert it to my accounting software for importation with AHK.
And as you can guess, because of the randomness of how each client choose their column order over a possibility of 15+ sheets, it can be very time consumming to manually assign a fixed column order to a usable object.

So this script example is generating a global object called invoiceObj[].
The steps are :
1. initialize the global object invoiceObj[] with default values - mostly structure and column name reg expressions
2. initialize the Excel ComObject and keep reference of its handle into xlApp variable.
3. find Excel headers matching the column names I am looking for with regular expressions I defined in the init. of invoiceObj[]
4. having stored the matching Excel columns in my invoiceObj[], I can then retrieve a Excel row and assign it's corresponding cell values to my invoiceObj.columnName.value
5. from there I will then convert those raw values to a compatible format for import in the accounting software. (this part is another un-related script which I did not include)

Here's the example:

Code: Select all

#SingleInstance,Force
#Persistent

GLOBAL invoiceObj := initializeInvoiceObj()
xlApp:= ComObjectConnexion("Excel.Application", xlApp)
xlHeadersRow:=2 ; column titles row number
ExcelRowToInvoiceObj(xlApp, xlHeadersRow, xlHeadersRow+1)
xlHeadersRow:=""
Return

;--------------------------------------------------------------------------------
ComObjectConnexion(ByRef AppNameString, ByRef ComObjHandle) {
;--------------------------------------------------------------------------------
	if (!ComObjHandle) {
		Try 
			ComObjHandle:= ComObjActive(AppNameString) ; creates handle to currently active excel sheet
		catch e {
			MsgBox,262208,Excel, Échec de connexion avec l'application Excel.`n`nAssurez-vous d'ouvrir le document Excel et réessayez.
			return
		}
	}
	if (!ComObjHandle.ActiveWorkbook.Name or !ComObjHandle.ActiveSheet.Name) {
		MsgBox,262208,Excel, Aucun document Excel detecté.`n`Assurez-vous d'ouvrir le document Excel et réessayez.
		return
	}
	return ComObjHandle
}

;--------------------------------------------------------------------------------
initializeInvoiceObj() {
;--------------------------------------------------------------------------------
	CRITICAL
	colNames:=[ "nom"
			  , "credit"
			  , "debit"
			  , "proprio"
			  , "noFac"
			  , "date"
			  , "dateP"
			  , "montant"
			  , "rabais"
			  , "pourboire"
			  , "transport"
			  , "tps"
			  , "tvq"
			  , "total"
			  , "devise"
			  , "totalCAD"
			  , "description"
			  , "libre_1"
			  , "libre_2"
			  , "libre_3" ]
	colNamesRegexs:=[ "i)^(nom|magasin|fournisseur|client|endroit|lieu)$"
				    , "i)cr.dit|visa|master.?car:"
				    , "i)d.bit|interac|.*courant|ch.que"
				    , "i)propri(o|.taire)|actionn?aire"
				    , "i)facture"
				    , "i)^date$"
				    , "i)^date pay."
				    , "i)^(montant|prix|prix avant taxe.?)$"
				    , "i)rabais|discount"
				    , "i)^(pourboire|^tips)$"
				    , "i)^(transport|livraison)$"
				    , "i)^(tps|tvh|gst)$"
				    , "i)^(tvq|pst)$"
				    , "i)^total$"
				    , "i)^devise$"
				    , "i)CAD|canadien"
				    , "i)^(description|commentaire.?|note.?|raison)$"
				    , "^libre_\d$"
				    , "^libre_\d$"
				    , "^libre_\d$" ]
	
	returnedObject:=[]
	For colIndex, colName in colNames {
		; based on each colNames (nom, date, total, ...)
		returnedObject[colName, "regex"	 ] := colNamesRegexs[colIndex]
		
	}
	return returnedObject
}
;--------------------------------------------------------------------------------
ExcelRowToInvoiceObj(ByRef xlApp, ByRef xlHeadersRow, ByRef xlRow) {
;--------------------------------------------------------------------------------
	GLOBAL invoiceObj
	CRITICAL
	timerStart:=A_TickCount
	
	; Reset values that are used with the .= operator so that they don't stack up
	For nameIndex in invoiceObj {
		invoiceObj[nameIndex, "value"			] := ""
		invoiceObj[nameIndex, "xlColMatchNumber"] := ""
		;invoiceObj[nameIndex, "xlColMatchName"] := ""
	}
	
	; Get Excel Column name matchings into invoiceObj.nameIndex.xlColMatchNumber
	xlSafeArray := xlApp.Range("A" xlHeadersRow ":T" xlHeadersRow).Value
	Loop, % xlSafeArray.MaxIndex(2) {
		index := A_Index
		cellValue := xlSafeArray[1, index]
		if (cellValue) {
			For nameIndex in invoiceObj {
				if (RegExMatch(cellValue, invoiceObj[nameIndex, "regex"])) {
					invoiceObj[nameIndex, "xlColMatchNumber"] .= index
					;invoiceObj[nameIndex, "xlColMatchName"] .= cellValue
					break
				}
			}
		}
	}
	
	; set invoiceObj.nameIndex.value to its matching Excel cell value
	xlSafeArray := xlApp.Range("A" xlRow ":T" xlRow).Value
	For nameIndex in invoiceObj {
		xlColMatchNumber :=invoiceObj[nameIndex, "xlColMatchNumber"]
		if (xlColMatchNumber) {
			cellValue :=xlSafeArray[1, xlColMatchNumber]
			if (cellValue)
				invoiceObj[nameIndex, "value"] := cellValue
		}
	}
	
	MsgBox % "Execution time for "A_ThisFunc "() = " timeElapsed:=A_TickCount-timerStart "ms"
	;invoiceObj["xlSheet"] := xlApp.ActiveSheet.Name
}

I hope this can give others some ideas