Click here to Skip to main content
15,884,237 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to split an excel spreadsheet with different people's information into different spreadsheets with a single person's information. I got some VBA code from the website but ran into error when I tried to run the code. The error code is "Data type mismatch in criteria expression" And the code with problem is the line ".Range("A2").CopyFromRecordset conn.Execute(Sql) "

Could anyone help me with this? Thank you so much!

What I have tried:

VB
Sub CFGZB()
    Dim myRange As Variant
    Dim myArray
    Dim titleRange As Range
    Dim title As String
    Dim columnNum As Integer
    myRange = Application.InputBox(prompt:="choose title row", Type:=8)
    myArray = WorksheetFunction.Transpose(myRange)
    Set titleRange = Application.InputBox(prompt:="choose the column", Type:=8)
    title = titleRange.Value
    columnNum = titleRange.Column
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim i&, Myr&, Arr, num&
    Dim d, k
    For i = Sheets.Count To 1 Step -1
        If Sheets(i).Name <> "Information" Then
            Sheets(i).Delete
        End If
    Next i
    Set d = CreateObject("Scripting.Dictionary")
    Myr = Worksheets("Information").UsedRange.Rows.Count
    Arr = Worksheets("Information").Range(Cells(2, columnNum), Cells(Myr, columnNum))
    For i = 1 To UBound(Arr)
        d(Arr(i, 1)) = ""
    Next
    k = d.keys
    For i = 0 To UBound(k)
        Set conn = CreateObject("adodb.connection")
        conn.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
        Sql = "select * from [Information$] where " & title & " = '" & k(i) & "'"
        Worksheets.Add after:=Sheets(Sheets.Count)
        With ActiveSheet
            .Name = k(i)
            For num = 1 To UBound(myArray)
                .Cells(1, num) = myArray(num, 1)
            Next num
         'it seems that this row has error
            .Range("A2").CopyFromRecordset conn.Execute(Sql)  
     
        End With
        Sheets(1).Select
        Sheets(1).Cells.Select
        Selection.Copy
        Worksheets(Sheets.Count).Activate
        ActiveSheet.Cells.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Next i
    conn.Close
    Set conn = Nothing
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
Posted
Updated 17-Jul-17 2:06am
v2

We can't help you much as the error depend on the contain of the sheet.
VB
.Range("A2").CopyFromRecordset conn.Execute(Sql)

Something go wrong in your code, but you don't understand what go wrong or why.
Use the debugger to see what your code is doing, Open the local variable window to inspect their values, you will probably discover that Sql do not contain what it should.
-----
There is a tool that allow you to see what your code is doing, its name is debugger. It is also a great learning tool because it show you reality and you can see which expectation match reality.
When you don't understand what your code is doing or why it does what it does, the answer is debugger.
Use the debugger to see what your code is doing. Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute.

Debugger - Wikipedia, the free encyclopedia[^]
Debugging in Excel VBA - EASY Excel Macros[^]
MS Excel 2013: VBA Debugging Introduction[^]
How to debug Excel VBA - YouTube[^]
The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.

PS: I used a second solution to keep things clear as both are not related.
 
Share this answer
 
The problem is in the Sql string you're trying to execute with that line of code. Since we can't see that string...

The error is saying that you're trying to pass in a type of value that is wrong for the place you're putting it. Like passing in a string where the SQL is expecting a numeric value, or the other way around.
 
Share this answer
 
VB
Sql = "select * from [Information$] where " & title & " = '" & k(i) & "'"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900