Import data from Excel - Select Only certain columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey,

I'm not sure how to do this but pretty sure it's possible. I would like to
import 1 to many excel spreadsheets into an Access database but I need to
select only certain columns in the excel files. Below is an outline of what
I'm trying to do:

1. Search a certain folder for Excel files that need to be processed - all
set with this.

2. Import 1 to many files to Processing table. This is where I'm stuck. I
have no problem importing the entire spreadsheet for each file but each
spreadsheet contains 30 plus fields and I only need 5 fields. I would like to
learn how I can query each excel file and import only the fields I need.

3. After importing the data, move the excel files from the Processing folder
to the Completed folder. Haven't started working on this yet but I think I
can figure it out. Any help though would be appreciated!

4. Next, the rest of my steps (essentially OpenQuery steps to create various
tables) are executed. I'm all set with this as well.

So far, I've written step 1 and 2 as a VBA module in Access called
Import_Excel_Files. I have some VBA experience and I believe but could be
completely wrong that I would need to use VBA & ADO to create a query to get
the data I need from the excel spreadsheets. I've never done this before so
I'm looking for a little help!

Thanks
 
Question 2
In this case, I would not import the spreadsheets. Instead, Link to them.
Now you can treat them just like tables. Write queries that append or update
the tables you want the data in. Then, just delete the link.

Question 3
Since you have linked to the spreadsheet, you have it's name. Use the VBA
Name statement to give it it's new path and name.

Question 4
If you are going to make new tables each time (Not the way I would do it),
try using the Execute Method of the CurrentDb object instead of the OpenQuery
method. You will be suprised at how much faster it runs.
Now, as to Make Table Queries. There are some issues with this method.
First, it defaults all text fields to the size specified in your
Tools-->Options. Unless otherwise specified it is 255. Allowing greatly
oversized fields slows processing. Also, when pulling in Excel data without
specified data types, Access guesses it what the data type should be. This
is not that dependable and may not get the type you want or even the same
type over multiple runs. A better approach is to use a predefined table and
put your data in that. You only need to delete all the existing data in it
before loading it from your queries.
CurrentDB.Execute("DELETE * FROM MyTableNameHere;")
 
Thanks for the response but I think I figured out a pretty good way to do this.

Basically, in a word document I created a button that executes my report
(this is somethuing similar I've done for several reports - I have 1 word doc
called monthly reports with a button for each report).

For this particular report the code (VBA) does the following steps:

1. Checks my Need to be Processed folder using the FileSystemObject to see
if any files exist that need to be processed.

2. If files exist it opens up my Access DB and executes my Create Report
macro. First, the macro deletes the existing Imported_Data table then creates
a new blank Imported_Data table using the CopyObject and a template table.
This macro then executes a module I wrote. The module using the FileSearch
objects searches the Need to be Processed folder - identifing each file in
the folder. I then loop through the files importing them using the
DoCmd.TransferSpreadsheet and setting the table name property to my table
Imported_Data. I have to import all the fields rather than just the 5 I need
but the amount of data is so small that I can live with this. Next the macro
executes several OpenQuery steps. Thanks for the info regarding the use of
this but the amount of data I have is so small that performance is a non
issue. Next, using the FileSystemObject I move all the files in the Need to
be Processed folder to a folder called Processed Source Files.

3. After the macro executes Access is closed and an excel file that serves
as a template is opened (using VBA). The file is refreshed and saved with a
new name and in the appropriate folder where we store reports.

So basically, once a month I open my monthly word doc and click a button and
this report is created - takes all of 2 minutes at the most.
 
Sounds reasonable. I would make one change. Rather than deleting a table
and using the CopyObject to create a new one, I would just delete the data
from the table:

CurrentDb.Execute("DELETE * FROM mytablename;")
 
Back
Top