Importing and Appending Multiple Excel workbook data into Acces Ta

  • Thread starter Thread starter snowiii
  • Start date Start date
S

snowiii

Every day there are a various number of excel workbooks which our area
receives via e-mail...In each is a worksheet that has the same format and
contains data about a request for service from other departments. We receive
200+ requests daily...Right now the attachment is opened and printed, then
data is then typed into an access db for reporting and inventory tracking
purposes.

1. Is there a way to write some vb code into the access db to import as many
workbooks data as needed? They would all reside in one directory and will
each have a unique key to prevent duplicate records...
2. (I'm looking into creating a rule in Outlook to save the attachment to a
specified directory...Any thoughts on this?)
3. The other thing I'd like to know is how to identify those files that fail
the import due to the file already residing in the access db....

I am familiar with vb, but am not a power programmer by any measure...

Thanks!
 
1. Yes, you can use the TransferSpreadsheet method to import excel
worksheets. If you want to import multiple excel files from a specific
directory, use the Dir function to loop through all the files in the
directory and import them.

2. Not a bad idea

3. During the import process identified in 1., when you determine a file
has already been imported, take whateve action is appropriate. Maybe a table
that you can append to that will list the failed imports that you can then
use to create a report.
 
Thanks...Do you have any sample vb code that could accomplish # 1 & 3...As I
mentioned I know vb when i see it but have no idea where to start....

Thank you!
 
Hi Snowiii,

Try this code to do the import.- replace the Const (constant declarations)
with the appropriate information for your circumstances.

myPathPattern should be the drive and folder where the excel sheets are -
along with \*.xls or \*.csv as appropriate.
myTable should be the name of a temporary table where you will be putting
the spreadsheet data

In the docmd statement, the acSpreadsheetTypeExcel4 is a safe value
(usually) for *.xls files but you may need to change that constant if the
file is a *.csv or if you have problems with the import.

As far as handling dupes - need to know the name of the temp table, the
permanent table, and what column or column(s) we can rely on to provide a
unique value for each row of information. The processing would be to append
the temp table dupes to another table for review, then delete the dupes,
then append the new/unique records to the permanent table.

Public Function ImportSheets() As Boolean


Const myPathPattern As String = "C:\myfolder\*.xls"
Const myTable As String = "NameofTable"

Dim f As String
Dim myXLS As String

f = Dir(myPathPattern)
Do While Len(f)
myXLS = Replace(myPathPattern, "*", f)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel4,
myTable, myXLS, True
f = Dir()
Loop

End Function
================================

By the way, both the Dir() function and the Replace function are useful,
additional tools that I added to my repertoire after browsing other people's
code in the the Microsoft Community Forums - adding a little twist, we can
snag a folder full of imports. If you wanted to get a little more radical
here, you could use the VBA copy to make a backup copy of the files in
another folder as you process them, then use the VBA Kill statement to
delete the processed file from the current (incoming) folder - thus emptying
out the folder as you import the files but saving a backup copy just in case
something goes wrong with the import.

To accomplish this, you would insert (replacing "C:\backupfoldername\" with
the appropriate drive and path\)

Copy myXLS "C:\backupfoldername\" & f
Kill myXLS

after the DoCmd statement and
before f=Dir()


Hope this helps!
Gordon
 
Gordon:

This is very cool....I have tried both and the kill process is a neat one...

Thanks for your reply!

Snowiii
 
Hi Anyone out there: I was wondering if you could help me.
I have 3 sets of workbooks in 47 different languages.
Workbook A has the following:
A header row that displays the following
ColA = Country
ColB = Language
ColC = Section
ColD = Phrase
ColE = Num
ColF = New
ColG = Country Specific
Then several rows following the heading row

Workbook B has the following:
2 header rows that will not be coming over
ColA = TextCode
ColB = Original Text
ColC = Translation
and rows of data following

Workbook C has the following:
one header row that will not be coming over
ColA = Text Code
ColB = Original Text
ColC = Translation

What I need:
1 Master workbook for each language (meaning, I need a master workbook/sheet
for a language to start with appending workbook a and its headings and append
workbook b and then append workbood c.

with workbook b and c, Col A would append to ColC of Master and Col c would
append to ColD of Master.

I have 47 languages with three spreadsheets each to do from now until Monday.
Is this doable. Of course I will be working on it all weekend.

Can you help me?
 
Back
Top