VBA Code: (NEED HELP) Import files to Access & Run/Export Queries

[areyoupat]

[FONT=&quot]I’m trying to automate a task that I perform daily at work using VBA. I’ve used very basic VBA code to manipulate data in excel before, but never anything involving access. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Below, are the instructions that I’m hoping to convert to VBA code.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I would be more than grateful for any kind of help, with ANY of the steps that I’m trying to automate. And I do apologize in advance for the lengthy instructions below; I was trying to be descriptive as possible, in order to show all the steps/variables that I’m trying to incorporate into the VBA code.
[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Basic steps: Import files, Run Queries, and Export Queries.
[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Directions to import Primary File[/FONT]
[FONT=&quot]>>Import the (mmddyy)_Primary_A_patinfo.txt file into the patinfo table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo, Yes to Overwrite existing table & it’s data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>>Import the (mmddyy)_Primary_C_patinfo.txt file (Appending a copy of the records to the table patinfo) into the patinfo table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo, Yes to Overwrite existing table & it’s data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>>Import the (mmddyy)_Primary_D_patinfo.txt file (Appending a copy of the records to the table patinfo) into the patinfo table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo, Yes to Overwrite existing table & it’s data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Directions to import Update File[/FONT]
[FONT=&quot]>>Import the (mmddyy)_Update_A_patinfo.txt file into the patinfo-update table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo-update, Yes to Overwrite existing table & it’s data. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>>Import the (mmddyy)_Update_C_patinfo.txt file (Appending a copy of the records to the table patinfo-update) into the patinfo table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo-update, Yes to Overwrite existing table & it’s data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>>Import the (mmddyy)_Update_D_patinfo.txt file (Appending a copy of the records to the table patinfo-update) into the patinfo table, fixed width, select advanced, select specs, select Patinfo import spec, select Open, No Primary Key, Import To Table: patinfo-update, Yes to Overwrite existing table & it’s data.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Directions to import New business File[/FONT]
[FONT=&quot]>>Import the newbusiness(mmddyy).txt file into the newbusiness table, fixed width, select advanced, select specs, newbusiness import spec, select Open, No Primary Key, Import To Table: newbusiness, Yes to Overwrite existing table & it’s data, Close.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Directions to run Queries[/FONT]
[FONT=&quot]>>Run the Query update: patinfo-update accounts with matching patinfo, say Yes to the warning that the action query is going to modify data in a table, say Yes to the warning that you’re about to updated #### row(s) in the table.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>>Run the Query append: patinfo-update accounts without matching patinfo, say Yes to the warning that the action query is going to modify data in a table, say Yes to the warning that you’re about to updated #### row(s) in the table.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Directions to Export Queries[/FONT]
[FONT=&quot]>>Export Query newbusiness with Matching patinfo, Export>Excel, Filename: "\\my name\ClientDataProcessingFiles\client\NewBusiness\newbusiness With Matching patinfo (mmddyy).xls, save as type Excel 97-2003 (*.xls), check Export data with formatting and layout.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>> Export Query patinfo letters, Export>Excel, Filename: "\\myname\ClientDataProcessingFiles\Client\NewBusiness\patinfo letters (mmddyy).xls save as type Excel 97-2003 (*.xls), check Export data with formatting and layout, enter date into dialog box (make date as 2 days before current date) (format mm/dd/yy).[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]>> Export Query Edata Found In newbusiness, Export>Excel, Filename: "\\myname\ClientDataProcessingFiles\client\NewBusiness\Edata Found In newbusiness save as type Excel 97-2003 (*.xls), check Export data with formatting and layout.[/FONT]

 

[revans]

The best way I've found to import data automatically is to import it manually once and save the specs as an Import Spec. This is slightly different from version to version. Once you have an import spec you can use DoCmd.ImportText to import your text file. There are various ways you can set this up if you have multiple files. You can simply loop through a folder with the FileSystemObject or you can get some code off the web to instantiate a FileSelectBox (not sure the name of it) to import each file individually. If you are certain of the file's existence you could hardcode the file name--I typically do this until I've got the bugs worked out.

To run an update query, write the query out in a normal query. Save it with a name you'll remember. You can then run the update query with

Currentdb.execute "MyQueryName"

. If your query has parameters you can play with the parameters in code and run a querydef with DAO (search forums for how to do this) or you can dynamically create a sql string then run that.

dim sql as string
sql="INSERT INTO blahblahblah"
currentdb.execute sql

I've never had to export a query to Excel so I won't be much help to you except to note that this sort of question is asked a bunch on these forums so a search here should net you some info.

hth,

Rich

 

[alansidman]

I recently addressed this in another forum and posted a sample database that showed the code for importing and exporting. Look at this thread

http://www.excelforum.com/access-programming/760256-macro-code-to-delete-table-data.html

Rich's advise for importing files is the best way to get a good understanding of how it all comes together.

Alan

 

[areyoupat]

Thank you VERY much for the helpful hints! This will definitely help me get started with running tests over the weekend. Thanks again!