Results 1 to 4 of 4
  1. #1
    rayted is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    34

    Can my mega excel table be imported

    Hi guys,



    I have an excel sheet which is used for monitoring performance of field interviewers.

    I find it hard to read and want to import this into access and run reports for what I need. Does anyone think with my table this is possible? (look at the monitoring tab). File attached!

    Thanks so much.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, if you want to import the single months- May-Dec17,
    or even the PortalQn sheets.

    this code would import all the sheets in the workbook.(upon asking you)
    you must link 1 sheet, then create the append query,qaImportXLsheet, that will put the data into your table.

    usage:
    ImportAllSheets1File "c:\temp\Ray test interviewer spreadsheet.xls"

    Code:
    '--------------
    Public Sub ImportAllSheets1File(ByVal pvFile)
    '--------------
    Dim colShts As New Collection
    Dim sht As Excel.Worksheet
    Dim xl As Excel.Application
    Dim itm
    Dim sName As String, sTbl As String
    
    
    On Error GoTo errImp
    docmd.setwarnings false
    Set xl = CreateObject("excel.application")
    With xl
        .Workbooks.Open pvFile
        For Each sht In .Worksheets
           sName = sht.Name
           colShts.Add sName, sName
        Next
        .ActiveWorkbook.Close False
        .Quit
    End With
    Set xl = Nothing
    
    
    sTbl = "xlFile2Import"
    For Each itm In colShts
           'ask to import the sheet...not all are wanted
       If MsgBox("Import " & itm, vbQuestion + vbYesNo, "Confirm") = vbYes Then
          DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, sTbl, pvFile, True
          DoCmd.OpenQuery "qaImportXLsheet"
       End If
    Next
    
    
    DoCmd.SetWarnings True
    Exit Sub
    
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    Exit Sub
    Resume
    End Sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by rayted View Post
    I find it hard to read and want to import this into access and run reports for what I need. Does anyone think with my table this is possible? (look at the monitoring tab).
    Yes, but you will have to a lot of analysis BEFORE you import the workbook into Access. You cannot just import the worksheets into tables and expect any valid information.

    For instance, looking at the headers in sheets Q2 - Q4, column H is different than Q1 header column H. It appears that column I - N have been shifted left one column.

    Your formulas won't work. You will have to convert the formulas to queries, UDFs or calculated controls.

    Since most of the columns in the MONITORING sheet are calculated, these columns should NOT be saved in a table, but calculated in a query.
    Columns A, B, C (maybe D) would be in a table (tblInterviewer)
    Columns D, F, G, H could be in a table (tblMonitoring).
    There would be LOTs of queries.

    Sheets Q1 -Q4 could be in 1 table with an additional field for the quarter number.
    Sheets May17 - Dec17 could be in one table with an additional field for the MthYr.
    Sheets portalQ1 - portalQ4 could be in 1 table with an additional field for the portal/quarter.
    Sheets portalMay - portalDec could be in one table with an additional field for the portalMth.

    Tthe Lookups sheet could be in a table.
    The CODES sheet could be in its own table or combined with the LOOKUP table.....


    Other than those observations, you will have to figure out what you want to see/report......

  4. #4
    rayted is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    34

    thank you

    thanks - not super familiar with codes etc. but will try this! thanks for your help

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

Similar Threads

  1. Set a table column to NULL after imported to excel
    By Thom2467 in forum Import/Export Data
    Replies: 7
    Last Post: 09-27-2017, 02:57 PM
  2. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  3. Replies: 4
    Last Post: 11-27-2014, 10:20 AM
  4. Replies: 1
    Last Post: 06-11-2014, 11:38 AM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

Tags for this Thread

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