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

Forums

My Profile

Inbox

Address Book

My Subscription

My Forums

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 next > >>

Message

<< Older Topic Newer Topic >>

RE: excel to array, perform functions to convert units ... - 6/30/2008 4:32:57 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

Yes, I would have probably used your structure with ado/sql as well, but with the time crunch we simply moved forward with what i had starting. I enjoy playing with this and learning in my free time, but did not have that luxury this time. They have the option when they export the data to excel whether to leave the fields blank or have them marked n/a. they reexported the file. I did see a lot of other tidbit i would have tried on other forums that would make my code much cleaner, but if something goes wrong when we go live, i want to be comfortable that i understand everything and can correct it on the fly.

EDIT Im also noticing that i may have made this more complex than needing by using the array. since i can simply perform the calculations based on the cells.value

< Message edited by chriswebb18 -- 6/30/2008 4:35:57 AM >

(in reply to ehvbs)

Post #: 21

RE: excel to array, perform functions to convert units ... - 6/30/2008 4:43:54 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

I was also having a problem with Do Until .endoffile does that not work with excel since technically, the file is infinite with null cells. i ended up just using the until intROW = 4000. my files generally stop from 3200 to 3600 but it may go farther and im not sure that ill remember to check the input. Do you knwo of the proper way to do this?

(in reply to chriswebb18)

Post #: 22

RE: excel to array, perform functions to convert units ... - 6/30/2008 4:59:22 AM

ehvbs

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

You could test a cell that must always be filled (if there is any in your data) - something like
your first version of the script.

OTOH, there is:

' magic from Giovanni Cenati
' http://www.codecomments.com/archive299-2005-2-401145.html

*** something rotten with this link *********

' oExcel.Range(oExcel.cells(1,1),oExcel.cells(100,1)).Select
oExcel.Range(   oExcel.cells( 1, 1 ) _
              , oExcel.cells( oWBook.Sheets( 1 ).UsedRange.SpecialCells( xlLastCell ).Row , 1 ) _
            ).Select
oExcel.Selection.TextToColumns oExcel.Range("A1"), xlFixedWidth

use the Excel Docs to get further info and test it carefully.

< Message edited by ehvbs -- 6/30/2008 5:02:49 AM >

(in reply to chriswebb18)

Post #: 23

RE: excel to array, perform functions to convert units ... - 6/30/2008 5:46:26 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

I ran into a problem that i can seem to figure out. I'm sorry to send you this sloppy code, its is the LB -> K . for some reason my shipment on hand (col 19) is not populating... but this only happens on half of the LB -> K and occassionally on some others, if we could figure out why it does it one place i could look to correct the others.

col 19 should be col 18(function performed)
i.e.
..cells(intRow, 19).value = ..cells(intRow, 18).value * 8.333 / intSG
or something similar. i am sending this in email so you could see the file and what im getting in excel.

-i sent this and my files in an email. Do you think you could look at it?

< Message edited by chriswebb18 -- 6/30/2008 5:50:15 AM >

(in reply to ehvbs)

Post #: 24

RE: excel to array, perform functions to convert units ... - 6/30/2008 5:53:22 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

i actually think its anything coming from pounds

nevermind its not just pounds. im having a hard time finding a consistency in the error and thus a hard time finding the error

< Message edited by chriswebb18 -- 6/30/2008 6:02:30 AM >

(in reply to chriswebb18)

Post #: 25

RE: excel to array, perform functions to convert units ... - 6/30/2008 6:14:31 AM

ehvbs

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

I'm looking at it right now, but I'll need some time to work tru it.

(in reply to chriswebb18)

Post #: 26

RE: excel to array, perform functions to convert units ... - 6/30/2008 6:25:54 AM

ehvbs

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

Could you identify the relevant colums in 'Excel' (colum letter)? Should colum S updated
with column R?
There still lots of "#N/A"s in the .xls you sent me. Why?

(in reply to ehvbs)

Post #: 27

RE: excel to array, perform functions to convert units ... - 6/30/2008 6:41:14 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

the fields that are still N/A were actually n/a in the database because they are items without descriptions. none of them are relevant. im not sure how they do the exporting as i have no access to the databse, simly what they are giving me in this spreadsheet. column s -(.cells(intRow, 19) or arrexceldata(18)) is the one that should be updated column s would be the result of the function performed on col R. i took col r and made it intSOH. then performed the function making it intSOHMeasure(I dont remember why i dimmed 2 ints for this, but originally there was some reason i needed them seperate.). then column R objSh....Cells(intRow, 19).Value = intSOHMeasure

sorry its a bit confusing with my redundancy giving multiple names, i am trying performing it directly now i.e.

objSheet.Cells(intRow, 19) = objSheet.Cells(intRow, 18) * 55.1150 / 25

(in reply to ehvbs)

Post #: 28

RE: excel to array, perform functions to convert units ... - 6/30/2008 6:54:20 AM

ehvbs

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

To have some kind of reference, I used this code:

Dim oFS     : Set oFS    = CreateObject( "Scripting.FileSystemObject" )
 Dim sIFSpec : sIFSpec    = oFS.GetAbsolutePathName( ".\cwtransexcel\in2.xls" )
 Dim sTName  : sTName     = "[2007-03.01 Inventory Count Impo$]"
 Dim oXCN    : Set oXCN   = CreateObject( "ADODB.Connection" )
 Dim sCS, sSQL, oRS

sCS = Join( Array( _
               "Provider=Microsoft.Jet.OLEDB.4.0" _
             , "Data Source=" & sIFSpec _
             , "Extended Properties=""Excel 8.0;HDR=Yes""" _
                  ), ";" )

oXCN.Open sCS

sSQL = Join( Array( _
              "SELECT COUNT(*) AS [FoundByAdoSql]" _
            , "FROM " & sTName _
                   ), " " )
 Set oRS = oXCN.Execute( sSQL )
 showRS oRS
 oRS.Close

sSQL = Join( Array( _
              "SELECT *" _
            , "FROM " & sTName _
            , "WHERE 1 = 2" _
                   ), " " )
 Set oRS = oXCN.Execute( sSQL )
 ReDim aFNames( oRS.Fields.Count - 1 )
 Dim nIdx : nIdx = 0
 Dim oFld
 For Each oFld In oRS.Fields
     aFNames( nIdx ) = "[" & oFld.Name & "]"
     nIdx = nIdx + 1
 Next
 oRS.Close
 For nIdx = 0 To UBound( aFNames )
     sSQL = "SELECT COUNT( " & aFNames( nIdx ) & " ) FROM " & sTName
     Set oRS = oXCN.Execute( sSQL )
     WScript.Echo Right( "   " & nIdx, 3 ), Chr( 65 + nIdx ), Right( "        " & oRS.Fields( 0 ).Value, 8 ), aFNames( nIdx )
     oRS.Close
 Next

oXCN.Close

to get this output:

=== checkCWCols: check CW columns =============================================
======================================================================
SELECT COUNT(*) AS [FoundByAdoSql] FROM [2007-03.01 Inventory Count Impo$]
FoundByAdoSql
----------------------------------------------------------------------
3488
======================================================================
 0 A        0 [Facility]
 1 B        0 [Tag-ID]
 2 C     3058 [old whse]
 3 D     2560 [Old-Prod-Code]
 4 E     2950 [Prod-Pkg-Code]
 5 F     2929 [CS PROD NAME]
 6 G     2003 [CS PROD DESC]
 7 H     3058 [Old Description]
 8 I     3056 [Old Des# #2]
 9 J     3058 [Lawson UOM]
10 K     3058 [Lot-Code]
11 L     2914 [Lawson Cost per UOM ]
12 M     2966 [Product Code]
13 N     2950 [CS Measure]
14 O     2950 [CS UOM ]
15 P        0 [Location]
16 Q        0 [Serial-Number]
17 R     3058 [lawson Amount(soh)]
18 S        0 [CS Amount(soh)]
19 T     2928 [Spacific Gravity]
20 U        0 [CS cost per uom]
21 V     3053 [lawson Net Package]
22 W     3052 [lawson Package SOH Qty]
23 X        0 [CS Package SOH Qty]
24 Y        0 [Package-Override]
25 Z        0 [Override-Pkgs]
26 [        0 [Remarks]
27 \        0 [PlaceHolder]
=== checkCWCols: 0 done (00:00:00) ============================================

26/27 should manually changed to

26 AA        0 [Remarks]
27 AB      0 [PlaceHolder]

So we are talking about

17 R     3058 [lawson Amount(soh)]
18 S       0 [CS Amount(soh)]

?

(in reply to chriswebb18)

Post #: 29

RE: excel to array, perform functions to convert units ... - 6/30/2008 6:59:18 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

yes, i was unsure how to say it earlier because when you read the excel sheet using objsheet.cells, the first column is labeled 1, when you put in array, first column is labeled 0. but yes you have the right columns

(in reply to ehvbs)

Post #: 30

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:02:13 AM

ehvbs

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

Adding this SQL

sSQL = Join( Array( _
              "SELECT COUNT( * )" _
            , "FROM " & sTName _
            , "WHERE " & aFNames( 17 ) & " IS NULL" _
                   ), " " )
Set oRS = oXCN.Execute( sSQL )
showRS oRS
oRS.Close

I got

======================================================================
SELECT COUNT( * ) FROM [2007-03.01 Inventory Count Impo$] WHERE [lawson Amount(soh)] IS NULL
Expr1000
----------------------------------------------------------------------
430
======================================================================

So there are 430 Rows with bad [lawson Amount(soh)]; computations based on these data must fail.

(in reply to ehvbs)

Post #: 31

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:09:16 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

hmmm. well why are lines 16 - 32 failing? i see no data that is invalid. i can handle it not working if there is data missing, but everything needed to perform the functions is there, but it is not working

looking back at the spreadsheet in excel, it is not showing any null lines in that column. why would that sql query find 430 null fields?

< Message edited by chriswebb18 -- 6/30/2008 7:10:46 AM >

(in reply to ehvbs)

Post #: 32

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:13:48 AM

ehvbs

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

row 16 - 32 contain real numbers, not integers as the ones before and after

(in reply to chriswebb18)

Post #: 33

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:18:06 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

i never would have cought that. thank you

(in reply to ehvbs)

Post #: 34

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:31:39 AM

ehvbs

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

As to NULL [lawson Amount(soh)] fields: There are about 3060 'real' data rows
in your .xls; but Excel (and Ado) think there about 3488.

This Excel code supports this:

Dim oFS       : Set oFS      = CreateObject( "Scripting.FileSystemObject" )
Dim objExcel : Set objExcel = CreateObject( "Excel.Application" )
Dim strInFile : strInFile    = oFS.GetAbsolutePathName( ".\cwtransexcel\in2.xls" )
Dim oWBook    : Set oWBook   = objExcel.WorkBooks.Open( strInFile )
Dim objSheet : Set objSheet = objExcel.ActiveWorkbook.Worksheets( 1 )

Dim iRow : iRow = 2
Do Until "" = objSheet.Cells( iRow, 18 )
    iRow = iRow + 1
Loop
WScript.Echo "iRow", iRow

oWBook.Close
objExcel.Quit

(in reply to ehvbs)

Post #: 35

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:32:16 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

so i just run this against it and it should fix that problem then right?

(in reply to chriswebb18)

Post #: 36

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:34:29 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

that was in reference to the integer/real number problem... why would i want to echo the row numbers? i think im missing something here

and apparently just changing them to sng doesnt help.. ill have to dim as singles, i guess

< Message edited by chriswebb18 -- 6/30/2008 7:36:58 AM >

(in reply to chriswebb18)

Post #: 37

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:41:46 AM

chriswebb18

Posts: 71
Score: 0
Joined: 4/7/2008
Status: offline

ok probably a dumb question, but why does
Dim sngCost As Single

give me an error expected end of statement?

(in reply to chriswebb18)

Post #: 38

RE: excel to array, perform functions to convert units ... - 6/30/2008 7:49:18 AM

ehvbs

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

you can't specify types with AS in VBScript (that's VBA lingo).

I run this work code (in the context of my last Excel.Application sample
code successfully:

Dim iRow : iRow = 2
Dim dblNewValue
Do Until "" = objSheet.Cells( iRow, 18 )
   dblNewValue = objSheet.Cells( iRow, 18 ) * 55.1150 / 25
   objSheet.Cells( iRow, 19 ) = dblNewValue
   iRow = iRow + 1
Loop
WScript.Echo "iRow", iRow

lawson Amount(soh)    CS Amount(soh)
500    1102,3
27    59,5242
108    238,0968
108    238,0968
168    370,3728
91    200,6186
650    1432,99
50    110,23
352    776,0192
13    28,6598
60    132,276
16    35,2736
4    8,8184
10    22,046
1818,795    4009,715457
2204,6    4860,26116
2094,37    4617,248102
2204,6    4860,26116
55,115    121,506529
2204,6    4860,26116
2204,6    4860,26116
2204,6    4860,26116
2204,6    4860,26116
2204,6    4860,26116
2204,6    4860,26116
2204,6    4860,26116
1818,795    4009,715457
2204,6    4860,26116
496,035    1093,558761
2204,6    4860,26116
2204,6    4860,26116
55,115    121,506529
100    220,46
11025    24305,715
75    165,345

(sorry about the format)

If this looks correct and similiar code fails for you, check whether there is something
fishy with the represantation of reals (decimal point or comma, is Excel using thousand
separators)

< Message edited by ehvbs -- 6/30/2008 7:54:00 AM >

(in reply to chriswebb18)

Post #: 39

RE: excel to array, perform functions to convert units ... - 6/30/2008 8:18:21 AM

ehvbs

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

I just thought of another type of problem: you use

If cond1 Then
ElseIf cond2 Then
...
ElseIf cond110 Then
...
ElseIf arrExcelData(9) = "KG" And arrExcelData(14) = "LB" Then
computing intSOHMeasure = intSOH * 25 / 55.1150
ElseIf cond112 Then
...

could it be that for some rows one of cond1, cond2, ..., or cond110 is true so
that your update isn't done for these rows?

< Message edited by ehvbs -- 6/30/2008 8:20:32 AM >

(in reply to ehvbs)