VB6 SP4
WIN 2000
EXCEL 2000
ACCESS 2000
I keep getting an error on the line below. as indicated.
I HAVE TRIED CHANGING THE STRINGS TO VARIANTS BUT IT DOES NOT HELP....
I'm trying to import(map) data from Access to Excel via listbox field selections.
This field go to that column etc.............
' ACCESS TO EXCEL
' reopen access
Dim statement As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db_name As String
Dim Copycells As Variant
'db_name = Text2.Text
db_name = Text1.Text
' Open a connection.
Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OL
EDB.4.0;" & _
"Data Source=" & db_name & ";" & _
"Persist Security Info=False"
conn.Open
Set rs = New ADODB.Recordset
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
' ============== reopen excel Destiniation ==========================
===
'EFile = CommonDialog1.FileName
Text2.Text = EFile ' efile is a variable
Set oDest = CreateObject("Excel.Applic
ation")
oDest.Visible = True
oDest.workbooks.Open EFile
' ============== reopen excel ==========================
===
rs.Open (List1), conn, , , adCmdTable ' all records
Dim lastSourceRow As Long
Dim lastDestRow As Long
lastSourceRow = rs.RecordCount 'oSource.SHeets(List1.Text
).UsedRang
e.Rows.Cou
nt
lastDestRow = oDest.SHeets(List4.Text).U
sedRange.R
ows.Count
' ReDim Copycells(lastSourceRow - 1) As Variant
' oDest.Visible = True
' NEW CODE **************************
**********
**********
**********
**
'Dim CopyText As String
Dim CopyText As Variant
Dim str1 As String
Dim sourcedest As String
Dim x2 As Variant
'Dim strUnknown As String
'Dim strNext As String
' start Excel to Access import
Do While Not rs.EOF
x2 = lastDestRow + 1
lastDestRow = x2
oDest.ActiveCell.Offset(1,
0).Range("A1").Select
oDest.ActiveCell.EntireRow
.Insert
For x = 1 To lvwMap.ListItems.Count
str1 = lvwMap.ListItems.Item(x).L
istSubItem
s(1).Text
sourcedest = lvwMap.ListItems.Item(x).L
istSubItem
s(3).Text
CopyText = rs.Fields(str1).Value
' ERROR ON THE LINE BELOW RUNTIME ERROR 13 TYPE MISMATCH
oDest.SHeets(List4.Text).R
ange(oDest
.workbooks
(1).SHeets
(List4.Tex
t).Cells(x
2, CInt(sourcedest)), oDest.workbooks(1).SHeets(
List4.Text
).Cells((x
2), CInt(sourcedest))).Value = CopyText
Next x
x2 = x2 + 1
rs.MoveNext
Loop
rs.Close
conn.Close
' NEW CODE **************************
**********
**********
**********
**
MsgBox "Acces Data Import is Completed !"