Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that querys only the most current dBase file.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that querys only the most current dBase file.
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that querys only the most current dBase file.
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 "Alaniz" wrote in message ... 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\SHINEBROTH ER 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to create dbase file from text file? | Excel Discussion (Misc queries) | |||
Macro to open most current file in folder | Excel Discussion (Misc queries) | |||
how can I view a Dbase file with more than the 65k records that E. | Excel Discussion (Misc queries) | |||
Creating a Macro that loads the most current dBase file. | Excel Programming | |||
Macro to play wave file in current directory | Excel Programming |