Who can take on this challenge

[Guest]

I have been given data that sits in two different kinds of files. I have
four years of Excel spreadsheets (formatted alike); one for each day. I also
have the same data in a number of databases (one for each month) in tables
(one for each date).

My challenge to you is to suggest the best way to import that data (and
whether to take it from the databases or spreadsheets) into one table on one
spreadsheet.

 

[Marshall Barton]

I have been given data that sits in two different kinds of files. I have
four years of Excel spreadsheets (formatted alike); one for each day. I also
have the same data in a number of databases (one for each month) in tables
(one for each date).

My challenge to you is to suggest the best way to import that data (and
whether to take it from the databases or spreadsheets) into one table on one
spreadsheet.

That's your challange, not ours. ;-)

I would use the databases if they are Access MDB files. If
the files have a reasonable naming convention and are
located in the same folder, then you could conceivably use
nested code loops in the final destination Access database.

The Dir function can retrieve each file name in a loop and
you can then loop through the TableDefs collection to
identify the tables. Then you can Execute an INSERT INTO
query to copy the records from each table into your new
master table.

strFile = Dir("path to folder\*.mdb")
Do Until strfile = ""
For Each tdf in db.TableDefs
If tdf.Name Like "some pattern"
db.Execute "INSERT INTO [new table] " & _
"SELECT * FROM [" & tdf.Name & _
" IN ""path to folder\" & strFile & """"
End If
Next tdf
strFile = Dir()
Loop

 

[Guest]

Yes, my challenge, but one of my brilliant tools is your brilliant advice..
Pretty smart of me, eh?

I think I can see what you mean with the loops. What I am not exactly
catching I can probably look up in my Visual Basic textbook.

What does tdf represent?

Have a great weekend.
--
Thanks

I have been given data that sits in two different kinds of files. I have
four years of Excel spreadsheets (formatted alike); one for each day. I also
have the same data in a number of databases (one for each month) in tables
(one for each date).

My challenge to you is to suggest the best way to import that data (and
whether to take it from the databases or spreadsheets) into one table on one
spreadsheet.

That's your challange, not ours. ;-)

I would use the databases if they are Access MDB files. If
the files have a reasonable naming convention and are
located in the same folder, then you could conceivably use
nested code loops in the final destination Access database.

The Dir function can retrieve each file name in a loop and
you can then loop through the TableDefs collection to
identify the tables. Then you can Execute an INSERT INTO
query to copy the records from each table into your new
master table.

strFile = Dir("path to folder\*.mdb")
Do Until strfile = ""
For Each tdf in db.TableDefs
If tdf.Name Like "some pattern"
db.Execute "INSERT INTO [new table] " & _
"SELECT * FROM [" & tdf.Name & _
" IN ""path to folder\" & strFile & """"
End If
Next tdf
strFile = Dir()
Loop

 

[Marshall Barton]

Yes, my challenge, but one of my brilliant tools is your brilliant advice..
Pretty smart of me, eh?

I think I can see what you mean with the loops. What I am not exactly
catching I can probably look up in my Visual Basic textbook.

What does tdf represent?

I would think that someone clever enough to get me to help
would be able to deduce the meaning of tdf. ;-)

The missing stuff would be:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strFile As String

Set db = CurrentDb()
. . .

But I'm not so clever that I can figure out what patterns
should be used to identify the names of the tables that you
want to import.