Results 1 to 2 of 2
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    WHY! (2000 sr 1)

    Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
    ' Example: ADOImportFromAccessTable "C:FolderNameDataBaseName.mdb", _
    "TableName", Range("C1")
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    ' open the database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
    DBFullName & ";"
    Set rs = New ADODB.Recordset
    With rs
    ' open the recordset
    .Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable
    ' all records
    '.Open "SELECT * FROM " & TableName & _
    " WHERE [FieldName] = 'MyCriteria'", cn, , , adCmdText
    ' filter records

    RS2WS rs, TargetRange ' write data from the recordset to the worksheet

    ' ' optional approach for Excel 2000 or later (RS2WS is not necessary)
    ' For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
    ' TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    ' Next
    ' TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

    End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
    End Sub


    Sub DAOCopyFromRecordSet(DBFullName As String, TableName As String, _
    FieldName As String, TargetRange As Range)
    ' Example: DAOCopyFromRecordSet "C:FolderNameDataBaseName.mdb", _
    "TableName", "FieldName", Range("C1")
    Dim db As Database, rs As Recordset
    Dim intColIndex As Integer
    Set TargetRange = TargetRange.Cells(1, 1)
    Set db = OpenDatabase(DBFullName)
    Set rs = db.OpenRecordset(TableName, dbOpenTable) ' all records
    'Set rs = db.OpenRecordset("SELECT * FROM " & TableName & _
    " WHERE " & FieldName & _
    " = 'MyCriteria'", dbReadOnly) ' filter records
    ' write field names
    For intColIndex = 0 To rs.Fields.Count - 1
    TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
    Next
    ' write recordset
    TargetRange.Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
    db.Close
    Set db = Nothing
    End Sub

    i have this 2 script( http://www.exceltip.com/excel_tips/Import_...n_VBA/213.html)

    But dont work!

    I press PF8 to run step by step but not run....

    Peraphs i set various psrameter.
    My db is in erova.mdb
    Tks for all

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WHY! (2000 sr 1)

    You can't run a sub with parameters with F8. Put a breakpoint (F9) on the line :
    Sub ADOImportFromAccessTable(DBFullName As String, _
    TableName As String, TargetRange As Range)
    Then go to the immediate window ( CTR G ) and type something like in the sample line :
    ADOImportFromAccessTable "C:FolderNameDataBaseName.mdb", "TableName", Range("C1")
    The execution of the code will stop on the break line and now you can step through the code using F8.
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •