Fordraiders
asked on
RUNTIME ERROR 13 : TYPE MISMATCH ???
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 !"
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
"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
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
lastDestRow = oDest.SHeets(List4.Text).U
' 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,
oDest.ActiveCell.EntireRow
For x = 1 To lvwMap.ListItems.Count
str1 = lvwMap.ListItems.Item(x).L
sourcedest = lvwMap.ListItems.Item(x).L
CopyText = rs.Fields(str1).Value
' ERROR ON THE LINE BELOW RUNTIME ERROR 13 TYPE MISMATCH
oDest.SHeets(List4.Text).R
Next x
x2 = x2 + 1
rs.MoveNext
Loop
rs.Close
conn.Close
' NEW CODE **************************
MsgBox "Acces Data Import is Completed !"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and more important is the address of the cell you're trying to fill correct at time of inserting the copytext
Have you tried using CINT on x2? ex -> .cells(cint(x2), cint(sourcedest))
Also, if that doesn't work try removing the .value from the left side of your equation.
Also, if that doesn't work try removing the .value from the left side of your equation.
oh, guess the second part of mine looks a lot like bruintje's answer. sorry.
no need for sorries that's EE :-)
ASKER
To All,
I get the error on this
CInt(sourcedest))
I put the cursor when the error pops up.
But I have tried taking the .value off also...
still the error.
Thanks
fordraiders
I get the error on this
CInt(sourcedest))
I put the cursor when the error pops up.
But I have tried taking the .value off also...
still the error.
Thanks
fordraiders
can you see what's in your variables if you set a breakpoint at the line?
ASKER
Yes,
and what did ring on a bell from both of you is the .Value issue
I have yet to figure it out but,
The index value on the listbox selection was not correct.
I changed the .text to
FROM :
sourcedest = lvwMap.ListItems.Item(x).L istSubItem s(3).Text
TO:
sourcedest = CInt(lvwMap.ListItems.Item (x).SubIte ms(4))
I was puliing in the value text not the actual index value....
Thanks
I hope this was it..
Still playing around...
fordraiders
and what did ring on a bell from both of you is the .Value issue
I have yet to figure it out but,
The index value on the listbox selection was not correct.
I changed the .text to
FROM :
sourcedest = lvwMap.ListItems.Item(x).L
TO:
sourcedest = CInt(lvwMap.ListItems.Item
I was puliing in the value text not the actual index value....
Thanks
I hope this was it..
Still playing around...
fordraiders
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.