none
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=10.238.15.14;Initial Catalog=CIMProRPT01;User ID=meshview;Password=mes$hview"
        
    
        strSQL = Worksheets("RawData").Cells(9, 2)
    
    
    
     qRS.Open strSQL, oConn
        
        Counter = 14
        
        CColor = 1
        
        Do
        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")
    
    
    
      qRS.MoveNext
        Counter = Counter + 1
        r = r + 1
            
        If CColor = 1 Then
         
            Sheets("RawData").Range("E8:K8").Interior.ColorIndex = 6
            CColor = 2
            
        Else
         
            Sheets("RawData").Range("E8:K8").Interior.ColorIndex = 1
            CColor = 1
        
        End If
        
        Else
          Exit Do
        End If
        Loop
        
        qRS.Close
        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
    Answerer