Automating Excel Data Import

[Sydious]

I have a 3 Excell workbooks that have 2 worksheets I import into an access
databse. It requires me to go through the import wizard 6 times to import all
the data.

The Workbook file name is always the same thing with the date at the end.
Can I automate this?

Even if it is having to press 6 buttons to import each sheet. This would be
better then going through the wizard each time.

I would love to avoid VB if possible. I would rather not try to work with
that.

Is there a simple way to automate this??

 

[AndyB via AccessMonster.com]

Hi There,

Rather than import your spreadsheets, what you should be able to do is link
them to the database as linked tables.

Andy

 

[Sydious]

A new spreadsheet file is saved each day. How can I link them if they aren't
created yet. Wouldn't I have to link them each time one is created?

Sorry for the ignorance. I never linked a sheet to a table before.

 

[AndyB via AccessMonster.com]

Sorry, Lets start again & try to determine exactly what you are trying to
achieve.

As I now understand it. On a daily basis, you import three differently named
excel workbooks into your database, which contain two worksheets which always
bear the same names.

Do you import the data into new tables or append the data to existing tables?

If new tables, what do you name them? and do you retain the tables imported
from the previous day(s)?

That'll do to get us started.

Andy

A new spreadsheet file is saved each day. How can I link them if they aren't
created yet. Wouldn't I have to link them each time one is created?

Sorry for the ignorance. I never linked a sheet to a table before.

Hi There,

[quoted text clipped - 17 lines]

 

[Sydious]

Ok let me try to explain better.

I have 3 Excel workbooks that get filled out each day. They are saved as
single files named:
1st Shift Metrics ##/##/####
2nd Shift Metrics ##/##/####
3rd Shift Metrics ##/##/####

where ##/##/#### = what date they are for.

Each of these work books are identical. They contain 4 worksheets. 2 of
these sheets are imported out of each workboook. The Work sheet names are as
follows:
CORE Hours
OT Hours

This data is imported and appended to a table named:

LaborLog

I would like to make a button(s) that allow me to import the CORE Hours and
OT Hours worksheets out of all 3 workbooks. with a text box field that has
the date that would be added to the end of the file name.

Like I stated before, either with 1 button for each sheet import, or 1
button for all 6 sheets imports.

Sorry, Lets start again & try to determine exactly what you are trying to
achieve.

As I now understand it. On a daily basis, you import three differently named
excel workbooks into your database, which contain two worksheets which always
bear the same names.

Do you import the data into new tables or append the data to existing tables?

If new tables, what do you name them? and do you retain the tables imported
from the previous day(s)?

That'll do to get us started.

Andy

A new spreadsheet file is saved each day. How can I link them if they aren't
created yet. Wouldn't I have to link them each time one is created?

Sorry for the ignorance. I never linked a sheet to a table before.

Hi There,

[quoted text clipped - 17 lines]

Is there a simple way to automate this??

 

[gllincoln]

Hi Sydious,

Seems to me - if you have different sheets with different names each day -
then it might be simpler to change the filenames than to get Access to
figure out the new files names each day, without using code. You said that
the workbooks have the same name except for the date? 3 of them each day?

I've dealt with similar situations this way.

You can create a macro to run the batch file, then have it step thru the
transferspreadsheet imports.

Create a folder called incoming - that is where people send the new
spreadsheets.

under incoming we create a folder named work

\incoming\work

under work we create a folder called processed

\incoming\work\processed

Might be able to use a DOS batch file to do most of the dirty work.

something like this:

myProcessFiles.bat

@echo off
del /Q \work\*.*
copy \incoming\*.* \incoming\work\processed\*.*

copy \incoming\myfileone*.xls \work\myfileone.xls
copy \incoming\myfiletwo*.xls \work\myfiletwo.xls
copy \incoming\myfilethree*.xls \work\myfilethree.xls

del /Q \incoming\*.*


This batch file will give you a chance of recovering from oop's by saving a
copy of each file untouched to the processed folder before it starts slicing
and dicing. If you move the files from process back to incoming, you can
attempt a 'do over'. If you need to avail yourself of this Mulligan, move
the files - don't just copy them or you will get prompts about overwriting
the files in the processed folder.

Hope this helps,
Gordon

 

[AndyB via AccessMonster.com]

That's better, Now we can see what we're looking at.

To make this completely automated without some complex VBA coding (and I'm
not sure that's possible without researching) is virtually impossible.

The difficulty arises because of the daily change of the file names.

The simple solution would be to either copy and rename or temporarily rename
your excel files to
1st Shift Metrics.xls, 2nd Shift Metrics.xls & 3rd Shift Metrics.xls.

The required worksheets within each workbook could then be linked to your
database.

Then create 3 append queries to copy the data in the linked tables to your
main table.

Create a Macro to run the 3 queries.

Run the Macro on the OnClick property of a command button.

This works - Just tried it & No VBA.

Daily you just replace your 3 excel files (renamed), Then open your database
and run the macro.

If you need any further pointers on setting this up - give me a shout.

Andy

Ok let me try to explain better.

I have 3 Excel workbooks that get filled out each day. They are saved as
single files named:
1st Shift Metrics ##/##/####
2nd Shift Metrics ##/##/####
3rd Shift Metrics ##/##/####

where ##/##/#### = what date they are for.

Each of these work books are identical. They contain 4 worksheets. 2 of
these sheets are imported out of each workboook. The Work sheet names are as
follows:
CORE Hours
OT Hours

This data is imported and appended to a table named:

LaborLog

I would like to make a button(s) that allow me to import the CORE Hours and
OT Hours worksheets out of all 3 workbooks. with a text box field that has
the date that would be added to the end of the file name.

Like I stated before, either with 1 button for each sheet import, or 1
button for all 6 sheets imports.

Sorry, Lets start again & try to determine exactly what you are trying to
achieve.

[quoted text clipped - 22 lines]

 

[Sydious]

OK. What if I were to use VBA? I'm not VBA ignorent, actully I have written
some apps using VB6 in the past, but I don't have a ton of knowledge to draw
from. I am looking for an easy way to do this.
I figure the file name just needs to be built into a string and use that
string to call the files to import. Not sure how I would reference the form's
txtFileDate to add it to the string.