Reading excel value in VB 6.0 giving error of Invalid use of Null [SOLVED]

[Kuldeep singh]

Hi,


Please help me i am getting error on using below Vb code from excel:

[pre]

Dim recPayment As New Recordset '************ Fields declar for read excel file
'************* Connection to the excel file  *************

connString = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ=" & inputPath
recPayment.Open "select * from [Sheet1$]", connString

F1 = App.Path & XMLPath

Close #1
Open F1 For Output As #1
VoucherType = "Payment"

recPayment.MoveFirst

Print #1, StrEnvelop
Do While Not recPayment.EOF

VoucherNumberPay = recPayment.Fields(1).Value

recPayment.MoveNext
Loop
Print #1, StrEnd
Close #1

[/pre]

And Below is the sample excel data which i trying to get:

UTRNO/CC No

--------------

VYSAH13133605078

VYSAH13133605079

VYSAH13133605080

262820

262821


First three rows value getting in VB but on fourth row vb showing me error of "Invalid use of Null". However, i want the value from excel cell into a variable, so that i can use it further.


Thanks in adavance.

 

[Hui]

Kuldeep


Firstly, Welcome to the Chandoo.org Forums


I suspect that this is data related and not code related.

Is your forth record consistent with the other records?


Which line is returning the error message ?

 

[Kuldeep singh]

Hi again,


Below is the excel sample data which i am using:


UTRNO/CC No

--------------

VYSAH13133605078

VYSAH13133605079

VYSAH13133605080

262820

262821


And i am getting error on cell value containing "262820" in vb as "Invalid use of Null" on VoucherNumberPay = recPayment.Fields(1).Value .


Thanks for reply.

 

[Colin Legg]

It's because you have mixed data types in that column. The first 3 are strings but the last 2 are numbers.


On my computer, which has XL 2007, I used:

[pre]

connString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};IMEX=1;" & "DBQ=" & inputPath & ";"
And I already had the following keys set in my registry:

[pre][code]Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/[/pre]
[code]TypeGuessRows         0
ImportMixedTypes      Text

[/pre]
Note that these two registry settings cannot be overridden in the connection string.

With the above configured, I correctly pulled in your sample data from an xls file.


By the way, you can check to see if a value is Null[/code] by using the IsNull()[/code] function.

 

[Kuldeep singh]

Thats connstring work for me on XL 2007.


Thank you much guys.