Results 1 to 3 of 3

Merging multiple excel surveys using access

  1. #1
    z723 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    1

    Merging multiple excel surveys using access

    Hi,



    I have about 40 excel files that have been sent to various departments in our company with surveys. The excel sheets are the same for every department and contain the following headings:

    Unique ID, Question, Rating Scale, Rating, Comment, POC

    When I get these items back what I would like to do is the following. Please note, that not all departments will answer all of the questions, but the questions are tied to the unique ID.

    Unique ID, Question, Rating Scale, Rating Dept 1, Comment Dept 1, POC Dept 1, Rating Dept 2, Comment Dept 2, POC Dept 2...and so on.

    After the data is in the database, I would like to export the data back into excel and come up with an overall rating average and all comments for a given question from all departments in a single cell.

    I tried using a web survey but the folks here are afraid of change and want to use excel. I really don't want to spend a week copy and pasting in and out of excel.

    Any help is appreciated.

    Z

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,363
    take an existing file and save it to a generic name like "c:\data\file2import.xls"
    attach this file as an external table
    build a query to append the excel table to your data table.

    the code below will scan the folder, copy the excel file to overwrite the generic one, then run the import query, repeat.

    Code:
        ''usage on button click:
    sub button_click()
       ImportAllFilesInDir  "c:\folder\"
    end sub
    
    
    Public Sub ImportAllFilesInDir(ByVal pvDir)
    Dim vFil, vTargT, vTarg
    Dim i As Integer
    Dim fso
    Dim oFolder, oFile
    Dim vOutFile
    
    On Error GoTo errImp
    If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
    vTarg = "c:\data\File2Import.xls"
    
    docmd.setwarnings false
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFolder = fso.GetFolder(pvDir)
    
    For Each oFile In oFolder.Files
        vFil = pvDir & oFile.Name
        If InStr(vFil, ".xls") > 0 Then      'ONLY DO excel FILES
               FileCopy vFil, vTarg                               '' docmd.transferspreadsheet  vFil
               docmd.openquery "qaImportXL"
        End If
    Next
    
    Set fso = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
    MsgBox "Done"
    Exit Sub
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    Exit Sub
    Resume
    End Sub

  3. #3
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,754
    Unique ID, Question, Rating Scale, Rating Dept 1, Comment Dept 1, POC Dept 1, Rating Dept 2, Comment Dept 2, POC Dept 2...and so on.
    This structure violates normalization rules.

    Unique ID, Question, Rating Scale, Rating, Comment, POC
    This is a better table structure... you might add in another field "Dept" to track which department the Excel workbook is from.

    Note: you SHOULDN'T use spaces in object names.

    ================================================== ============
    Normalization Terms and Concepts http://www.utteraccess.com/wiki/inde...s_and_Concepts
    Normalization http://www.utteraccess.com/wiki/index.php/Normalization


    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization? http://rogersaccessblog.blogspot.com...on-part-i.html
    What Is Normalization, Part II: Break it up. http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships http://rogersaccessblog.blogspot.com...on-part-v.html
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Replies: 5
    Last Post: 11-18-2016, 04:40 PM
  2. Merging multiple mdb files into one
    By Swoosh in forum Access
    Replies: 2
    Last Post: 04-04-2014, 11:01 AM
  3. Replies: 11
    Last Post: 10-15-2013, 04:59 PM
  4. Replies: 2
    Last Post: 01-02-2012, 05:46 PM
  5. Replies: 8
    Last Post: 11-04-2009, 03:22 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
  •  
Tech Forums: Microsoft Office Forums