Every morning I need to download data from a website. The data comes in the form of an Excel spreadsheet and shows the previous day's orders.
I created an Excel macro that I run each time I open these "raw" Excel files. The macro formats some columns and does general clean-up.
Next, I import the spreadsheet into Access, but here's the part that is rather tedious (and i'm sure there's a better way to do it)
I wrote a report that is based on a QUERY, and the query is based on a specific TABLE.
The table is named "AM".
Since the query is based on the table named, specifically, "AM", I have to make sure that the Excel file is ALWAYS imported into a new table called "AM"
I take the previous day's "MA" table and rename it "MA_2006_10_14" for example, and then import the new Excel file into a new table called "AM"
I do this day after day.
Here's what I'd like to do instead:
1. Assuming the daily Excel file ALWAYS has the same file name, and is ALWAYS saved in the same directory, I'd like to have a button in Access that will automatically import whatever is in that Excel file and APPEND it to the current "AM" table. (no more renaming yesterday's table and then creating a new one over and over ... I'd like to keep appending each day's data to yesterday's table.)
2. Next, when I run the report, I want to be promted "Enter Start Date" and then "Enter End Date". Since the table will keep getting appended (i.e. keep growing) I can control what data shows on the report by specifying a date range. (The table might have 30 days of orders but I only want to see the orders placed on October 14th, for example)
Any help would be much appreciated!!
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Temp", YourFileNameHere, True
DoCmd.RunSQL "INSERT INTO AM ( [DateFieldName] ) SELECT Temp.*, Date() as Expr1 FROM Temp;"
Then, use the following code in the Report_Open event:
Dim BegDate As Date
Dim EndDate As Date
BegDate = CDate(InputBox("Beginning Date:", "Report Parameters", Date))
EndDate = CDate(InputBox("Ending Date:", "Report Parameters", Date))
Me.FilterOn = True
Me.Filter = "Date Between #" & BegDate & "# and #" & EndDate & "#"
Let me know if this works.