Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147

    Import from Excel, using a Named range for control

    I have VBA code the loads Access tables from named ranges in a excel workbook.

    Currently I use a Choose command to control which tables/ranges are loaded. I would like to replace this with a named range (ImportTables) that has both the Access tables and the corresponding Excel range.

    For Counter = 1 To TotalCount 'loop through at lest once (for..next loops) do loops,
    CellRange = Choose(Counter, _
    "E_tblTOEs", "E_tblRanks", "E_tblEqpts", _
    "E_tblPers", "E_tblSubElmts", "E_tblElmtEqpts", _
    "E_tblElmtPers")
    TableName = CellRange
    DoCmd.TransferSpreadsheet acImport, ExcelType, TableName, ImportFile, FieldNames, CellRange
    Next Counter

    Note: I am teaching myself SQL, but trying to keep my data scope small (max 40 records). These 7 tables are part of 40+ tables with over 1000 main records which have swapped me before.



    The Access Table name and the Excel Name range are currently the same. This can be a problem as the naming conventions are not the same.

    Thank you for looking
    Western Neil

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you put those into a table you can open a recordset on it. Within a loop of that recordset:

    DoCmd.TransferSpreadsheet acImport, ExcelType, rs!TableNameField, ImportFile, FieldNames, rs!CellRangeField
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Pretty sure ranges can be at the workbook level or sheet level. Something more dynamic would be to iterate over the ranges collection of a workbook or worksheet, as appropriate. However, I'm speculating, based on Range being a valid Excel object and that it lends itself to being a member of a Ranges collection. I've no proof of that, but if this were my task, it's how I would approach it so that I don't have to define ranges in Excel and in Access too.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    So what are you asking for?
    You will need a table that associates range names with appropriate table name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I'm not sure what you are saying, but Access doesn't have ranges (that I know of), in this case it's a table that is named the same as the Excel range being imported.

    I use a lot of named ranges in Excel to make my lookups more understandable, most of them have no connection to Access tables. Yes you are correct about scope but that doesn't matter as I only use Workbook level normally.

    So if ImportTables is two columns by 7 rows how do I load that into an Array (of sorts)?

  6. #6
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Looking for something that would feed
    TableName = ImportTables (Counter, 1)
    CellRange
    = ImportTables (Counter, 2)

  7. #7
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Not understanding the bangs(!), part of your style or a command? If rs!TableNameField is a array will that cause it loop? (Wow)

    That is my question, how to load the named range into a Array (recordset ?)

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I assumed by now you'd used a recordset. Here's my template code:

    Code:
      Dim strSQL  As String
      Dim db      As DAO.Database
      Dim rs      As DAO.Recordset
    
      Set db = CurrentDb()
      
      strSQL = "SELECT ..."
      Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    
      Do While Not rs.EOF
        'do what you need to do here
        rs.MoveNext
      Loop
    
    
      rs.Close
      set rs = nothing
      set db = nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I think I understand, and if not I need to read up on recordsets.
    Will
    DoCmd.TransferSpreadsheet acImport, ExcelType, rs!, ImportFile, FieldNames, ImportTables
    work for the import inserted before the While command? Again I have reading to do.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't understand - why not iterate over the named ranges? If their scope is the sheet, this will list the sheet then the named ranges in the sheet, for each sheet.
    Code:
    Sub IterateOverRanges()
    Dim nam As Name
    Dim sht As Worksheet
    
    For Each sht In ActiveWorkbook.Sheets
        Debug.Print sht.Name
        For Each nam In sht.Names
            Debug.Print nam.Name
        Next
    Next
    
    End Sub
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    I don't need the all the named ranges, I need the data is some of the names ranges as listed in ImportTables named range.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Western_Neil View Post
    I think I understand, and if not I need to read up on recordsets.
    Will
    DoCmd.TransferSpreadsheet acImport, ExcelType, rs!, ImportFile, FieldNames, ImportTables
    work for the import inserted before the While command? Again I have reading to do.
    The red bits in my example intended for you to use the table and range names from your table. "rs!" refers to the recordset, the bit after a field name in that recordset.

    Quote Originally Posted by pbaldy View Post
    DoCmd.TransferSpreadsheet acImport, ExcelType, rs!TableNameField, ImportFile, FieldNames, rs!CellRangeField
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, my apologies, both for not getting the drift. Just to be clear, I was saying if you add a range or change it's name, the table values are incorrect so why store the named range when Access can get it from Excel?

    The answer probably is that I overlooked that you only want to feed range name(s) to one or more TransferSpreadSheet calls using the simplest approach. You don't want to have to open a workbook (hidden or otherwise) to get those names. My thinking was around that no matter which workbook you'd want to run such code on, it would grab the range names that you would then feed to the transfer method, but it now seems that you only want some of them.

    lf nothing else, maybe the idea will prove useful some other day. Again, sorry for the intrusion.

  14. #14
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Micron, No intrusion done. I always listen and try to understand a master. Ya sometimes they mutter but the knowledge is there.

  15. #15
    Western_Neil is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2016
    Location
    Calgary, AB
    Posts
    147
    Paul and everyone

    Got this to work for myself, and Thanks to Paul and for everyone for the hints.
    Code:
    Function ImportExcelFiles()  
      ' Replace with the actual path and filename
        Dim FilePath As String, ImportFile As String
        FilePath = "E:\Data\Documents"
        ImportFile = FilePath & "\" & "TableData.xlsm"
    
    
      ' Replace with the correct Spreadsheet type
        Dim ExcelType As Integer
        ExcelType = 10  ' "acSpreadsheetTypeExcel8"
    
    
      ' Change this to reflect first row in EXCEL worksheet having field names or NOT
        Dim FieldNames As Boolean:   FieldNames = True
    '    Dim FieldNames As Boolean:  FieldNames = False
    
    
      'Get the cell ranges to appended to the Access Tables
        Dim OpenDB     As DAO.Database
        Dim rstImports As DAO.Recordset
        Set OpenDB = CurrentDB()
        Dim CellRange As String:  CellRange = "ImportTables"  'The Named cell range holding the Range/Tables
        Dim TableName As String:  TableName = "ImportTables"  'The Access table to be imported into (assumed empty)
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE * FROM " & TableName             'Empty the access table w/checking
        DoCmd.SetWarnings True
    
    
        DoCmd.TransferSpreadsheet acImport, ExcelType, CellRange, ImportFile, FieldNames, TableName
        Set rstImports = OpenDB.OpenRecordset(CellRange)      'Load that data into a Recordset
    
    
    '   For each record in the data set Import the range into the given Table
        Do While Not rstImports.EOF
            CellRange = rstImports("Range")
            TableName = rstImports("Table")
            DoCmd.TransferSpreadsheet acImport, ExcelType, TableName, ImportFile, FieldNames, CellRange
            rstImports.MoveNext
        Loop
        
    End Function 'ImportExcelFiles
    So this requires in the spreadsheet "TableData" to have a named range "ImportTables" and a matching Access Table "ImportTables" with two fields, Range and Table.
    It will handle as many ranges/tables in the "ImportTables" range that you have, but note you must load them in an order that respects the dependencies within the tables

    As is with no warranties

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 08-26-2018, 07:54 PM
  2. Replies: 3
    Last Post: 08-15-2012, 04:15 PM
  3. Import excel worksheet by range with non identical headers
    By snoopy2003 in forum Import/Export Data
    Replies: 1
    Last Post: 03-14-2011, 01:04 AM
  4. Imported Named Range
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 03-04-2011, 10:03 AM
  5. How to import named range from excel
    By timpepu in forum Import/Export Data
    Replies: 1
    Last Post: 02-19-2010, 11:26 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