Results 1 to 4 of 4
  1. #1
    gloworm is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2011

    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.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    There may be an easier way to do this but this is what I tested and it worked:

    1. Create a table called IMPORTS in your access database
    2. Create columns in that table that are labeled A through O
    3. Set the data type of column A to date/time
    5. Set the data type of all other columns to number (double is probably what you want for all of them but I don't know)

    I created three test files each having one row of data on row 12, each having data exactly as wide as you show in your example spreadsheet.

    I put these files in c:\ and incremented the date on each file so I could determine if the correct records were being appended.

    I then ran this code:

    Dim fs
    Dim fs2
    Dim SourceFolder
    Dim sSourceDir As String
    Dim CurrFile
    Rem the Excel Application
    Dim objExcel
    Rem the path to the excel file
    'Dim excelPath
    Rem how many worksheets are in the current excel file
    Dim worksheetCount
    Dim counter
    Rem the worksheet we are currently getting data from
    Dim currentWorkSheet
    Rem the number of columns in the current worksheet that have data in them
    Dim usedColumnsCount
    Rem the number of rows in the current worksheet that have data in them
    Dim usedRowsCount
    Dim row
    Dim column
    Rem the topmost row in the current worksheet that has data in it
    Dim top
    Rem the leftmost row in the current worksheet that has data in it
    Dim leftct
    Dim Cells
    Rem the current row and column of the current worksheet we are reading
    Dim curCol
    Dim curRow
    Rem the value of the current row and column of the current worksheet we are reading
    Dim word
    Dim sSQL As String
    Dim db As Database
    sSourceDir = "c:"
    Set fs = CreateObject("scripting.filesystemobject")
    Set SourceFolder = fs.getfolder("c:")
    Set db = CurrentDb
    For Each CurrFile In SourceFolder.files
        sFileName = CurrFile.Name
        sExcelFile = sSourceDir & "\" & sFileName
    If InStr(sFileName, ".xls") Then
    Debug.Print "Reading Data from " & sExcelFile
    Rem Create an invisible version of Excel
    Set objExcel = CreateObject("Excel.Application")
    Rem don't display any messages about documents needing to be converted
    Rem from  old Excel file formats
    objExcel.DisplayAlerts = 0
    Rem open the excel document as read-only
    Rem open (path, confirmconversions, readonly)
    objExcel.Workbooks.Open sExcelFile, False, True
    Rem How many worksheets are in this Excel documents
    worksheetCount = objExcel.worksheets.Count
    Debug.Print "We have " & worksheetCount & " worksheets"
    Rem Loop through each worksheet
    For counter = 1 To worksheetCount
        Debug.Print "-----------------------------------------------"
        Debug.Print "Reading data from worksheet " & counter & vbCrLf
        Set currentWorkSheet = objExcel.ActiveWorkbook.worksheets(counter)
        Rem how many columns are used in the current worksheet
        usedColumnsCount = currentWorkSheet.UsedRange.Columns.Count
        Rem how many rows are used in the current worksheet
        usedRowsCount = currentWorkSheet.UsedRange.Rows.Count
        Rem What is the topmost row in the spreadsheet that has data in it
        top = currentWorkSheet.UsedRange.row
        Rem What is the leftmost column in the spreadsheet that has data in it
        leftct = currentWorkSheet.UsedRange.column
        Set Cells = currentWorkSheet.Cells
        Rem Loop through each row in the worksheet
        For row = 0 To (usedRowsCount - 1)
        Rem Loop through each column in the worksheet
            curRow = row + top
            curCol = column + leftct
            If Cells(curRow, 1) <> "" Then
                sSQL = "INSERT INTO Imports (A,B,C,D,E,F,G,H,I,J,K,L,M,N,O) VALUES ("
                For column = 0 To usedColumnsCount - 1
                    Rem only look at rows that are in the "used" range
                    curRow = row + top
                    Rem only look at columns that are in the "used" range
                    curCol = column + leftct
                    Rem get the value/word that is in the cell
                    word = Cells(curRow, curCol).Value
                    Rem display the column on the screen
                    If curCol = 1 Then
                        sSQL = sSQL & "#" & word & "#,"
                        sSQL = sSQL & word & ", "
                    End If
                    'Debug.Print "CurRow " & curRow & " CurCol " & curCol & " " & (word)
                sSQL = left(sSQL, Len(sSQL) - 2) & ")"
                Debug.Print sSQL
                db.Execute sSQL
            End If
        Rem We are done with the current worksheet, release the memory
        Set currentWorkSheet = Nothing
    End If
    Set currentWorkSheet = Nothing
    Rem We are done with the Excel object, release it from memory
    Set objExcel = Nothing
    End Sub
    I used the code on this site for cycling through the excel file:

    I just modified it to fit your need.

    The code assumes you may have data on all worksheets in the excel file
    The code assumes that ONLY records with something in column 1 will need to be appended.
    The code assumes that the ONLY DATA you need to append are in columns where column 1 is not blank.
    If you need to add things like the original filename or any of the data that is in rows that DO NOT have anything in the first column you will have to program for that as well.

  3. #3
    vennies83 is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2011
    How would you modify this for other spreadsheets that do not have numbers in every column? I just want to use this as an import tool.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Don't piggy back on other people's threads please start a new one and, where necessary, provide a link to the original thread.

    But to answer your question it really depends on if the blank fields are in a consistent location, if they are you can count the column you are on and ignore the nth column or create a table with the same number of columns but when you append data to them just append a 0 value or a blank string ("") to your table then basically ignore those fields in any query/report/form you use relating to that data.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 10-31-2013, 06:51 AM
  2. Replies: 1
    Last Post: 02-21-2011, 09:55 PM
  3. Cannot import excel or text files
    By donald_s in forum Access
    Replies: 2
    Last Post: 04-13-2010, 11:48 PM
  4. Import HTML files into access
    By mccrimmon in forum Programming
    Replies: 0
    Last Post: 02-15-2010, 03:40 PM
  5. How to import word and PDF files into Access
    By asaini in forum Import/Export Data
    Replies: 1
    Last Post: 09-03-2009, 11:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums