Thread: Import from 5 Excel Workbooks into one Access Table

Hello again all...

I have 5 excel workbooks (all identical) that have a hidden worksheet called MTS_datatable. This worksheet has formulas instead of just plain values.

On a Form Button click in Access I want to iterate through the 5 workbooks and import the entire "MTS_datatable!" sheet values (they have headers) into one Access table called DataTable (this table has all the same fields except it has an ID field where it assigns each imported row a unique ID).

Also, importing Columns A:J and only down to the last row that has actual values in it - it is currently importing 100 rows where only 16 have values - because the rest have formulas, so I get 16 data rows in access table and 84 blank rows. I even tried putting in a NEW sheet called datatbl and doing an ON CLOSE event where it would do the following:

Sheets("MTS_Datatable").Range("A1:j2000").Copy

Sheets("DATATBL").Range("A1:j2000").PasteSpecial Paste:=xlValues

Now I have values only, but it still imports the full 100 rows??

Currently have the following on button click in form:

DoCmd.TransferSpreadsheet acImport, 8, "DataTable", "I:\Devprojects\MTS\mts\MTS_ENTRY1.xlsm", True, "MTS_datatable!"

I have great code that looks in "I:\Devprojects\MTS\mts" folder and lists the five excel sheets. Now I just need to figure out the next step, which is a button click that imports the worksheets in the found workbooks into the one master DataTable in access while ignoring all blanks (not really blanks they have Transition Navigation Keys (') in them).

The attached is an example of what I am importing (see the sheet "datatbl!")

See this post if you need more background on this sheet http://www.excelforum.com/excel-prog...t-on-save.html

Thanks!!

I think you can import from hidden sheets in 2007...at least, I think I tried it and it worked fine. For my need, they have to be hidden for the users who use the sheet, but when it comes time to import I guess I could go in and unhide each of the five sheets manually (or programmatically). No biggie there.

Here is what I am using code wise to list the sheets found in a certain directory in a listbox on an access form, then I have a click that imports them....this is easy when they are simple .csv files (which I am also thinking, hmmm could I just do a programmatic save-as in excel and save the hidden sheet ONLY as a .csv file in this directory, because when that happens, ONLY the non-blank rows come in...perhaps .csv is the way to go?? Just brainstorming.)

Private Sub Form_Load()

Call ListFiles("F:\factsdata\IMPORT\", , , Me.lstFileList)

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean, Optional lst As ListBox)
On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim varItem As Variant
       
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
    
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
     
    If lst Is Nothing Then
        For Each varItem In colDirList
            Debug.Print varItem
        Next
    Else
        For Each varItem In colDirList
        lst.AddItem varItem
        Next
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
End Function

Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
    bIncludeSubfolders As Boolean)
    'Build up a list of files, and then add add to this list, any additional folders
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant

    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colDirList.Add strTemp
        strTemp = Dir
    Loop

    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
End Function

Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Now on import_click() I have the following for importing .csv files (this is all code from another database of mine I am implementing for this new database).

Private Sub import_Click()

    Const strPath As String = "F:\factsdata\IMPORT\" 'Directory Path
    Dim strFile As String 'Filename for importing from
    Dim oItem As Variant 'each item selected in listbox
    Dim iCount As Integer 'how many iterations
    Dim tablenum As String 'Table name importing to
    
    iCount = 0
   

      ' Loop through the list
    If Me!lstFileList.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!lstFileList.ItemsSelected
            If iCount = 0 Then
                strFile = strFile & Me!lstFileList.ItemData(oItem)
                iCount = iCount + 1
                tablenum = Mid(strFile, 9, 6)
            Else
                strFile = strFile & Me!lstFileList.ItemData(oItem)
                tablenum = Mid(strFile, 9, 6)
                DoCmd.TransferText acImportDelimi, , tablenum, strPath & strFile, True
                iCount = iCount + 1
            End If
            strFile = ""
        Next oItem
        MsgBox Me!lstFileList.ItemsSelected.Count & " Files were Imported"
        DoCmd.SetWarnings (warningsoff)

        'Logging of import function
        mysql = "UPDATE UserLogtbl SET UserLogtbl.imported = -1, dateimported = Now()"
        mysql = mysql + " WHERE ((UserLogtbl.LogID) = [Forms]![Main Menu Switchboard]![logid])"
        DoCmd.RunSQL mysql
        DoCmd.SetWarnings (warningson)
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If

This will probably do everything I need - it works fine for my old dbase except:
1. It imports into separate tables
2. I need help with getting the worksheet from Excel into a .csv file or somehow just listing the workbooks and then importing the ONE hidden sheet without the blank lines (preferred).

Have you had a look here?

It seems you posted the same thing twice? I answered on your other thread - my suggestion will import into a single table. Does this do what you need?

Thanks Kyle123 ...

I didnt mean to post it twice in two different areas, it wasnt getting any looks in the access forum for awhile and I started to wonder if it wasnt more of an Excel question since the problem I am really having is in Excel.

http://www.excelforum.com/excel-prog...ess-table.html

To answer a question of yours, I am doing it in excel because the front end users are comfortable with excel and I want to make it as easy as possible for them. I love Access and would have made all in access, but with 5 different people all over the state trying to put information into it, access wouldnt work through our company intranet so I would have to go with SQL server and VB Front End which was way too much for the simple data we needed.

*I am looking at your code now, perhaps it will work!!! Fingers crossed, thanks so far to everyone taking the time to help me on this

No problem

Done the Excel/Access set up loads in the past it's handy and quick, though I always went down the route of having Excel insert directly into Access rather than pulling the data out with Access. In the end I moved everything to SQL server and ASP.Net and never looked back!!

Yeah just completed a project using SQL server and ASP.Net and it worked okay, the problem is speed issues and it with a slow infrastructure I am getting tired of complaints about refresh times on my AJAX updatepanels and having to jump through IT security hoops. So for this little excercise decided to keep it simple and stick with an excel sheet for each group in the state and since we only need to report once a year, I would just have an access dbase on my desktop that imported the data on a button click once a year.