RE: Import data from Excel - Select Only certain columns

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;")

"ridawg" wrote:

> 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.TransferSpread*** 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.
> --
> -ridawg
>
>
> "Klatuu" wrote:
>
> > 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 spread***, 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;")
> >
> > "ridawg" wrote:
> >
> > > 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 spread*** for each file but each
> > > spread*** 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
> > > --
> > > -ridawg
.