Importing from excel into VB6 1

[judgeh59]

I am currently trying to import 3 columns from an excel file. I used a connection setup that Skip had showed somebody.

Dim sConn As String, sSQL As String
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim sPath As String, sDB As String

sPath = "\\namp-dsk-002\Eng\Common Area\Ernest H\"
sDB = "BH RI defects 04_11_12.xlsx"

Set cnn = New ADODB.Connection

sConn = "Provider=MSDASQL.1;"
sConn = sConn & "Persist Security Info=False;"
sConn = sConn & "Extended Properties=""DSN=Excel Files;"
sConn = sConn & "DBQ=" & sPath & sDB & ";"
sConn = sConn & "DefaultDir=" & sPath & ";"
sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""

cnn.Open sConn

Set rst = New ADODB.Recordset

sSQL = "SELECT * FROM [QN Raw Data]"

With rst
.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText ''
.MoveFirst
Do Until (.EOF)

this seems to error out on the .open after the With rst line. the error is a run-time error basically saying it can't find "QN Raw Data". QN Raw data is a worksheet in the excel file with multiple columns in the WS. The excel file itself has multiple WS in it also. I have copy and pasted the sPath and sDB strings into explorer and made sure the file opens correctly, it does. I have looked in the locals windows as things run and everything "looks" okay.

Any Idea? thank in advance...

Ernest

Be Alert, America needs more lerts

 

[gmmastros]

try this....

    sSQL = "SELECT * FROM [QN Raw Data[!]$[/!]]"

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[judgeh59]

I noticed that in the example and thought somebody had named their WS that way....it worked....thanks for the help and quick response....

Ernest

Be Alert, America needs more lerts