Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011

    Unanswered: I have been given thousands of excel files and need to import them in to access

    I have been given a very large number of excel files and need to get them imported into access. The first folder alone has 900 spreadsheets.

    I have attached a screenshot called names, showing some file names of the 900 spreadsheets.

    The sheets I have looked at have headers and the data doesn't start till row 12 or so. Only one sheet per file.

    The second attachment named data, shows some of the data that is to be imported. The red cells notate columns with formulas in them that need to be changed to regular cells before importing.

    Here is what I need to happen:

    The excel files, all of them, would need to be opened and have a copy paste special to get rid of the formulas in the excel files, then saved.

    Then all of the files in a specified directory would need to be imported into a specified table in access.

    I don't know much about doing this in vb, but i am thinking that is where this needs to be done.

    Any help/code that can be offered to me on this is much appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2009
    there is relatively simple vb code out there that will import all data from multiple excel files co-located inside one defined folder. but this presumes that the excel sheets are layed out the same in terms of column count/name. importing blank/empty rows isn't an issue cause one can easily find & delete those rows later in Access.

    but if the layout of the excel sheets differ; i.e. column names....then one sort of guesses that maybe all this data should not be imported into the same table but imported into separate tables. And so there needs to be some human organization applied to the situation....but in any case if you do want them all imported into the same table but their layout varies widely - am not sure any mechanical approach is viable....
    www CahabaData com

  3. #3
    Join Date
    Jul 2011

    They are the same

    As far as I can tell, all of the files are the same layout.

    So, if they are the same, what do I do next?

  4. #4
    Join Date
    Oct 2009
    ok - well I trust you understand that this is going to be visual code and are comfortable working with it. If not, you might want to look for some professional assistance. You can google about for this topic of importing multiple files. Here below I have copied in some code I have on file when I was doing a project similar to yours - but this should be considered an outline and guide that you tweak as needed:


    Public Sub subImport()
    On Error GoTo Err_subImport

    Dim stDocName As String
    Dim fs As FileSearch
    Dim ifn As String
    Dim sql As String
    Dim today As String
    Dim fso As Scripting.FileSystemObject
    Dim oktogo As Boolean
    Dim specname As String
    Dim repdate As String
    Dim myfile As Scripting.TextStream
    Dim i As Long
    Dim y As Integer
    Dim ShortFn As String

    specname = "Import Specs"

    DoCmd.SetWarnings False
    oktogo = False
    ifn = CurrentProject.Path & "\Imports\"
    Set fs = Application.FileSearch
    With fs
    .LookIn = ifn

    .FileName = "*.txt"
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then

    For i = 1 To .FoundFiles.Count

    ShortFn = Right(.FoundFiles(i), Len(.FoundFiles(i)) - InStrRev(.FoundFiles(i), "\"))
    DoCmd.TransferText acImportFixed, specname, "tbl_temp_Import", .FoundFiles(i), True
    y = y + 1

    Next i
    MsgBox "Please ensure that the source file is present and try again" & vbCr _
    & "Required file location: " & vbCr & ifn, vbExclamation + vbOKOnly, "Input File Missing"

    Exit Sub
    End If
    End With

    MsgBox "Import complete. " & y & " files Imported", vbOKOnly + vbInformation, "Import Complete"

    ' Turn warning messages back on
    DoCmd.SetWarnings True

    Exit Sub

    MsgBox Err.Description
    Resume Exit_subImport

    End Sub


    Note: In the DoCmd.TransferText line the 'True' at the end which tells the top line is field names. Change this to false if that is not the case.

    Hope it helps.
    www CahabaData com

  5. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    Personally, I wouldn't use the TransferText command to pick up data from spreadsheets...

    If all the workbooks need to go into the same table, you could look at Automation, whereby Access opens a recordset based on the destination table, and then in turn interrogates each workbook, picking up the cell values (which bypasses the need to convert the formulae to values) for each row into a new record.

    It would be some work to set up, but if you have about a thousand files to import on a regular basis, it would be worth it!
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts