Snasui Excel Forum

Code: Select all

Sub importfile()
    Dim conn As ADODB.Connection
    Set rs = New ADODB.Recordset
    Dim sql As String, sFile As String
    sFile = "D:\data.mdb"
    sql = "select * from txt"    
    sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFile & ";" & _
    "Persist Security Info=False"    
'    If rs.State = 1 Then rs.Close    
    rs.Open sql, sConStr
    With Sheets("Sheet1")
        .Range("A1").CurrentRegion.Offset(1, 0).Clear
        .Range("A2").CopyFromRecordset rs
    End With
'    Do While Not rs.EOF
'    rs.MoveNext
'    Loop
    Set conn = Nothing
    Set rs = Nothing
End Sub

Code: Select all

Sub importfile()
    
Dim I As Integer
    
    Dim conn As ADODB.Connection
    Set rs = New ADODB.Recordset
    Dim sql As String, sFile As String
    Application.ScreenUpdating = False
    
    sFile = Range("path01").Value
    sql = "select * from ccap_booked_daily"
    sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFile & ";" & _
    "Persist Security Info=False"
 
    Sheets("ccap_booked_daily").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select

    rs.Open sql, sConStr
    With Sheets("ccap_booked_daily")
        .Range("A1").CurrentRegion.Offset(1, 0).Clear
        .Range("A2").CopyFromRecordset rs
    End With
'    Do While Not rs.EOF
'    rs.MoveNext
'    Loop
    Set conn = Nothing
    Set rs = Nothing
    Call akekorn
    MsgBox "Programe Complete"
End Sub

Code: Select all

Sub importfile()
    
Dim I As Integer
    
    Dim conn As ADODB.Connection
    Set rs = New ADODB.Recordset
    Dim sql As String, sFile As String
    Application.ScreenUpdating = False
    
    sFile = Range("path01").Value
    sql = "select * from ccap_booked_daily"
    sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFile & ";" & _
    "Persist Security Info=False"
 
    Sheets("ccap_booked_daily").Select
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
'*****************
I = ActiveCell.Row
Do While Not rs.EOF
For J = 0 To rs.Fields.Count - 1
Cells(I, J + 1).Value = rs.Fields(J)
Next J
rs.MoveNext
I = I + 1
Loop


'********************

    rs.Open sql, sConStr
    With Sheets("ccap_booked_daily")
        .Range("A1").CurrentRegion.Offset(1, 0).Clear
        .Range("A2").CopyFromRecordset rs
    End With
'    Do While Not rs.EOF
'    rs.MoveNext
'    Loop
    Set conn = Nothing
    Set rs = Nothing
    Call akekorn
    MsgBox "Programe Complete"
End Sub

Code: Select all

Sub importfile()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sConstr As String
    Dim FieldRange As Range
    Dim fld As Field, i As Integer
    Dim sql As String, sFile As String
    Sheets("Sheet1").Range("A1").CurrentRegion.Clear
    sFile = "E:\data.mdb"
    sql = "select * from txt"
    sConstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & sFile & ";" & _
    "Persist Security Info=False"
    conn.Open sConstr
    With Sheets("Sheet1")
        Set FieldRange = .Range("A1")
        rs.Open sql, conn
        For Each fld In rs.Fields
            FieldRange.Offset(0, i).Value = fld.Name
            i = i + 1
        Next fld
        .Range("A2").CopyFromRecordset rs
    End With
    Set conn = Nothing
    Set rs = Nothing
End Sub