could not find the object sheet1$ - Excel to SQL using VB6 1

[barbola]

I have an excel sheet and woudl like to import it into a SQL table using VB6. I am getting the error "The Microsoft Jet Database Engine could not find the object 'Sheet1$'"

Option Explicit

Global Const g_strConnDB = "Provider=sqloledb;Data Source=sql1;Initial Catalog=_Hytek; User Id=sa;Password=;"
Global Const g_strConnXL = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                           "Data Source=C:\DATA_test.xls;" & _
                           "Extended Properties=Excel 8.0;"
    
Dim g_oCnnDB As ADODB.Connection 'SQL
Dim g_oCnnXL As ADODB.Connection 'Excel
'--------------------------------------------
Public Function ImportDataTest()
    
    Dim oRsetXL As ADODB.Recordset
    Dim oRsetDB As ADODB.Recordset
    Dim oCmd As ADODB.Command
    
    Dim i As Integer
    
    On Error GoTo ErrHandler
    
    'Establish connection to SQL database
    Set g_oCnnDB = New ADODB.Connection
    g_oCnnDB.Open g_strConnDB
    Set oRsetDB = New ADODB.Recordset
    oRsetDB.Open "DataDetailTable", g_oCnnDB, adOpenDynamic, adLockOptimistic
    
    'Establish connection to Excel worksheet
    Set g_oCnnXL = New ADODB.Connection
    g_oCnnXL.Open g_strConnXL
    Set oRsetXL = New ADODB.Recordset
    
    Set oCmd = New ADODB.Command
    oCmd.ActiveConnection = g_oCnnXL
    oCmd.CommandText = "SELECT * FROM [Sheet1$]"
    
    oRsetXL.Open oCmd, , adOpenStatic, adLockOptimistic

    oRsetXL.MoveFirst
    
    Do Until oRsetXL.EOF
        MsgBox oRsetXL.Fields(0)
        oRsetXL.MoveNext
    Loop
   
    
    Debug.Print oRsetXL.RecordCount

    MsgBox "recordcount = " & i
    Set oRsetXL = Nothing
    Set oRsetDB = Nothing
    Exit Function
ErrHandler:
    MsgBox Err.Number & " -> " & Err.Description
End Function

The worksheet IS called Sheet1 in my file, so there is no misspelled information.

Thanks.

 

[gmmastros]

There are several ways to import data in to SQL Server. From what it looks like, you are about to do it the slowest way possible. (but don't feel bad, most people do it the same way you are).

SQL Server has an 'OpenDataSource' command that 'understands' excel files.

So, you could do something like...

SELECT *
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="[!]c:\tektips.xls[/!]";User ID=Admin;Password=;Extended properties=Excel 8.0;')...Sheet1$

If you run this in QA, you should see all your data.

If you want to insert the data in to a table, then...

Insert
Into   Table(Field1, Field2, Field3)
Select Column1, Column2, Column3
From   OpenDataSource('Microsoft.Jet.OLEDB.4.0',
  'Data Source="[!]c:\tektips.xls[/!]";User ID=Admin;Password=;Extended properties=Excel 8.0;')...Sheet1$

Of course, the filename, table, and fields will have to be altered.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Glasgow]

Or consider a different connection string:

  DbConnect = "Provider=MSDASQL.1;Persist Security " & _
                         "Info=False;Extended Properties=" & Chr(34) & _
                         "DSN=Excel Files;DBQ=" & "C:\DATA_test.xls" & ";" & _
                         "DefaultDir=c:\;DriverId=790;" & _
                         "MaxBufferSize=2048;PageTimeout=5;" & Chr(34)

Or if this is a one-off requirement you could use DTS (Data Transformation Services) that ships with SQL Server.

 

[barbola]

The import is something that a user will be running at least once a week, so I can't use query analyzer, BUT maybe I could write a stored procedure in SQL and call it in VB.

Actually, I am writing this import in VB, but the user interface is in Access which I am slowly changing over and this is one of the first. She will click a menu item on the Access form that will open the VB form.

The access database is linked to the SQL tables, and that is really slow. I'm still learning VB so eventually I will change things around.

Thanks for the tips. I will give them a try.

 

[gmmastros]

You don't need to do this in Query Analyzer. I was suggesting that you first test this in QA.

In the production code, you could create a connection to the SQL Database and then run it through ADO.

Dim sSQL As String

sSQL = "Insert Into Table(Field1, Field2, Field3) Select Column1, Column2, Column3 From OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=""" & FileName & """;User ID=Admin;Password=;Extended properties=Excel 8.0;')...Sheet1$"

Call dbConnection.Execute(sSQL)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[barbola]

Ok thanks. I am thinking out loud now, trying to comprehend this because I get confused with the connections and what is open and what is not, and oCmd and oConn etc etc.

Global Const g_strConnDB = "Provider=sqloledb;Data Source=sql1;Initial Catalog=ProdDatabase; User Id=sa;Password=;"

Dim g_oCnnDB As ADODB.Connection 'SQL
---------------------------------------
Public Function DataImport()

    Dim oRsetDB As ADODB.Recordset
    Dim oCmd As ADODB.Command 'do I need this?

    'Establish connection to SQL database
    Set g_oCnnDB = New ADODB.Connection
'Do I need this below?
'    g_oCnnDB.Open g_strConnDB
'    Set oRsetDB = New ADODB.Recordset
'    oRsetDB.Open "FinDetail_Test", g_oCnnDB, adOpenDynamic, adLockOptimistic

    sSQL = "Insert Into DataTable(Trim1, Weight1, Class,CValue,Premium) Select Trim, NetWeight, Class,CValue,LPrem From OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=\\server1\D drive\production\Data_test.xls; User ID=;Password=;Extended properties=Excel 8.0;')...Sheet1$"

'    Call dbConnection.Execute(sSQL)
     Call g_oCnnDB.Execute(sSQL)  'is this correct???

Set oRsetDB = Nothing
Exit Function

I'm not sure about the .execute part.

thanks.

 

[barbola]

Sorry I used two different names for my table.

FinDetail_Test is the table but I changed it to DataTable in the sSQL for this sample post. They should read the same tablename.

barb.

 

[gmmastros]

If I'm not mistaken, this should do it.

Global Const g_strConnDB = "Provider=sqloledb;Data Source=sql1;Initial Catalog=ProdDatabase; User Id=sa;Password=;"

Dim g_oCnnDB As ADODB.Connection 'SQL
---------------------------------------
Public Function DataImport()

    'Establish connection to SQL database
    Set g_oCnnDB = New ADODB.Connection
    g_oCnnDB.Open g_strConnDB

    sSQL = "Insert Into DataTable(Trim1, Weight1, Class,CValue,Premium) Select Trim, NetWeight, Class,CValue,LPrem From OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=\\server1\D drive\production\Data_test.xls; User ID=;Password=;Extended properties=Excel 8.0;')...Sheet1$"

     Call g_oCnnDB.Execute(sSQL)  'is this correct???

Exit Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[barbola]

YESSSS! It works!

Now I have to put in a pile of calculations on the Excel data and import into some fields in SQL, but that will be a piece of cake (ha).

thank you!!!!

B.

 

[gmmastros]

I'm glad.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[barbola]

...and the first calculation is.....lol

I have a textbox to display the number of records in the excel sheet.

I moved the open data source connection to a global variable and that works fine.

So I have:

Global Const g_strConnXL = "OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=\\server1\D drive\production\Data_test.xls; User ID=;Password=; Extended properties=Excel 8.0;')...Sheet1$"

I created a function but getting a Type Mismatch error and being new at this, I'm not sure how to find it other than try different types:

Dim g_lrecords as long

Public Function CountRecords() As Long
    g_lRecords = "select count(clng(HRecords)) from " & g_strConnXL

End Function

The first column in the spreadsheet is called HRecords and contains numbers from 1 to 185.

My form just has text1.text = Clng(CountRecords)

and I've tried integer, etc but it can't figure this easy one out.

 

[gmmastros]

If you want to return info, then you need to use a recordset.

Public Function CountRecords() As Long

  Dim RS As ADODB.Recordset
  Dim DB as ADODB.Connection

  Set DB = CreateObject("ADODB.Connection")
  Call DB.Open(g_strConnDB)

  Set RS = CreateObject("ADODB.Recordset")
  Call RS.Open("select count(HRecords) As RecordCount from " & g_strConnXL, DB)
  CountRecords = RS("RecordCount")
  RS.Close
  Set RS = Nothing
  DB.Close
  Set DB = Nothing
End Function

This should work (but I didn't test it). However, if you plan on opening this excel document repeatedly to get information from it, I think you are going about this the wrong way because it will probably be slow.

Instead, I suggest you import the data in to a SQL Server table, and then work with it there.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[barbola]

Sounds like a plan.

I originally used a linked table in access (linked to Excel) and tried using a query to calculate values for other fields, but ran into errors and decided to use VB.

I suppose I could import the Excel data into a temp table and write another function to do the calculations on these records before importing into da big SQL table.

thanks again, I will plug and play away and learn as I go!

barb

 

[barbola]

Actually, I lied. Each import is a one-time grab of the Excel information.

I will create the recordset as you suggested, and make all the calculations and import in one big step. Some of the imported fields will be from Excel and some will be based on user input in the form.

thanks again.