Reading cells in macros

[sineados]

I have an Excel Comma seperated values file (A) and a macros file (B). On my machine B reads A perfectly but on another machine B does not read 2 columns which are formatted custom H:mm:ss.

Does anyone know why this would happen, or what I can do to fix it.This is a copy of the code I am using.

Set m_conXLS = CreateObject("ADODB.Connection")
Set m_rsXLS = CreateObject("ADODB.RecordSet")
Set rs = CreateObject("ADODB.RecordSet")
Set rs1 = CreateObject("ADODB.RecordSet")

sConnection = "DRIVER={Microsoft Excel Driver (*.xls)};DriverId=790;ReadOnly=True;DBQ=" & wbIT.Name & ";"
m_conXLS.Open sConnection
m_rsXLS.Open "SELECT * FROM [" & wbIT.Sheets(2).Name & "$]", m_conXLS

While Not m_rsXLS.EOF
If IsDate(m_rsXLS.fields(0)) Then
sSQL = "MCK_iqPhoneList '" & m_rsXLS.fields(0) & "','" & m_rsXLS.fields(1) & "','" & m_rsXLS.fields(2) & "'"
objConn.Execute sSQL
End If
m_rsXLS.movenext
Wend

m_rsXLS.fields(1) and m_rsXLS.fields(2) are fields that contain the time.for example below is what I am reading.

Jan 1, 2007 10:03:14 PM 10:03:36 PM


When I print the sSQL statement on my machine its shown as
'Jan 1, 2007','10:03:14 PM','10:03:36 PM'

but on the other machine is 'Jan 1, 2007','',''

thanks,

 

[PHV]

Same regional setting on both machines ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

In addition to checking regional settings, try converting each field to a Date/Time Value using thenCDate function...

sSQL = "MCK_iqPhoneList '" & _
CDate(m_rsXLS.fields(0)) & "','" & _
CDate(m_rsXLS.fields(1)) & "','" & _
CDate(m_rsXLS.fields(2)) & "'"

Skip,
[sub]
[red][/red]
[/sub]

 

[sineados]

The regional settings are the same. The column is being read in as null so I cant convert it. I converted the column to a Time format and that didnt make any difference.

thanks,

 

[SkipVought]

Are there NULL values in other cells in the TIME columns, particularly the first 8 or 10 so rows?

Skip,
[sub]
[red][/red]
[/sub]

 

[sineados]

Hi

My apologies for not replying.I was on holidays. Yes there are nulls for the first couple of rows in these columns, but the column is formatted as time.

thanks,

 

[xlbo]

Doesn't matter what it is formatted as. Many ODBC drivers look at the 1st x number of rows to determine the data type of the column. If the 1st few rows are null or empty, you may have a problem setting the data type in the query...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

BINGO!

Skip,
[sub]
[red][/red]
[/sub]