Re: programmatic linking to external tables


Ben wrote:
> I have a directory structure that is like:
>
> /year/month
>
> and within this structure, there are files with date suffixes like:
> filename_MMDDYYY.xls
>
> I need to extract a subset of the data in a particular tab each day.
> I like to ask, if there's an programmatic way to link and query each day's
> data, since the directory structure is static and the file naming convention
> is consistent.

Instead of creating then deleting an attached table, consider querying
the Excel table direct e.g. (untested)

Sub test()
Const SQL As String = _
"SELECT * FROM" & _
" [Excel 8.0;HDR=YES;" & _
" DATABASE=<<FILENAME>>].[Sheet1$];"

Dim filename As String
filename = _
Format$(Date, "/yyyy/mmm/") & _
"filename_" & _
Format$(Date, "mmddyyyy") & _
".xls"

Dim sourceSql As String
sourceSql = _
Replace(SQL, "<<FILENAME>>", filename)

Dim rs As Object
Set rs = _
CurrentProject.Connection.Execute(sourceSql)
MsgBox rs.GetString

End Sub


Jamie.

--

.

Loading