Solved

Batch import Excel Spreadsheets

Posted on 2004-10-28
591 Views
Last Modified: 2008-02-01
Hi,

I have a hundred or so Excel spreadsheets that are identical in structure and I'd like to batch import them into the same Access ttable with the method being: use first row as field names. Is there a method to do this?

Thanks
0
Question by:Chris_Granger
    9 Comments
     
    LVL 18

    Expert Comment

    by:Data-Man
    0
     
    LVL 9

    Expert Comment

    by:Bat17
    if all of your files are in one folder you could try

    Sub Import()
    Dim strPath As String
    Dim strFileName As String
    Dim strFileType

    strFileType = "*.xls"
    strPath = "C:\MyFiles\" ' add your path here

    strFileName = Dir(strPath & strFileType)

    While Not strFileName = ""
    Debug.Print strFileName
    DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, True
    strFileName = Dir()
    Wend
    End Sub

    Peter
    0
     

    Author Comment

    by:Chris_Granger
    Thanks guys these examples are just what I need. On a more basic level, my skills are with ASP - VB Script, PHP, JavaScript, and so on but I understand the VB code and can work with it, I'm just not sure how to implement it. As in, does this get put into a .bat file or a macro in Access or do I need Visual Studio to compile an exe, etc? Could you explain exactly where I put this code and how to trigger it. I think I have an old dev. version of Visual Studio somewhere if that's necesary. This would be very handy for me to know when I need to implement these sorts of tasks so any help appreciated...

    Many Thanks
    0
     
    LVL 9

    Expert Comment

    by:Bat17
    It goes in a module, just go to the module tab and click new.
    paste the code in, changing names a needed.

    with the cursor in the code press F5 and the code will run.

    If this is a "one off" the is no need to even save the code but if you want to keep it then make sure that you save the module with a differnt name to the sub or Access will spit its dummy out when you try to run it.

    HTH

    Peter
    0
     

    Author Comment

    by:Chris_Granger
    Cool, I'm nearly there, except it's telling me f2 field doesn't exist in database. The spreadsheet looks pretty clean but I may still need to isolate what it is importing and to what fields. Is there a way to tell it to:

    1. Import a range of cells, for example: a2 to u16?
    2. Code it so that: "row a" imports into "fieldname a", "row b" imports into "fieldname b", etc?

    cheers
    0
     
    LVL 9

    Expert Comment

    by:Bat17
    you can add the range to the end of the Transferspreadsheet command

    DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, True, "A2:U16"

    As long as the field names are the same it will match them in any order. (that was the "True" argument)
    If the fields are in the right order you can set that to false and set your range to exclue the first row of names

    DoCmd.TransferSpreadsheet acImport, , "Your Table name here", strPath & strFileName, False, "A2:U16"

    HTH

    Peter
    0
     

    Author Comment

    by:Chris_Granger
    Thats really cool! I'm using the false command but it requires my database fields be renamed to f1, f2, f3...  Is there a way to instruct it to use certain db fieldnames are? Otherwise, I'll just name all my fields in db tables as f1,f2... and then rename them to what i require after importing - not too much hassle.

    The only real clincher is that the length of the spreadsheets (U16) is variable. Is there a way to get the length of the spreadsheet so that "U16" becomes a variable: "U" & rowlength?

    Thanks
    0
     
    LVL 9

    Accepted Solution

    by:
    I dont know any way to make the length variable, but, you can use NamedRanges rather than a set range. This only helps if you can get a range in the Spreadsheets first though! I am sure that one of the XL guru's would be able to do you a code that would go through each file check the range and add a name for it though.

    I thought that it should just ignore names and put them in any field if you set it to false. From Help

    HasFieldNames  Optional Variant. Use True (–1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed.

    Each field in the spreadsheet must be of the same data type as the corresponding field in the table.
    The fields must be in the same order (unless you set the Has Field Names argument to Yes, in which case the field names in the spreadsheet must match the field names in the table).


    Peter
    0
     
    LVL 39

    Expert Comment

    by:stevbe
    dynamic excel named ranges ...

    make a name in Excel (Insert --> Name) and use the OFFSET function to determine the size ...
    my excel library is at home, I don't have an example handy ...
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Anonabox PRO Tor & VPN Router

    PRO is the most advanced way to fortify your privacy and online anonymity by layering the Tor network with VPN services. Use both together or separately, and without needing to download software onto your devices.

    This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now