0

I have the VBA script as below to pull the SQL (stored procedure) and fetching to Excel. I allow the excel cell "B1" to enter the date, but I am facing the error message

Error converting varchar to datetime

I have tried to change the Excel column "B1" to date format but the error still remains. Please help. Thanks 

Sub puldata()
    Dim oConn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim mssql As String
    Dim row As Integer
    Dim col As Integer
    Dim DateTo As Date
    Dim ws As ThisWorkbook
    Set ws = ThisWorkbook
    Application.ScreenUpdating = False
    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    'oConn.Parameters.Append oConn.CreateParameter("DateTo", adDate, adParamInput, 10, Range("D1").Text)

    mssql = "exec dbo.HP_Receivable @DateTo = '" & Cells(1, 2) & "';"

    oConn.ConnectionString = "Driver={SQL Server Native Client 11.0}; Server=10.88.71.32; Database=HCMY; UID=sa; PWD=Hcmy@sap"

    'oConn.ConnectionTimeout = 30
    oConn.Open

    rs.Open mssql, oConn

    If rs.EOF Then
        MsgBox "No Matching recrods found"
        rs.Close
        oConn.Close
        Exit Sub
    End If

    row = 3
    col = 1

    For Each fld In rs.Fields
        Sheet1.Cells(row, col).Value = fld.Name
        col = col + 1
    Next

    rs.MoveFirst
    row = row + 1

    Do While Not rs.EOF
        col = 1

        For Each fld In rs.Fields
            Sheet1.Cells(row, col).Value = fld
            col = col + 1
        Next

        row = row + 1
        rs.MoveNext
    Loop

    rs.Close
    oConn.Close
End Sub
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2334112
  • 19
  • 1
  • It looks your stored procedure returns different type of what you expect - VARCHAR instead of DATE. There is not enough information where the root cause for this is - i.e. is it the DB setup/column type or the stored procedure cast to varchar for whatever reason. You may need to convert the result to date after you populate the data on the sheet or while populating. given that currently you don't perform any additional actions on recodset why not use [Range.CopyFromRecordset](https://learn.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset)? – buran Sep 10 '19 at 10:48
  • What value you have in `Cells(1, 2)` ? Try in the immediate window `?Cells(1, 2)`. Also keep in mind the formatting, SQL Server will translate `10/09/2019` (i.e.: today's date) into `09 October 2019` – FAB Sep 10 '19 at 10:55
  • @FAB That depends on the [language settings](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017) of the connection. – GSerg Sep 10 '19 at 14:18
  • "Error Converting varchar to datetime" is an SQL Server error. The only place you can be getting it from is the value you have in `Cells(1,2)`. You should not be [concatenating that value into the query](https://stackoverflow.com/q/332365/11683) in the first place. Create an `ADODB.Command` explicitly and add the date as a parameter. – GSerg Sep 10 '19 at 14:20
  • 1
    debug.print mssql. Take the result of that, and try to execute in SSMS or other client that an directly execute queries. Does it work? Why or why not? – Jeremy Sep 10 '19 at 14:27
  • @FAB in cells (1,2) i was input the date 2019-08-31 in order to execute my report in store procedure, i have tried to change the format in cell (1,2) to different date format (i.e yyyy-mm-dd) but the issue still same – user2334112 Sep 11 '19 at 01:05

0 Answers0