Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro that loads 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: 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro that loads the most current dBase file.
"Alaniz" wrote ...
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/Ex....htm#ChangeSQL HTH, Jamie. -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Macro that loads the most current dBase file.
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.) Jamie Collins wrote: "Alaniz" wrote ... 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/Ex....htm#ChangeSQL HTH, Jamie. -- -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to create dbase file from text file? | Excel Discussion (Misc queries) | |||
excel loads another workbook to run the macro... | Excel Discussion (Misc queries) | |||
When Excel loads it autoloads a file | Excel Discussion (Misc queries) | |||
Macro that querys only the most current dBase file. | Excel Programming | |||
Creating a Macro that loads the most current dBase file. | Excel Programming |