[RESOLVED] Reading Excel sheet in VB, need to read the leading zeros

Hi, I am quite newbie to excel and vb, but trying to make something with them, I need help now though.
My excel contains a Cell value of say "0342345534", and I want to read it like this :

Dim f As String
f = objDepReqSheet.Cells(1,2).Value()

the thing is that f keeps getting the value "342345534" rather than "0342345534".
I tried also :

f = objDepReqSheet.Cells(1,2).Value().ToString() ' This does the same

and:
f = objDepReqSheet.Cells(1,2).ToString() ' This yields System._comObject

How can I make string f get the correct value ?
thank a bunch in advance.

btw, forgot to tell about some initializations:

Dim objDepReqSheet As Object

Dim ObjInputRefWorkBook As Object
Dim ObjInputRefSheet As Object
Dim objRefSheet As Object

ObjInputXLS = CreateObject("Excel.Application")

ObjInputDepReqWorkBook = ObjInputXLS.Workbooks.Open(DepReq.Text)
ObjInputDepReqSheet = ObjInputDepReqWorkBook.Worksheets(1)
objDepReqSheet = ObjInputDepReqWorkBook.Sheets(WorksheetNum)

That's the method I use to work with Excel...
Tx again ;)

check to make sure there is no Formatting on the cell (that adds the zero)

to do this: Select the cell.. look in the forumla bar.. does the value MATCH whats displayed in the cell?

Example:

put the number: 342345534 in a cell
now click Format -> Cells from the menus
select the Number tab... then Custom
enter 0######### in the textbox
then click ok.

now.. see? the CELL has the 0 but the actual value doesnt

u would need to use Format() to get the zero back

f = Format(objDepReqSheet.Cells(1,2).Value(),"0#########")

very interesting... !
but what if not all numbers begin with a zero, and their length is unknown ?

in the example I posted.. if the lenght of the cell is less than 10 numbers a zero will be added

anyway.. try this:


Dim f As String
f = objDepReqSheet.Cells(1,2).Text

worked in my test ;)

Is the formatting of the cell General/Custom/Text ?

formatting can be General, number, text, custom, date, etc.. there are tons..
BUT, as far as getting the contents

TEXT will get whats displayed
VALUE will get the actual Value
FORMULA will get the formula ;)

Seems to work !
Just how did you get to that .Text property ? Intellisense doesn't ring me a bell about .Text or .Format or anything like it... ?

well tha would make sense.. intellisense probably doesnt show u anything because of how u are declaring it

VB Code:

Dim ObjInputRefWorkBook As Object
Dim ObjInputRefSheet As Object
Dim objRefSheet As Object

if u add a reference to MS Excel x.0 Object Library and change the references to this

VB Code:

DIm ObjInputXLS as Excel.Application
Dim ObjInputRefWorkBook As Workbook
Dim ObjInputRefSheet As Worksheet
Dim objRefSheet As Worksheet

Set ObjInputXLS = New Excel.Application

that will give u all the intellisense...
now. 1 issue with this is that its not as compatible.
if u use this method (Early binding) then people with earlier versions of excel wont be able to use this.

so.. here is what I do.

Program this way.. get everything running. then switch it BACK to the object/create object(Late binding) method. (Dont forget to remove the reference)


Another way is to go into excel and program it then move your code over to VB
u can even record a macro to figure out how to do something, then copy the code over. once copied, just add the object reference back in and u are good to go.

Tx bro, this is very useful

no problem!

can u go to Thread Tools and select "Mark thread resolved"?

Thanks!!
oh yeah and http://www.vbforums.com/