VBA code not working (Error 13 Type Mismatch)

Question

Can someone help me why my code in Visual Basic for Applications is not working? I get an error 13 (Mismatch Error). I am new in VBA and i also got help to create this macro. Used the debug tool in VBA, after the yellow line in 'objMyCmd.CommandType = objMyCmd.CommandText' i get the error message.

Thank you!

Option Explicit

Sub GetDataFromADO()

    'Declare variables
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim objMyCmd2 As ADODB.Command
        Dim objMyRecordset2 As ADODB.Recordset
        Dim objMyCmd3 As ADODB.Command
        Dim objMyRecordset3 As ADODB.Recordset

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset
        Set objMyCmd2 = New ADODB.Command
        Set objMyRecordset2 = New ADODB.Recordset
        Set objMyCmd3 = New ADODB.Command
        Set objMyRecordset3 = New ADODB.Recordset

    'Open Connection
        objMyConn.ConnectionString = "Provider=SQLNCLI11;Data Source=.\MSSQLSERVER2014;User ID=USER;Password=PASSWORD;Database=DBNAME"
        objMyConn.Open

    'Set and Excecute SQL Command
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "SELECT PrKey,TimePeriod,FKProduct FROM Database.header2 WHERE TimePeriod >= '2022-1-1' AND Target > 200"
        objMyCmd.CommandType = objMyCmd.CommandText

    'Open Recordset
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

    'Copy Data to Excel
        Worksheets("Sheet34").Range("A2").CopyFromRecordset objMyRecordset

    'Set and Excecute SQL Command
        Set objMyCmd2.ActiveConnection = objMyConn
        objMyCmd2.CommandText = "SELECT FKMachine,BatchId,PeriodEnd,Target,Density,Tare,XqMid,CreateDateTime FROM Database.header2 WHERE TimePeriod >= '2022-1-1' AND Target > 200"
        objMyCmd2.CommandType = objMyCmd2.CommandText

    'Open Recordset
        Set objMyRecordset2.Source = objMyCmd2
        objMyRecordset2.Open

    'Copy Data to Excel
        Worksheets("Sheet34").Range("F2").CopyFromRecordset objMyRecordset2

    'Set and Excecute SQL Command
        Set objMyCmd3.ActiveConnection = objMyConn
        objMyCmd3.CommandText = "SELECT PrKey,FKProduct,Id,name FROM Database.Header1 WHERE DateBegin >= '2022-1-1'"
        objMyCmd3.CommandType = objMyCmd3.CommandText

    'Open Recordset
        Set objMyRecordset3.Source = objMyCmd3
        objMyRecordset3.Open

    'Copy Data to Excel
        Worksheets("Sheet23").Range("A2").CopyFromRecordset objMyRecordset3
       
    'Set weeknumber formula's
        Call WeekFormula
       
    'Load Data In Week Sheet
        Call ThisWeekData

End Sub```

VBA code not working (Error 13 Type Mismatch)

0 Answers