Need Help with Array 2

[dpotter]

I am having problem with my script, specially the array.

I am reading an excel worksheet (ShipData) and trying to read specific cells into my array if the load number matches user input.

I can get the wscript.echo to populate and the msgbox shows the right data, but when it begins to generate my data file, i have to stop it when it gets too big ( 75 mb plus) and I see that no data from the array made it into the loop as variables.

This is lengthy, but i'm new at this, so lots of screen feedback.

'-----------------------------------------------------------------------------------
'Define Dynamic Array - Works
'-----------------------------------------------------------------------------------
Dim shipmentsArray()
i = 0
nCount = 0

'-----------------------------------------------------------------------------------
'Ask User for Shipment Information Input - Works
'-----------------------------------------------------------------------------------

nShipments = InputBox ( "Count of DC's in Shipment ?", "How many DC's are in this shipment ?", "DC Shipment Count")
MsgBox ( "Count of DC's in Shipment is: " & nShipments )

nLoadNumber = InputBox ( "What is the Shipment Load Number ?", "What is shipment load number ?", "Shipment Load Number")
MsgBox ( "Shipment Load Number is: " & nLoadNumber )

strShipDate = InputBox ( "What is the Shipment Date ?", "Date of Shipment ?", "Ship Date")
MsgBox ( "Shipment Date is: " & strShipDate )

strShipTime = InputBox ( "What is the Time the Shipment left the Dock ?", "Time of Shipment ?", "Ship Time")
MsgBox ( "Shipment Date is: " & strShipTime )

'-----------------------------------------------------------------------------------
'ReDimension Dynamic Array - Works
'-----------------------------------------------------------------------------------
nCount = nShipments

ReDim shipmentsArray((nCount*4)*11)
'ReDim shipmentsArray(nCount*4,11)

For i = 0 To nCount - 1
shipmentsArray(i) = ""
Next

MsgBox "The upper bound of the shipmentsArray() array is " & UBound(shipmentsArray)

'-----------------------------------------------------------------------------------
'Open Excel File and read row information with For Next Loop.
'-----------------------------------------------------------------------------------
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Users\David Potter\Desktop\Worldwise\Tar_856_11-20-09.xlsm")
objExcel.ActiveWorkbook.Sheets("ShipData").Visible = True

intRow = 29 'What row to start on

Do Until objExcel.Cells(intRow,2).Value = "137646" ' Loop until column 2 is past 2nd order
' change to blank after testing.
If objExcel.Cells(intRow, 12).Value = CLng(nLoadNumber) Then 'User input = nLoadNumber = 409500
'MsgBox ("Row : " & intRow & ": Value is true: " & nLoadNumber)

shipmentsArray(i) = ( objExcel.Cells(intRow, 20).Value & _
objExcel.Cells(intRow, 3).Value & _
objExcel.Cells(intRow, 13).Value & _
objExcel.Cells(intRow, 17).Value & _
objExcel.Cells(intRow, 5).Value & _
objExcel.Cells(intRow, 1).Value & _
objExcel.Cells(intRow, 4).Value & _
objExcel.Cells(intRow, 18).Value & _
objExcel.Cells(intRow, 8).Value & _
objExcel.Cells(intRow, 11).Value & _
objExcel.Cells(intRow, 9).Value )

WScript.Echo ( intRow & vbCrLf & "ControlID#: " & objExcel.Cells(intRow, 20).Value & vbCrLf & _
"Order #: " & objExcel.Cells(intRow, 3).Value & vbCrLf & _
"SCAC #: " & objExcel.Cells(intRow, 13).Value & vbCrLf & _
"Carrier #: " & objExcel.Cells(intRow, 17).Value & vbCrLf & _
"DC Name #: " & objExcel.Cells(intRow, 5).Value & vbCrLf & _
"DC 4-digit #: " & objExcel.Cells(intRow, 1).Value & vbCrLf & _
"PO Number #: " & objExcel.Cells(intRow, 4).Value & vbCrLf & _
"PO Date #: " & objExcel.Cells(intRow, 18).Value & vbCrLf & _
"Carton Qty. #: " & objExcel.Cells(intRow, 8).Value & vbCrLf & _
"DPCI #: " & objExcel.Cells(intRow, 11).Value & vbCrLf & _
"SKU #: " & objExcel.Cells(intRow, 9).Value & vbCrLf )

MsgBox ( intRow & vbCrLf & "ControlID#: " & objExcel.Cells(intRow, 20).Value & vbCrLf & _
"Order #: " & objExcel.Cells(intRow, 3).Value & vbCrLf & _
"SCAC #: " & objExcel.Cells(intRow, 13).Value & vbCrLf & _
"Carrier #: " & objExcel.Cells(intRow, 17).Value & vbCrLf & _
"DC Name #: " & objExcel.Cells(intRow, 5).Value & vbCrLf & _
"DC 4-digit #: " & objExcel.Cells(intRow, 1).Value & vbCrLf & _
"PO Number #: " & objExcel.Cells(intRow, 4).Value & vbCrLf & _
"PO Date #: " & objExcel.Cells(intRow, 18).Value & vbCrLf & _
"Carton Qty. #: " & objExcel.Cells(intRow, 8).Value & vbCrLf & _
"DPCI #: " & objExcel.Cells(intRow, 11).Value & vbCrLf & _
"SKU #: " & objExcel.Cells(intRow, 9).Value & vbCrLf )


intRow = intRow + 1

Else MsgBox ("Row : " & intRow & ": Value is false" )

intRow = intRow + 1
End If
Loop

objExcel.Quit

 

[mrmovie]

shipmentsArray(i) = ( objExcel.Cells(intRow, 20).Value & _
objExcel.Cells(intRow, 3).Value & _
objExcel.Cells(intRow, 13).Value & _


'''doesnt that need to be something like

shipmentsArray(intRow - 29) = ( objExcel.Cells...

 

[dpotter]

I only start on row 29 to speed up the testing.

I usually start on row 3, since the first two rows have header detail.

The script does loop through the excel file and displays the correct info on the wscript.echo and the msgbox at the appropriate rows. The problem is, no data is stored in the array that i can see. When it drops further down on the script into passing the data back to a Do While loop, no data is applied to the loop. I am sure I am doing something wrong.

Thanks in advance for any and all help.

 

[HarleyQuinn]

i will be set to nCount-1 outside of your loop. You then use i to specify the element of the array for which you wish to populate. So it will always populate (and overwrite) shipmentsArray(nCount-1)

If you try using mrmovie's suggestion but using shipmentsArray(intRow - 3) = ( objExcel.Cells... if you're not testing it should work as described in both his and my posts.

Hope this helps

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[mrmovie]

thanks HarleyQuinn,
one might say more explicitly that i whilst is not incremented inside the Do Loop.

i dont like my use of (intRow -29) as the -29 is static and should really reflect the intRow itself, or more accurately the first value of it...anyway

actually HarleyQuinn, sorry for my ignorance but (intRow - 3) will be 29 - 3 so the first 26 elements of the array will be blank?

@dpotter, "I usually start on row 3, since the first two rows have header detail.
", the array doesnt care what row you are populating or starting from in your excel. the arrays use appears to be as a method of taking information out of excel and then writing it somewhere else....therefore populate your array from array(0) (its doesnt matter that the information came from row 34 blaa blaa) and work upwards, i.e. if you insist on the use of i then set "i = 0" outside the loop, then increment "i = i + 1" inside it...

 

[HarleyQuinn]

Yes, i is never reset before or incremented during the loop would be a more explicit way to say it.

IntRow - 3 was in reference to dpotter saying that the use of IntRow = 29 was for testing and that the usual value for IntRow would start at 3 to ignore the header rows (so uses the same principle as you to get to the first array element).

While using the static isn't an ideal solution, providing the sheet's structure doesn't change it shouldn't be a problem (as I imagine that the IntRow = X line is going to be hardcoded as well). Though the correct use of i in this situation (as per your example) would make the code much more dynamic.

Regards

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com

 

[mrmovie]

beg your pardon HarleyQuinn, seen the ref to 3 now funny how you think you read something through properly but end up missing all the detail!

 

[dpotter]

Thank you both for the valuable information....i am still learning about array's.

I have posted the entire script to egghead, if you care to point out more errors. i would welcome the critique.

If anyone knows of a better place to upload for public access, i am looking for that as well.

http://www.eggheadcafe.com/fileupload/-1676203221_Target_856_TL_Script.zip

 

[HarleyQuinn]

I know the feeling all too well mrmovie

Andy
---------------------------------
[green]' Signature removed for testing purposes.[/green]

http://lessthandot.com