Reading Excel file using VB

  • Thread starter Thread starter Phoebe
  • Start date Start date
P

Phoebe

Hi, Good Day!

I've a coding which Open Excel File and save it into a recordset. But the
below coding "died" when reached the SQL statement.

Can someone help?
Thanks in advanced.

rgds
Phoebe.

Function GetRecordSetFromExcel(ByVal strFile, ByVal strWkShtName)

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strDir As String
Dim strConnectionString As String
Dim strSQL As String
Dim strError As String

oConn = CreateObject("ADODB.Connection")
oRs = CreateObject("ADODB.Recordset")

strDir = Mid$(strFile, 1, InStrRev(strFile, "\") - 1)
strConnectionString = "Driver={Microsoft Excel Driver (*.xls)};
DriverId=790; Dbq=" & strFile & "; DefaultDir=" & strDir & ";"


With oConn
.ConnectionString = strConnectionString
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.ConnectionTimeout = 60
.CommandTimeout = 120
.Open()

If .State <> ADODB.ObjectStateEnum.adStateOpen Then
strError = "Unable to connect"
End If
End With

strSQL = "SELECT * FROM """ & strWkShtName & """ WHERE F1 <> NULL"

With oRs
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
.Open(strSQL, oConn, , ADODB.LockTypeEnum.adLockOptimistic,
ADODB.CommandTypeEnum.adCmdText)

If .State <> ADODB.ObjectStateEnum.adStateOpen Then
strError = "Unable to retrieve data"
End If

If .EOF Then
strError = "No records retrieved"
Else
.MoveFirst()
End If
End With

GetRecordSetFromExcel = oRs

End Function
 
Hi Phoebe,

Did does not look as VB.net or ADO.net

If I do not make a mistake with that, you can maybe better ask this
question in one of the classic VB newsgroups.

Microsoft.public.vb* there are a lot.

I hope this brings you on the route,

Cor
 
¤ Hi, Good Day!
¤
¤ I've a coding which Open Excel File and save it into a recordset. But the
¤ below coding "died" when reached the SQL statement.

Please identify the error description and the line on which it occurred.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top