473,323 Members | 1,560 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Reading Excel file using VB

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
Nov 20 '05 #1
2 2161
Cor
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.

Can someone help?
Thanks in advanced. 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

Nov 20 '05 #2
Cor
Sorry

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

This does not look as VB.net or Ado.net
Nov 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: David Alliet | last post by:
Hello, I'm having a bit of a problem with ASP and Excel. A client has developed his own program, which calculates alot of financial stuff, in excel. I'm doing his websites and he has requested...
2
by: Roland Hall | last post by:
I have two(2) issues. I'm experiencing a little difficulty and having to resort to a work around. I already found one bug, although stated the bug was only in ODBC, which I'm not using. It...
6
by: Paul | last post by:
I was wondering if anyone has had an issue where using vba code to read an excel file and import the data into an access table some records are not imported from the excel file. It seems looking at...
1
by: mail2atulmehta | last post by:
Hi, I do not know if this is right place for this, but i need some help. I have a text file, whose values are seprated by a delimiter. I want to open this file in excel, ( not import it) . I have...
1
by: Turner, GS \(Geoff\) | last post by:
> -----Original Message----- > From: siliconwafer > Posted At: 19 August 2005 15:20 > Posted To: c > Conversation: reading an excel file in C? > Subject: reading an excel file in C? > > >...
4
by: Phoebe. | last post by:
Hi, Good Day! Reading 1 excel file into a dataset is fine. How can I read multiple excel with the same data structure into 1 dataset? How can I append those data? Can someone help? Thanks in...
5
by: Scott M. Lyon | last post by:
I've just discovered a bug in some code I wrote a little while ago, and I need you guys' help to fix it. My program imports data from a standard Excel Spreadsheet (just with specific column...
9
by: dba123 | last post by:
I need some help and direction on what classes and an example or two (article) on how to read an Excel Worksheet and insert one column into a database table column. I am using .NET 2.0 only. What...
1
by: sachinkale123 | last post by:
Hi, I am reading excel file and reading values from that I am using provider As : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + Filename + ";Extended Properties=\"Excel 8.0;Hdr=No;IMEX=1\"";...
1
by: ndedhia1 | last post by:
I was reading in a log file like this that had no milliseconds: QuoteBlockTiming exceeded 1 ms: 1 --- Thu Dec 10 02:01:40 CST 2009 170.137.15.155 Class key = 601650761 block size QuoteBlockTiming...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.