All Forums >> [Scripting] >> WSH & Client Side VBScript >> 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!
This is a massive pain for me. I was told yesterday morning that I had until this morning to get this done, obviously not gonna happen. We have data that we're moving from one DB to a new one in a different format. I don't know all the details, but I have the information in an xls file from the old DB. I am taking it row by row and putting into an array, then converting the rows that contain the measurement info from ton to lb or gallons to litres, there are about 20 different conversions that will be in here... I think I have put 2 in, simply because I didn't want to write it all and have to go back through it all if I screwed it up. I am getting a "Type Mismatch" error on line 56 char 2(this .line) If arrExcelData(9) = "EA" And arrExcelData(14) = "E" Then not sure what would make an If get a type mismatch error, but heres the whole code and a section of my in file. I am fairly new to VBS, but lately have been working on several different projects, this is my first working with excel and i am very sloppy/inexperienced with arrays. If you know an easier way or at least can help me get rid of this err, i would really appreciate it. Thank you.
OK, so i cannot upload an excel, so I am not sure how to ge my infile to you. Tell me what you need to help. Thanks again
i dont know that i completely understand that code... or partially, really. i am not using sql in any part of this either, so i dont know if that works with those db connectors and the insert from commands. it is excel to excel. originally, it was from sql, but i dont have access to the original sql db, they exported what they needed done to xls and gave it to me.
ive got a stand alone version ill send you, also, line 202-205 have "#N/A" in the cells that are being tested with the if conditionals. I believe this is the problem, but im not sure why it would be a problem. the array values should be strings which would accept all of those characters, and i shouldn't need an if to deal with them since the else(or elseif) is optional and they would be treated as a do nothing. that said... i am trying adding an else at the end just because and try using CString(arrExcelData(9)) and CString(arrExcelData(14)). ill let you knwo how that works.
no good... ill keep working on it
It has to be the#n/a, i tried a conditional based off of that string in the array and it got type mismatch error, then tried adding this to the beginning, before it splits to an array and it give a type mismatch error as well:
I think im just going to have to add a piece to the beginning of the script and makes those fields = something else, but how would i detect those if i get error when it reads them?
< Message edited by chriswebb18 -- 6/25/2008 6:20:45 AM >
You can use the scripting fso for excel? never would have thought to use that, does it have something to do with the combined use with the jet db connector?
thanks, ill try working with this and see how it goes.
ok, so with the way that this works, how do i know which cell it is reading? I'm assuming nCnt is row, but what identifiers do i use to mark like this below. or rather what are the array identifiers, arrexceldata(9) is the 10 column in the row, but im not sure would it be sCS(9) then? and once i'm done, how do i do the objSheet.Cells().Value = x, is it still reading as if excel. i know it was sloppy the way i had it, im just very inexperienced with arrays and this sort of thing.
If objSheet.Cells(intRow, 15).Value <> "#N/A" Then
That's wrong, "#N/A" results in ERROR. If you change the guard to
Do Until arrExcelData(5) = "" If vbError = VarType( objSheet.Cells(intRow, 15).Value ) Then ... what shall we do here? Else arrExcelData(0) = objSheet.Cells(intRow, 1).Value
your code will fail for
If arrExcelData(9) = "E" And arrExcelData(14) = "EA" Then
in row 2048 because arrExcelData(5) is ERROR. I don't think that
(a) using Excel.Application (instead of ADO/SQL)
(b) using indices instead of field names
(c) copy of cells into array elements and vice versa
You use the field/column names, like oRS.Fields( "Lawson UOM" ).Value. Which column did you want to access with "arrExcelData(9)"? If "Lawson UOM", why are there no values "E" in that column? Some for "arrExcelData(14)".
i have made a lot of progress with my original script and don't think i will need any help with it soon, but will work with the structure you gave me to learn that. thanks for all your help. im posting my script so far and will post the final when it's done.
I'm happy to hear of your progress; and you are welcome - I rather liked your problem and working with you.
As food for thought after you have finished your project, I include a further demo script. I hope it will show you that using ADO/SQL (treating Excel as a database) may be daunting at first, but will pay off in the long run.
The script assumes a sheet like:
Car Speed SpeedUnit --------------------------- audi 160 kmh jaguar 100 mph
to be transformed into
Car Speed SpeedUnit NewCol -------------------------------------- audi 160 kmh 4 jaguar 160 kmh 6
so we have a change/update and a new column. The code
if you read a cell value containing "#N/A" using oSheet.Cells( iRow, iCol ) the return value is a Variant of subtype vbError. You can't convert such a beast to a string or compare it to string (which entails such a conversion).
Sample:
So if you have garbage input data, you have to check carefully whether the data supports the actions/operations you want to apply.
Printable Version
excel to array, perform functions to convert units of m... - 
