Excel Macro displays error after executing sql query

Question

Below is the code for my macro. The issue is my macro runs fine(executes query and puts data on the spreadsheet) but I get this error

RunTime Error '13' Type Mismatch

The macro executes the query and puts the data on the spreadsheet but I get the error with yellow highlight on this line

Strcode = Target.Value

Any suggestions?

    Sub JobTaskHistory(ByVal Target As Range)
Dim sqlstring As String
Dim connstring As String
Dim Strcode As String

'Strcode = Trim(InputBox("Please enter a Job Number", "Job Task history"))
Strcode = Target.Cells(1, 1).Value
sqlstring = "select distinct m.JobNumber , cast(m.ExpectedDate as DATE) 'Ship Date'  &_
            " from ArchiveJobHeader m  left join AuxiliaryInfoFile af (nolock) on af.jobnumber=m.jobnumber left join CostEntry ce (NOLOCK) on ce.sJobNumber = m.JobNumber  left join CostCenterFile cc (nolock) ON cc.Code = ce.sDepartmentCode  left join JobExtra j on j.JobNumber = m.JobNumber   & _
             " where m.JobNumber = '" & Trim(Strcode) & "'" & _
             " order by 'Resulttime'"
connstring = "ODBC;DSN=Test;UID=Test;PWD=Test123"

 Dim thisQT As QueryTable
 Dim lastRow As Long, nextRow As Long

 lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
 nextRow = lastRow + 1

 'Set thisQT = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("a1", "a1000"))
Set thisQT = ActiveSheet.QueryTables.Add( _
                 Connection:=connstring, _
                 Destination:=Range("A" & nextRow))
 thisQT.BackgroundQuery = False

 thisQT.Sql = sqlstring


thisQT.Refresh

End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Call JobTaskHistory(Target.Cells(1, 1).Value)
End Sub

Jason Faulkner · Accepted Answer · 2015-01-12 15:00:05Z

1

You will get this error if your Range (Target) contains more than one cell.

For example:

Dim rangeTest As Range

Set rangeTest = Range("A1")
MsgBox rangeTest.Value ' This works.

Set rangeTest = Range("A1:B1")
MsgBox rangeTest.Value ' This fails with runtime 13 error.

So to fix it, make sure you are only referencing a single cell. To be safe, you can just access the first cell in the range using this:

' This will work if Target is a single cell or multiple.
' It will always use the upper-left most cell in the range.
Strcode = Target.Cells(1, 1).Value

Update after you made your edit

This will cause an error:

Private Sub Worksheet_Change(ByVal Target As Range)
    Call JobTaskHistory(Target.Cells(1, 1).Value)
End Sub

Because you are passing in a value and not a Range which your function expects. Replace the line with this to make it compatible with your JobTaskHistory method:

Call JobTaskHistory(Target)

edited Jan 12, 2015 at 15:00

answered Jan 12, 2015 at 14:26

Jason Faulkner

6,6782 gold badges31 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LearningMacro Over a year ago

Thank you for your quick response. I tried using the the example you suggested but still get the same error. Can you help me fix this? Stuck with this issue for long time. Thanks again.

Jason Faulkner Over a year ago

@Node.JSBeginner - What are the cells included in the Target range when you call this function?

Jason Faulkner Over a year ago

@Node.JSBeginner - Please see the question in my previous comment. I can reproduce your exact error when Target is a range containing more than 1 cell. The fixed noted above worked for me.

LearningMacro Over a year ago

When I run the macro (after editing per your answer), my sql query runs in loop for 104 times and puts column headers on the spreadsheet. Any help is greatly appreciated

Jason Faulkner Over a year ago

@Node.JSBeginner - It sounds like the subject of this question has been resolved, so that would be a different issue. I would suggest creating another question with the appropriate context defined for it.

Collectives™ on Stack Overflow

Excel Macro displays error after executing sql query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related