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; its just the part of getting the code to look at each row in a table in sequence that baffles me.
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
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)
Do Until .EOF
'replace 1 below with your fieldname
MyVar = .Fields(1)
using DAO (in the code window, go to Tools-References and add Microsoft DAO Object Library 3.6.
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
'goto the next record
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.
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;"
Set rst = CurrentDb.OpenRecordset(strTable, dbOpenDynaset)
If Not .EOF And Not .BOF Then
Do While Not .EOF
strMessage = "open " & .Fields("FullPath")