Full Version: Get Named Range Value From An Excel Tab
UtterAccess Forums > Microsoft® Access > Access Forms
Using the code below I'm able to get the names of tabs from an Excle spreadsheet which I'm importing into Access. How can I now get the range names within a tab if I select the tab name from the list I generate in a list box?
Sub GetXLSShtNames(sXLSFile As String)
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim ws As Object
On Error GoTo Error_Handler
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False 'Control whether or not Excel should be visible to the user or not.
    Set xlBook = xlApp.Workbooks.Open(sXLSFile) 'Open the workbook
    For Each ws In xlBook.Worksheets
        Me.LstTabNames.AddItem Item:=ws.Name
    Next ws
    xlBook.Close False 'Close the workbook without saving any changes
    xlApp.Quit 'Close the instance of Excel we create
    On Error Resume Next
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Sub
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: GetXLSShtNames" & vbCrLf & "Error"
'Description: " & Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub
For a start, in your code, it looks like you populate the list box and then close the excel file down. Not too sure if you want to have to open the excel file again to extract the range names, it seems like a bit of an overhead. I would keep the file open and only close it when you are finished with all the listing.
o list the range names in a worksheet you could use something like:
Public Sub ListRangeNames(ByRef ws As Worksheet)
    Dim varNames As Variant
    For Each varNames In ws.Names
        Me.LstTabNames.AddItem Item:=varNames.Name
    Next y
End Sub
Ooopsy, change that "Next y" bit to "Next varNames"!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.