Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    London
    Posts
    10

    Unanswered: Look at a table row by row

    Hi everyone, its my first time writing, but I have used this site before and its proved so helpful - thank you.

    I've seen this done before, but for the life of me cannot figure out how to write it.

    I have a table tbl_filelist that contains the paths to excel files in the column 'path'. Through Visual Basic I want to import a range, 'sick_data', from each file. The import part is simple enough; it’s just the part of getting the code to look at each row in a table in sequence that baffles me.

    Please help.

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Here is some code that will do just that
    I wrote it in DAO but could possibly come up with an ADO soloution if required


    Code:
    Sub test()
        Dim qdf As DAO.QueryDef
        Dim rs As DAO.Recordset
        
        Dim MyVar As String
        
        Set qdf = CurrentDb.CreateQueryDef("QryTemp", "SELECT * FROM tblTest")
        Set rs = CurrentDb.OpenRecordset(qdf.Name)
        
        With rs
            .MoveFirst
            Do Until .EOF
            'replace 1 below with your fieldname
            MyVar = .Fields(1)
            .MoveNext
            Loop
         End With
    End Sub
    Dave

  3. #3
    Join Date
    Apr 2004
    Location
    Kingsland, Georgia
    Posts
    231
    using DAO (in the code window, go to Tools-References and add Microsoft DAO Object Library 3.6.
    Code:
    Dim rs as recordset
    'puts all the records in the recordset rs
    Set rs = CurrentDb.OpenRecordset("tbl_filelist")
    
    'while there are still records (i.e. "rows") in the recordset
    Do While rs.EOF = False
    
       'do something with the information in the path column. 
       'here, it prints it to the debug window
       Debug.Print rs.Fields("path")
    
       'goto the next record
       rs.MoveNext
    Loop
    
    Set rs = Nothing
    You'd change the Debug.Print bit to do whatever you want with it. for example, you could put a avriable called strTemp and say strTemp = rs.Fields("path") and then perform string operations on the strTemp. Whatever. Hope it helps.

  4. #4
    Join Date
    Nov 2004
    Location
    London
    Posts
    10

    Cheers!

    Thanks for your help lads. I managed to peice this together: (I hope I can return the favour sometime)

    Private Sub updatesource_Click()
    Dim rst As DAO.Recordset
    Dim strTable As String
    Dim strMessage As String
    strTable = "SELECT SubFolder & " / " & FileName AS [FullPath] FROM tbl_FileList;"
    Debug.Print strTable
    Set rst = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)
    With rst
    If Not .EOF And Not .BOF Then
    Do While Not .EOF
    strMessage = "open " & .Fields("FullPath")
    .MoveNext
    MsgBox strMessage
    Loop
    End If
    End With
    End Sub

Posting Permissions

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