From Access VBA, Loop Excel WS to get Named Ranges

[ilcaa72]

I have several named ranges in EXCEL that I want to retrieve from Access using VBA. The goal is to use the procedure below to create Tables from the data in the named ranges. It works, but I have to manually place the name of the range to retrieve and Table Name. I want to program that since I have several named ranges.

So my only issue (i think) is how to loop Excel WS and return each name range from Access VBA. Thanks for any help.

Sub Transfer_Excel_NamedRanges()
Const conFILE_PATH As String = "C:\Users\User_Name\Downloads\all_industries_summary.xlsx"
Const conRANGE_NAME As String = "CashFlow"
Const conTABLE_NAME As String = "CashFlowTbl"


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, conFILE_PATH, True, conRANGE_NAME


End Sub

[ssanfu]

Welcome to the forum...

In Excel, you would use this to list range names

Sub ListrangeNames()
  Dim nm As Name
  
  For Each nm In ActiveWorkbook.Names
      Debug.Print nm.Name
  Next nm
End Sub

What you have to do is integrate this code into your Access automation to control Excel.
It is late and I don't have time tonight to work on the Access code to read the Excel range names....
Tomorrow?? Unless someone else helps tonight....

[ilcaa72]

thanks for the reply Steve,

I am familliar with Excel VBA to grab the named ranges, the issue I have is how to use Access VBA to return the names as an array or by using a loop function that gets the named ranges 1x1 from Excel from within Access VBA. Thanks for any help you can provide

[Micron]

If you can work in Steve's suggestion and test to see if it returns the names of all the ranges to Access, then maybe

Sub Transfer_Excel_NamedRanges()
Const conFILE_PATH As String = "C:\Users\User_Name\Downloads\all_industries_summary.xlsx"
Const conRANGE_NAME As String = "CashFlow"
Const conTABLE_NAME As String = "CashFlowTbl"
Dim nm As Name
  
For Each nm In ActiveWorkbook.Names
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, conTABLE_NAME, conFILE_PATH, True, nm
Next
End Sub

You'd have to make a 'connection' with the workbook first. To do that, I'd use Automation.
Another option is to put the range names into an Access table and create a recordset of range names and loop through that.

[ssanfu]

For Looping look at this:

Sub GetRangeNames(pWkshtPathName As String)
    On Error Resume Next

    '   Dim dbs As DAO.Database
    '   Dim rst As DAO.Recordset
    Dim xlx As Object, xlw As Object
    Dim nm
    Dim NR_array() As String
    Dim i As Integer   'counter for array
    Dim blnEXCEL As Boolean  'Quit Excel at the end? 

    blnEXCEL = False
    i = 0

    ' Establish an EXCEL application object
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xlx = CreateObject("Excel.Application")
        blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0

    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    '    xlx.Visible = True
    xlx.Visible = False

    ' the actual path and filename of the EXCEL file
    Set xlw = xlx.Workbooks.Open(pWkshtPathName)

    ' loop thorugh range names, placing names into an array
    For Each nm In xlw.Names
        i = i + 1
        ReDim Preserve NR_array(i)
        NR_array(i) = nm.Name
        '   Debug.Print nm.Name
    Next nm

    ' array now holds the range names

    '
    ' other code to do something with the range names goes here
    '

    ' Close the EXCEL file and clean up the EXCEL objects
    xlw.Close False   ' close the EXCEL file and don't save
    DoEvents  'make time for close to complete

    Set xlw = Nothing
    If blnEXCEL = True Then
        xlx.Quit
    End If
    Set xlx = Nothing
End Sub

Usage would be something like: (I used a button click)

Private Sub cmdRangeNames_Click()
    Call GetRangeNames("F:\Forum\Sample Class Schedule.xlsx")
End Sub