[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Importing and appending excel worksheets into Access

Posted on 1999-11-16
13
Medium Priority
?
416 Views
Last Modified: 2008-02-20
Hi,

I'm working on a project which requires me to import a few hundred Excel worksheets into one Access table. Is there any was to automate this task, rather than using the import wizard each time?

The excel worksheets has approx. 30 fields (columns) each and are named tmprecs01-300, tmprecs02-300, tmprecs03-300, tmprecs04-300..... tmprecs-254-300.

Is this possible?   Thanks

msawill.
0
Comment
Question by:msawill
  • 4
  • 3
  • 2
  • +4
13 Comments
 
LVL 1

Expert Comment

by:tkuppinen
ID: 2211621
If you know the names of the spreadsheets you can use the TransferSpreadsheet method to import these sheets.  This is done through code and if you need an example I can post one for you.
0
 

Expert Comment

by:PShields
ID: 2211775
Does the data change in the excel worksheets so you will have to import them again at a later date.  how about linking to them and then use a union query or append query to get all of the worksheets together.  If you use an union query any changes to excel would show up in the union query.
Phil
0
 

Expert Comment

by:HANDeMAN
ID: 2212026
You will need to create a function that does the following:

1. Determine the path where the spreadsheets are stored.  Obvious implications are: unique file names, where to find the path name.
2. Find the first file using the DIR$() function.
3. Create a loop that uses the DIR() function to find the next spreadsheet file. Exit if "".
4. Use the TransferSpreadsheet Method to import the data from the spreadsheet into a temporary table for data manipulation and logic processing or directly into an existing table.  Implications: unique values in the destination table, translations, etc.

Code available.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 4

Expert Comment

by:abaldwin
ID: 2212333
A method that has saved me a lot of time and Headaches is similar to the post from HANDeMAN.  While looping through the directory where the files are stored (or moving them to a temporary folder insuring you only import the excel files you want), take the time in the code process to open the excel sheets and process each line or record, applying validation rules that exist in your database and checking for duplicates, blank lines etc.  Although this seems like a long process it sounds like you are in the middle of something that may repeat in a month or two.  Writing a good validating app up front will save you time and money later.
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2212605
albadwin:  Welcome to EE.

Watching your first start in EE, at least in the Access topic, apparently you are pushing the wrong button when you make a comment.  You are pushing 'Answer' where you should be pushing 'Comment'.

Be careful in the future please.  All these questions that you responded to improperly have been taken out of the 'unanswered' area and so only the more knowledgeable experts will be able to respond.

Also there is the embarassment for the Asker to now go through and 'Reject' your answers so the question can be opened up again.

Jim
0
 

Author Comment

by:msawill
ID: 2213110
Thanks so much for responding to my question. here's some aditional info..

This is a one-time process that has to be done, and the worksheets have unique names( a sequential number - as part of the file name hence..)

 tmprecs01-300.xls
 tmprecs02-300.xls
 tmprecs03-300.xls
 tmprecs04-300.xls
 tmprecs05-300.xls
 ...
 ...
 tmprecs254-300  

Can you please send the vb code to accomplish this task??

Thanks.

msawill
0
 
LVL 7

Accepted Solution

by:
JimMorgan earned 400 total points
ID: 2213180
Is the Access table already created?

If so then you can use a simple DO/LOOP to find all the files and import them.

If all of the files are sequentially numbered do this:

Sub ImportExcel()
    Dim strFileName as String
    Dim strExt as String
    Dim intCount as Integer
    Dim strTable as String
    Dim strPath as String
    strExcelExt = "-300.XLS"
    strTable = "name of access table to import to"
    strPath = "path to drive and directory holding excel files"
    intCount = 0
    Do
       intCount = intCount + 1
       If Len(Dir$(strPath & strFileName & Cstr(intCount) & strExt)) Then
          DoCmd.TransferSpreadsheet acImport,,strTable, strPath & strFileName & Cstr(intCount) & strExt
       Else
          Exit Do
       End If
    Loop

If they run a range but a number might be missing, use this:

Sub ImportExcel()
    Dim strFileName as String
    Dim strExt as String
    Dim intCount as Integer
    Dim intEnd as Integer
    Dim strTable as String
    Dim strPath as String
    strExcelExt = "-300.XLS"
    strTable = "name of access table to import to"
    strPath = "path to drive and directory holding excel files"
    intCount = 0  '(or starting # - 1)
    intEnd = 255  '(or ending # + 1)
    Do
       intCount = intCount + 1
       If intCount = intEnd Then Exit Loop
       If Len(Dir$(strPath & strFileName & Cstr(intCount) & strExt)) Then
          DoCmd.TransferSpreadsheet acImport,,strTable, strPath & strFileName & Cstr(intCount) & strExt
       End If
    Loop

You could use the DIR() to pull the next filename is the directory but I felt that sequence was important and so one of the above routines is best.  If you use the second routine and one file is not valid, the process will continue.

The first routine will stop with a missing file number.  If this happens, set intCount to the missing file number and run it again.  It will continue with the next.

Jim
0
 

Expert Comment

by:HANDeMAN
ID: 2213247
Likewise:

Add this function to a class module of a form or standard Module.

Function gsImportSS(vstrPathName As String, vstrTableName As String)
    Dim strFile As String, strPath As String
    strPath = vstrPathName & IIf(Right(vstrPathName, 1) = "\", "", "\")
    strFile = Dir$(strPath & "*.xls")
    While strFile <> ""
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, vstrTableName, strPath & strFile, True
        strFile = Dir
    Wend
    '
    '   Now that all of the data has been imported into a table (vstrtableName)in Access,
    '   run appropriate queries or methods to import/update records in the
    '   database using records in this table.
End Function

Run the function by providing the path where the spreadsheets exist and the table where you want to import the data into.  You can run the function from the Intermediate Window or from a form that provides the parameters from text boxes.

If the table doesn't exist, the table is created.  In this case, after the data is collected from all of the spreadsheets, you can provide whatever logic you wish using:
a) queries to append the records into one or more tables of the destination database.
b) logic to provide translation of data from the raw data to values to be stored.
c) reports to group and count records
d) anything else you want to do with the raw data before actually importing it into a production database (recommended).

Believe me...report on it, have it approved, then update your production database (its much safer this way!).
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2213544
I recognize the merit in your method and even though msawill said that this is a one-time situation, experience has proven that there is not such thing as a one-time situation.

Depending on the wildcard file input would mean that the directory only contains the files that you want imported and no others (will really screw up the table) and that after the files are imported that the directory be cleared so the data is not duplicated.  Also is something happens in the import process, it can not be run again without emptying the table first or duplicate records will be added.  By naming the files, you cut down the risk of importing the same file more than once.  I noticed too that you are forcing Excel to send out its field names as the first line of each workbook.

You'll notice that I left out the spreadsheet type in my code.  msawill didn't indicate what version of Excel that the data was created in.  The default (none entered) is the most simple and will handle most spreadsheets.  If there is a particular version and the data has information that is unique to that version, then the spreadsheet types to user are:

acSpreadsheetTypeExcel3 (default)
acSpreadsheetTypeExcel4
acSpreadsheetTypeExcel5
acSpreadsheetTypeExcel7
acSpreadsheetTypeExcel97

Experience has shown that when dealing with Excel tables, Excel handles dates differently than Access.  When the table is generated at the first import, the date field might not be interpreted by Access correctly which may cause errors and no data imported.  If the table is designed first, then Access will follow the datatypes in the table and not try to guess what they are.

Another problem that might be run into, if a date field is null in the spreadsheet, no data will be imported for that record into Access.  It seems a little late to do this now, but null dates should be set to something like a 0 date - 01/01/1900 12:00:00 AM.  After the data comes in then these dates should be set back to null.

Jim
0
 

Expert Comment

by:HANDeMAN
ID: 2214119
Many valid points.  And many unknown specifications.  I believe a framework of logic has been provided to begin the process of creating a solution for msawill.  The code examples above in both cases should provide a template for the solution, not THE solution.  Let the user try the methodologies to find his/her own solution.  There has to be a learning process and if questions come up, they know where to go to find the answers.  
0
 
LVL 10

Expert Comment

by:brewdog
ID: 2214126
one clarification: it sounds to me like msawill has a bunch of worksheets in one workbook rather than a bunch of different workbooks. Is that the case, msawill? If so, then you'll need to modify whatever code you choose to work with to point to a specific sheet in the workbook . . . which in this case simply means setting up a

For i = 1 to 255

Next i

structure, where you feed the name of the worksheet with the i variable.
0
 

Author Comment

by:msawill
ID: 2230192
hi,
Many thanks to to JimMorgan for and excellent solution to my problem - it worked perfectly (with some minor changes). I'm grateful also to the following ppl. who took time to respond and share their expert knowledge.

tkuppinen, PShields, HANDeMAN, abaldwin and brewdog.

Thanks again...

msawill.
0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2232050
msawill

You'er welcome.  Always glad to be of help.

Jim
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses
Course of the Month7 days, 17 hours left to enroll

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question