Hello,,

Iam just trying to read a sheet from excel and transfer that excel
information into my Access database.

When i try to import the excel data Iam getting Object variable or with
block variable not set...

Iam using VB6.0 and Ms-Access .

The folloiw is my code

Dim rs2 As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Private Sub cmdBrowse_Click()
cmndFilePath.CancelError = True
cmndFilePath.Filter = "Databases|*.XLS"
On Error Resume Next
cmndFilePath.ShowOpen
Data1.DatabaseName = cmndFilePath.FileName
txtFilePath.Text = cmndFilePath.FileName
Data1.Refresh
End Sub
Private Sub cmdImport_Click()
If Data1.RecordSource "Sheet1$" Then
MsgBox "Invalid Sheet"
Exit Sub
Else
Set rs2 = Nothing
rs2.Open "Select * from TrackingDetails ", con, adOpenKeyset, 2

While Not Data1.Recordset.EOF
rs2.AddNew
rs2(0) = Data1.Recordset(1)
rs2(1) = Data1.Recordset(2)
rs2.Update
Data1.Recordset.MoveNext
Wend
End If
MsgBox "Transferred completely"

Call Display
End Sub
Public Sub Display()
'Set rs1 = New Recordset
rs1.Open "select * from TrackingDetails", con, adOpenStatic,
adLockOptimistic
Set DataGrid1.DataSource = rs1
End Sub

Private Sub Form_Load()
Call GetConn
End Sub


The below code isin Differnt module

Public con As ADODB.Connection
'Public con As New ADODB.Connection
Public Sub GetConn()
Set con = New ADODB.Connection
Dim dbname As String
dbname = App.Path & "\TrackingStatus.mdb"
con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
dbname & ";Persist Security Info=False"
con.Open
End Sub



Can anyone help me in solving the above said problem???

Thanks and Regards
Nick,

Archived from group: microsoft>public>vb>enterprise