[RESOLVED] VB6 and reading from Excel

[tinny]

g'day,

any chance anyone's come across this problem before....

one moment my subroutine reads fine from my Excel file, the next it returns a null value as if the cell was empty.

eg in the following section of code, variables temp1, temp2 and temp3 are all defined the same (singles), and all cells in excel are formatted the same ie number format, 2 digits.

the first few runs, all variables were assigned the correct values (ie VB6 read the correct data from the excel file). then later, temp2 and temp3 were read as null or empty, and (obviously) my calcs don't work anymore!

the variable types for temp2 and 3 have also changed to 'AdVarWchar = 202'

ElseIf j = 5 Then
temp1(i, c, id) = inputTable.Fields(j).Value
ElseIf j = 6 Then
temp2(i, c, id) = inputTable.Fields(j).Value
ElseIf j = 7 Then
temp3(i, c, id) = inputTable.Fields(j).Value
ElseIf j = 8 Then
etc etc

If anyone has any suggestions, they'd be much appreciated...
I'm currently using an ADODB connection ("Provider=Microsoft.Jet.OLEDB.4.0;") to link to the excel workbook, but maybe there's a better way?

cheers!

[poison6feet]

Here U need to check for number of rows and columns with data - then U have to move this routine with no of columns and row with for ... next statment - so that U can avoide empty cells ....

Better use object library....... to open excel sheet... for importing data.....

[Static]

you dont need an ADODB connection to excel
try just using a direct link:

Reference MS Excel x.0 Object Library

Dim xls as New Excel.Application
xls.workbooks.open "C:\Path\FileName.xls"
xls.Sheets(1).activate
temp(i,c,id) = xls.range("A5").value

or
tmp(i,c,id) = xls.cells(1,5).value

etc..

xls.quit
set xls = nothing

[tinny]

cheers for your comments/suggestions - i think i solved my problem whilst still keeping the connection setup. the problem was to do with VB6's (provider's) assignment of major/minor variable types depending on the first 8 rows of data.