Excel Forum

Code: Select all

Function DataIB()
Dim rs  As ADODB.Recordset
Dim Connected As Boolean
Dim sql As String
Dim tName As String

Connected = ConnectToDB("10.10.0.0", "Data", "user", "")
tName = "Data"

If Connected Then

    sql = "SELECT  WR_CarID,WR_ID,WR_Date "
    sql = sql & "FROM V_PlanSVOut "
    'sql = sql & "Order By  WR_ID ASC"

    Set rs = New ADODB.Recordset

    rs.Open sql, Conn
    Sheets(tName).Cells(5, 1).CopyFromRecordset rs
    rs.Close
    
    '´Ö§ª×èͺÃÔÉÑ· 䫵ì§Ò¹
    sql = "SELECT CustomerName,SiteName "
    sql = sql & "FROM V_PlanSVOut "
    'sql = sql & "Order By  WR_ID ASC"

    Set rs = New ADODB.Recordset

    rs.Open sql, Conn
    Sheets(tName).Cells(5, 5).CopyFromRecordset rs
    rs.Close
                    
    Conn.Close
    Set Conn = Nothing
    Set rs = Nothing
    
    DataIB = True
Else
     DataIB = False
End If

End Function
  

Code: Select all

rs.MoveFirst
iCount = 1: rws = 2: col = 0
Do While rs.EOF = False
    With wb.Sheets(1)
        For Each fld In rs.Fields(iCount)
            .Range("a" & rws).Offset(0, col).Value = fld
            col = col + 1
        Next fld
        iCount = iCount + 1
        rs.MoveNext
    End With
Loop
rs.Close

Code: Select all

For Each fld In rs.Fields(iCount)

Code: Select all

Function DataIB()
Dim rs  As ADODB.Recordset
Dim Connected As Boolean
Dim sql As String
Dim tName As String
Dim iCount, Rws, Col As Integer
Dim fld As Range

Connected = ConnectToDB("Server", "Data", "user", "")
tName = "Data"

If Connected Then
    sql = "SELECT  top 10 * "
    sql = sql & "FROM V_PlanSVOut "

    Set rs = New ADODB.Recordset

    rs.Open sql, Conn
    rs.MoveFirst
    iCount = 1:  Rws = 5:    Col = 0
    Do While rs.EOF = False
        With Sheets(tName)
            [b]For Each fld In rs.Fields(iCount)[/b]
                .Range("a" & Rws).Offset(0, Col).Value = fld
                Col = Col + 1
            Next fld
            iCount = iCount + 1
            rs.MoveNext
        End With
    Loop
    rs.Close
                
    Conn.Close
    Set Conn = Nothing
    Set rs = Nothing
    
    DataIB = True
Else
     DataIB = False
End If

End Function

Code: Select all

Do While rs.EOF = False
	Sheets(tName).Cells(i + 5, 1).Value = rs.Fields("CarID")
	Sheets(tName).Cells(i + 5, 2).Value = rs.Fields("ID")
	rs.MoveNext
	i = i + 1
Loop

Code: Select all

'Other code
Set rs = New ADODB.Recordset
rs.Open sql, Conn

Sheets("x").Range("a2").CopyFromRecordset rs
'Other code

Code: Select all

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Code: Select all

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True