All Forums >> [Scripting] >> WSH & Client Side VBScript >> RE: excel to array, perform functions to convert units of measure then save xls Do you like VisualBasicScript.com? Link to us and help spread the word about our forum. Thanks!
(3) To make it easier to reference specific rows, I added a column AE = Recno and filled it with consecutive numbers 2..3059.
(4) Using Ctrl+Pos1 resp. Ctrl+End, I verified that Excel agrees with me about the number of valid records/rows
(5) Using this SQL-Script
and a bit of manual editing, I came up with this table:
it shows:
(a) all cells of the columns [old whse], [Old Description], [Lawson UOM], [Lot-Code], [lawson Amount(soh)], [CS Amount(soh)] (, and [Recno] of course) contain not null/ non empty data. that looks good.
(b) the columns [Old Des# #2], [lawson Net Package], and [lawson Package SOH Qty] may have missing data
(c) the empties in columns like [Old-Prod-Code] may or may not be fatal; that's for you to decide
Please check/update/comment my assumptions about the fill/overwrite actions. Is it really a good idea to overwrite a column ([CS Amount(soh)]). Or did I misunderstand your code?
(6) Based on your code:
If arrExcelData(9) = "EA" Then ... ElseIf arrExcelData(9) = "PA" Then ... ElseIf arrExcelData(9) = "CT" Then ... ElseIf arrExcelData(9) = "TK" Then ... End If
and:
If arrExcelData(9) = "EA" And arrExcelData(14) = "E" Then ... ElseIf arrExcelData(9) = "GR" And arrExcelData(14) = "G" Then ...
I assume that (the content of) [Lawson UOM] and [CS UOM ] determine, how to do the updates. Using this code in the sql script:
I got this:
The first tables shows, that [Lawson UOM] is ok: all 3058 cells contain values. The second one proves: [CS UOM ] contains 108 empties (2950 + 108 = 3058). I'd think that this is a problem. Code like
If ... And [CS UOM ] = "..." Then ElseIf ... And [CS UOM ] = "..." Then ... End If
won't touch those 108 rows. What do you think?
The third table shows all combinations of [Lawson UOM] and [CS UOM ] values. The rows
EA NULL 22 GA NULL 1 LB NULL 83 TK NULL 2
reflect the 108 empties of [CS UOM ].
(7) Do you think that using the third table and adding some comments about the updates/ conversions would make sense:
(8) To get the [CS UOM ] empties, I added
and got
Perhaps you can check some these to determine their status/quality.
(9) Now lets start with some code to do the updates. Starting with
we prove that we can loop over all our 3058 records/rows.
Adding a select switch based on the [Lawson UOM]/[CS UOM ]-token table:
(mark the catch all else "Case Else"), I verified that all rows/records would be handled. If you can specify which actions to take, we can start to put code into the Case sections.
here is what im doing exactly. in lawson(old db) units for an item may be measured in gallons. in cs(new db), they will be measured in pounds. so i take
and compare 9 J lawson uom with 14 O cd uom to find out gallons to pounds. then i calculate 17 R lawson soh (shipment on hand) in gallons and calculate to be how many units on hand in pounds (becoming 18 S cs soh) -(there are a few that will stay the same like lb -> lb thats where 18.value = 17.value then i change 11 L (the cost per uom) to the new cost per uom(20 U) cs package soh is also a function of lawson package soh (sometimes a package is 25KG and no other way to sell even if uom change, sometimes is simply changed with uom im not sure exactly on thos yet) total and total1 compare whther the calculations were right and show the total value of the inventory before and after calculations. (to compare i would simply use autosum in excel)
EDIT - so using your above code, i would insert the second bit within the loop and perform calculations under each "case" line? and yes cs soh should be null going in, then overwrite after we get correct values.
< Message edited by chriswebb18 -- 7/1/2008 12:20:57 AM >
on a side note, i dont believe this will work using the script the way i have as it is not that the info cannot be stored as a variable but it cannot read excel cell with that decimal information(i think). i tweaked my script and redid in vba, then dimmed all ints as singles. when i try to display the singles after they are set to a cell with a real number, they show blank... ado/sql may be the only way to go.
so using your above code, i would insert the second bit within the loop and perform calculations under each "case" line?
==> yes; in principle, after we solved all other problems
and yes cs soh should be null going in, then overwrite after we get correct values.
==> what exactly is "cs soh"? I pointed out problems with "[CS UOM ] contains 108 empties (2950 + 108 = 3058)" and asked about overwriting "Is it really a good idea to overwrite a column ([CS Amount(soh)]). Or did I misunderstand your code? "
pertaining to cs uom -> there are some items that we do not have any units or measure for the new (cs) database. those we can leave or modify, it wont matter. they are going to manually change them later.
pertaining to cs amount(soh) -> that is the number of units we currently have on hand (shipment on hand). the input should be all empty in that column. it is necessary to have that column filled after the conversion. say we have 100 lb of something in lawson the lawson amount(soh) is 100. we convert to kg (100 / 55.1150 * 25) and the cs amount(soh) would be 45.3957... then i convert the cost per uom(per lb to per kg) and make sure that when we multiply our lawson cost per uom * lawson amount(soh) = cs cost per uom * cs amount(soh) is the same. thats where total and total1 come in. hope i made that clear
do i need to put the "if...end if" after each case, or do the "case"'s work as the conditional ie
or
< Message edited by chriswebb18 -- 7/1/2008 1:06:32 AM >
Case "CT-E" ' CT E 1 If arrExcelData(9) = "CT" Then sngNewSOH = 0 sngNewSOH = arrExcelData(17) * arrExcelData(13) objSheet.Cells(sngRow, 24).Value = arrExcelData(22) objSheet.Cells(sngRow, 19).Value = sngNewSOH End If
we should have something like:
Case "CT-E" ' CT E 1 *** explanation of what CT combined with E means (and a hint why this combo occurs just once) objSheet.Cells(iRow, [decent name from the list]).Value = some expression, perhaps refering to objSheet.Cells(iRow, [other decent name from the list]).Value ... End If
I don't know about you, but I won't use any arrays.
After your
pertaining to cs uom -> there are some items that we do not have any units or measure for the new (cs) database. those we can leave or modify, it wont matter. they are going to manually change them later.
at least the cases for the [CS UOM ] = Null are easy:
Case "EA-NULL" ' EA NULL 22 ** [CS UOM ] is empty; must be updated manually later ' nothing to be done ....
ok, so im goig to put this into that format, but still the issue with real numbers... even when i try to perform calculations directly as so: objsheet.cells(w,x) = objsheet.cells(y,z).value / a * b i get a null answer if there are real numbers.
this is what i used to change to start getting rid of the array. then i will simply move the conditionals to the new "CASE" structure. and i changed recno to a field that is complete all the way down. so this way instead of numbers, it uses the name of the field if i understand right
Is there any chance of locale/regional settings problems? Did you use step 5 to generate the Fields table? Did you check whether the columns you want to compute with are of data type 5 (double)? Did you check the cell/column format in Excel?
I have no problems with the second .xls you sent to me.
ok i have not tested it in your new format. i am trying to get everything so that it is formatted to work with your structur (ie no array and irow, [ ] instead of arrexceldata and sngrow, #) i am going to pull it all over now(i am trying to avoid having to rewrite all my conditionals.) i just hadnt seen where in your code it was changed to handle the real numbers. ill let you know once its moved and tested. And thanks for all your help. i know youve spent a lot of time helping me and explained things to me so i understand. hopefully i wont need as much help next time now
Printable Version
RE: excel to array, perform functions to convert units ... - 
