Macro that querys only 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:
040605AW.dbf
The next day a new one is created (Y/M/D):
040606AW.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've tried to record "down arrow key"
and "end" key stocks but the macro still only records what
ever file I had last selected.

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

Below I have listed the complete Macro for your review:


Sub GetData()
'
' GetData Macro
' Macro recorded 5/21/2004 by The Shredder Company
'
' 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

 

[Nigel]

One way (untested) is the substitute the file reference in the select
statement with a variable.......

"SELECT `040521BW`.Date, `040521BW`.Time,
`040521BW`.RTD_1, `040521BW`.RTD_2" & Chr(13) & "" & Chr
(10) & "FROM `040521BW` `040521BW`" _
)

change to......

put this statement before your Query statement.......

Dim fname as String

then change the select statement with the variable substitution as
follows.....

"SELECT" & fname ".Date," & fname & ".Time," & _
fname & ".RTD_1," & fname & ".RTD_2" & Chr(13) & "" & Chr
(10) & "FROM " & fname & fname)

then control the fname as a variable, the simplest would be to ask for a
file name to be entered but if you wish to automate this you'll need to
interact with the File system object (FSO) to manipulate the directory and
pull out the latest file.

Cheers
Nigel

 

[Alaniz]

Hi Nigel,

I tried what you suggested, but I got tons of syntax
errors. After a fassion I was able to clean up the edit
but when I ran the macro I got an ODBC error and it pointed
to this line of code:

.Refresh BackgroundQuery:=False

Any ideas?

Again, many thanks for your input. Apprieciated.

Alaniz

-----Original Message-----
One way (untested) is the substitute the file reference in the select
statement with a variable.......

"SELECT `040521BW`.Date, `040521BW`.Time,
`040521BW`.RTD_1, `040521BW`.RTD_2" & Chr(13) & "" & Chr
(10) & "FROM `040521BW` `040521BW`" _
)

change to......

put this statement before your Query statement.......

Dim fname as String

then change the select statement with the variable substitution as
follows.....

"SELECT" & fname ".Date," & fname & ".Time," & _
fname & ".RTD_1," & fname & ".RTD_2" & Chr(13) & "" & Chr
(10) & "FROM " & fname & fname)

then control the fname as a variable, the simplest would be to ask for a
file name to be entered but if you wish to automate this you'll need to
interact with the File system object (FSO) to manipulate the directory and
pull out the latest file.

Cheers
Nigel

Hello,

I am using Excell 2000 to query an external dBase file.
This file is created each day with the following format:
040605AW.dbf
The next day a new one is created (Y/M/D):
040606AW.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've tried to record "down arrow key"
and "end" key stocks but the macro still only records what
ever file I had last selected.

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

Below I have listed the complete Macro for your review:


Sub GetData()
'
' GetData Macro
' Macro recorded 5/21/2004 by The Shredder Company
'
' 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\SHINEBROTHER S_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

.