VBA Excel marco Connection with SQL server RRS feed

  • Question

  • HI, i try to collect data from my MS SQL server insert into my excel file As below the example file image. i have an error with my VBA code. hope can advise. Thank you.

    Public Sub RawData()
        Dim oConn As ADODB.Connection
        Dim strSQL As String
        Dim qRS As New Recordset
        Dim FIFromDate As String
        Dim FITillDate As String
        Dim MFGFromDate As String
        Dim MFGTillDate As String
        Dim FIDate As String
        Set qRS = New ADODB.Recordset
        Set oConn = New ADODB.Connection
        oConn.CommandTimeout = 0
        oConn.Open "Provider=SQLOLEDB;Data Source=;Initial Catalog=CIMProRPT01;User ID=meshview;Password=mes$hview"
        strSQL = Worksheets("RawData").Cells(9, 2)
     qRS.Open strSQL, oConn
        Counter = 14
        CColor = 1
        If qRS.EOF = False Then
        Worksheets("RawData").Cells(Counter, 1) = qRS.Fields("TIMEOUT_FI")
        Worksheets("RawData").Cells(Counter, 2) = qRS.Fields("MFGDATE")
        Worksheets("RawData").Cells(Counter, 3) = qRS.Fields("LOT_ID")
        Worksheets("RawData").Cells(Counter, 4) = qRS.Fields("WO_ID")
        Worksheets("RawData").Cells(Counter, 5) = qRS.Fields("PRODUCT_ID")
        Worksheets("RawData").Cells(Counter, 6) = qRS.Fields("LOT_TYPE")
        Worksheets("RawData").Cells(Counter, 7) = qRS.Fields("LOT_CATEGORY")
        Worksheets("RawData").Cells(Counter, 8) = qRS.Fields("BATCH_ID")
        Worksheets("RawData").Cells(Counter, 9) = qRS.Fields("STEP_ID")
        Counter = Counter + 1
        r = r + 1
        If CColor = 1 Then
            Sheets("RawData").Range("E8:K8").Interior.ColorIndex = 6
            CColor = 2
            Sheets("RawData").Range("E8:K8").Interior.ColorIndex = 1
            CColor = 1
        End If
          Exit Do
        End If
        Set qRS = Nothing

    Friday, August 17, 2012 6:08 AM

All replies

  • Will you pls specify where the problem occurs ?

    I have tested and it worked ok

    I suppose you have problem with Provider string.In that case I suggest :

    In your pc create a file with extension .udl (Like abc.udl), double click it,select appropriate driver for sql and see how you are guided.Once completed open the file in notepad or wordpad and look for the provider string.

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 17, 2012 11:34 AM