Photo Gallery Member List Search Calendars FAQ Ticket List Log Out


RE: excel to array, perform functions to convert units of measure then save xls

  		Logged in as: Guest
arrSession:exec spGetSession 2,2,61824
Active Users: There are 0 members and 0 guests.
Users viewing this topic: none
 

 

  
 
  Printable Version
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! 		Page: <<   < prev  1 2 3 [4]
Login
Message << Older Topic   Newer Topic >>
RE: excel to array, perform functions to convert units ... - 7/1/2008 4:03:10 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		yes, theses consts make it possible to refer to the cells/cols without any (errorprone) translation
from names to numbers in the programmer's brain. Use the clipboard (from the consts list) if you
need one of them in an expression. Retyping them from scratch is bound to fail (because of the
spaces/cases). Remember I used a script to generate the list (including the []) by getting them
directly/immediately from Excel.

(in reply to chriswebb18)
 
 
Post #: 61
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 4:15:04 AM   
  chriswebb18

 

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline 		Thank you so much for all your help the script is cleaner, more efficient, and most important, it works!.  here is my end code(all i have to do now is make sure that the calculations are all correct.)  my totals are matching, so hopefully.  Thank you so much!


      

-minor changes to code made to have items with no uom in new database be assigned the same uom and a 1 for measure  so to keep net pkg calc. correct.  (net pg calc also updated)

< Message edited by chriswebb18 -- 7/1/2008 4:26:47 AM >

(in reply to chriswebb18)
 
 
Post #: 62
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 4:35:15 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		line 61

       objSheet.Cells(iRow, [CS UOM ] ).Value = "EA"

line 129

         objSheet.Cells(iRow, [CS UOM ] ).Value = "GA"

line 176

         objSheet.Cells(iRow, [CS UOM ] ).Value = "LB"

line 238

       objSheet.Cells(iRow, [CS UOM ] ).Value = "TK"

===> 

subroutine approach: you would have to correct it just once

run time error

line 184

      objSheet.Cells(iRow, [CS Package SOH Qty]).Value = objSheet.Cells(iRow, [CS Amount(soh)]).Value / objSheet.Cells(iRow, [CS Measure]).Value

looks like objSheet.Cells(iRow, [CS Measure]).Value is 0. How can that be? Ad hoc remedy:

      If 0 = objSheet.Cells(iRow, [CS Measure]).Value Then
         WScript.Echo iRow, "LB-NULL", "objSheet.Cells(iRow, [CS Measure]).Value == 0"
      Else
         objSheet.Cells(iRow, [CS Package SOH Qty]).Value = objSheet.Cells(iRow, [CS Amount(soh)]).Value / objSheet.Cells(iRow, [CS Measure]).Value
      End If

Output:

202 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
203 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
204 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
205 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0

next rt error line 68:

      objSheet.Cells(iRow, [CS Package SOH Qty]).Value = objSheet.Cells(iRow, [CS Amount(soh)]).Value / objSheet.Cells(iRow, [CS Measure]).Value

looks similar; same remedy? If we had this kind of action in a sub, we would be done after the first
correction ....

(in reply to ehvbs)
 
 
Post #: 63
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 4:57:43 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Of course the error message code should be

         WScript.Echo iRow, sUpdType, "objSheet.Cells(iRow, [CS Measure]).Value == 0"

instead of

        WScript.Echo iRow, "LB-NULL", "objSheet.Cells(iRow, [CS Measure]).Value == 0"
        WScript.Echo iRow, "TK-NULL", "objSheet.Cells(iRow, [CS Measure]).Value == 0"
        ...

(in reply to ehvbs)
 
 
Post #: 64
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 4:58:32 AM   
  chriswebb18

 

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline 		if i were to dim x and make x = total + x on each row would it keep my running total to print on last line?  or would it have same problem with real numbers as earlier? 

(in reply to ehvbs)
 
 
Post #: 65
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 5:00:29 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		Just realized:

you didn't move

   iRow  = iRow  + 1
   nRecs = nRecs + 1

at the end of the loop - shame on you.

(in reply to ehvbs)
 
 
Post #: 66
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 5:04:44 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		ad running total: just try it (i will do it too). ad real numbers: i don't think the
missing results were caused by not being able to read real numbers from cells, but by
your conditionals skipping lines.

(in reply to ehvbs)
 
 
Post #: 67
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 5:19:50 AM   
  ehvbs

 

Posts: 2201
Score: 50
Joined: 6/22/2005
From: Germany
Status: offline 		totals:

I added

(1)  two cols to my sheet ()

(2) the consts

Const [Recno]                  = 31

Const [RunTotal]               = 32
Const [RunTotal1]              = 33

Dim oFS       : Set oFS      = CreateObject( "Scripting.FileSystemObject" )

(3) the init:

Dim nRecs  : nRecs  = 0

Dim dblRunTotal  : dblRunTotal  = 0.0
Dim dblRunTotal1 : dblRunTotal1 = 0.0

Do Until "" = objSheet.Cells( iRow, [Old-Prod-Code] )

(4) The calculations:

End Select

 dblRunTotal  = dblRunTotal  + objSheet.Cells( iRow, [Total]  )
dblRunTotal1 = dblRunTotal1 + objSheet.Cells( iRow, [Total1] )
objSheet.Cells( iRow, [RunTotal]  ) = dblRunTotal
objSheet.Cells( iRow, [RunTotal1] ) = dblRunTotal1

iRow  = iRow  + 1
nRecs = nRecs + 1

Loop

(5) the output:

WScript.Echo "Totals", dblRunTotal, dblRunTotal1
WScript.Echo "iRow", iRow, "nRecs", nRecs, "Secs", Fix( Timer() - tStart)

oWBook.Saveas oFS.GetAbsolutePathName( ".\out06.xls" )

and got:

... lots of "can't divide by zero' diagnostics
3055 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
3056 EA-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
3057 EA-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
3058 EA-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
3059 LB-NULL objSheet.Cells(iRow, [CS Measure]).Value == 0
Totals 10661984,3104146 10661984,3104146
iRow 3060 nRecs 3058 Secs 176

and two completely filled cols AF and AG in my output .xls

(in reply to ehvbs)
 
 
Post #: 68
 
RE: excel to array, perform functions to convert units ... - 7/1/2008 5:27:11 AM   
  chriswebb18

 

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline 		i went with the first before you had posted both seem to work fine.  I have completed the script and it is being reviewed right now.  thanks again here is the final final