Creating a Macro that loads the most current dBase file.

[Alaniz]

Hello,

I am using Excell 2000 to query an external dBase file.
This file is created each day with the following format:
040521BW.dbf
The next day a new one is created (Y/M/D):
040522BW.dbf

I sucessfully recorded a Macro that follows my MS Query
steps to bring this data in to my spread sheet. I then
can do a dynamic refresh. Works great! But it only points
to that one file I selected during my macro record. I've
tried to record "down arrow key" and "end" key strokes but
the macro still only records only the file I had selected.

I need to modify the macro so that it only gets the last
created file in the directory. Any help? Thank you in
advance.

Below I have listed the complete Macro for your review,
(the SELECT command is what need to be modified I think):


Sub GetData()
'
' GetData Macro
' Macro recorded 5/21/2004 by Me
'
' Keyboard Shortcut: Ctrl+g
' The If Then code is used to prevent multiply data files
crowding the spread sheet
If [C2].Value < 1 Then
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=RSView;DefaultDir=C:\TEMP\SHINEBROTHERS_R
SVIEW\DLGLOG\RSVIEW;DriverId=277;FIL=dBase
IV;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `040521BW`.Date, `040521BW`.Time,
`040521BW`.RTD_1, `040521BW`.RTD_2" & Chr(13) & "" & Chr
(10) & "FROM `040521BW` `040521BW`" _
)
.Name = "Query from RSView_6"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Columns("A:A").Select
With Selection.Interior
.ColorIndex = 15
.PatternColorIndex = xlAutomatic
End With
Columns("A:A").Select
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("E2").Select
End If
End Sub

 

[Jamie Collins]

Hello,

I am using Excell 2000 to query an external dBase file.
This file is created each day with the following format:
040521BW.dbf
The next day a new one is created (Y/M/D):
040522BW.dbf

I sucessfully recorded a Macro that follows my MS Query
steps to bring this data in to my spread sheet. I then
can do a dynamic refresh. Works great! But it only points
to that one file I selected during my macro record. I've
tried to record "down arrow key" and "end" key strokes but
the macro still only records only the file I had selected.

I need to modify the macro so that it only gets the last
created file in the directory. Any help? Thank you in
advance.

Below I have listed the complete Macro for your review,
(the SELECT command is what need to be modified I think):
<snip>

Is it déjà vu or has this been posted before?! In case you haven't got
an answer...

You have two issues here; separating them may help get an answer:

1. How to get the latest filename
2. How to plug the filename into your SQL code.

The first problem will interest a lot of people in these ngs. A
separate post which doesn't mention SQL (e.g. Dave Peterson doesn't do
SQL <g>) may be in order.

I thought you already had an answer to the second problem but you will
find the details you need at Dick Kusleika's site:

http://www.dicks-clicks.com/excel/ExternalData5.htm#ChangeSQL

HTH,
Jamie.

--

 

[Dave Peterson]

ahem!

maybe something like this will help:

Option Explicit
Sub testme()

With Application.FileSearch
.NewSearch
'Change this to your directory
.LookIn = "C:\my documents\excel"
.Filename = ".xls"
.SearchSubFolders = False
If .Execute(msoSortByLastModified, msoSortOrderDescending) > 0 Then
MsgBox .FoundFiles(1) & vbLf & FileDateTime(.FoundFiles(1))
End If
End With

End Sub

Change that .lookin and .filename stuff.

(And I agree with you, Jamie. Better two posts. It might be just me, but when
I see Charting or SQL (or lots of other stuff, I may read them (and the
responses), but I generally leave them to the smart people.)