Forum Discussion

PMHunt1955

Brass Contributor

Nov 22, 2019

Solved

Passing a workbook name and worksheet number to find out worksheet's name

I am wanting to find out the worksheet name for a worksheet in another workbook.

I seem to be close but all I am always getting is a #VALUE# error. I think it may be in the call but it could be in the code. Any help welcome, please.

Call reads -

=Sheet_Name_from_Sheet_Num("'[Johnson_Project_Patriarchal_Lines.xlsm]'",A2)

A2 contains a number - in the first testing line it is 1.

Function reads -

Code:

Function Sheet_Name_from_Sheet_Num(Workbook_Source As String, Worksheet_Number As Long) As String

Dim fWorkbook_Source As String
Dim fWorksheet_Number As Long

Dim fName As Long

Dim fExt_Workbook As Workbook
Dim fExt_Worksheet As Worksheet

fWorkbook_Source = Workbook_Source
fWorksheet_Number = Worksheet_Number

With Workbooks.Open(fWorkbook_Source)
     Set fExt_Worksheet = .Worksheets(fWorksheet_Number)
     fName = fExt_Worksheet.Name
End With

Sheet_Name_from_Sheet_Num = fName

End Function

The above With block is how I have gone after some input on another board. I previously had

Set fExt_Workbook = Excel.Workbooks.Open(fWorkbook_Source)
Set fExt_Worksheet = fExt_Workbook.Worksheets(fWorksheet_Number)

fName = fExt_Worksheet.Name

but that had produced, in at least one testing, a message about With block, so that is why I felt that the suggestion from an MVP was the way to go.

Unfortunately the With block suggestion did not work, either. Just to learn and test more about the With structure I tried the code without the period in front of the word Worksheets in the With code and that 'worked' but it gave me the name of the number 1 worksheet in the calling workbook, not the number 1 worksheet in the workbook that I want to look across at. That told me that the basic concept is working but that the problem is, I think, in the format of how I am passing the name of the workbook that I want to look across at.

I also tried, in the With block -

     fName = .Worksheets(fWorksheet_Number).Name

instead of the 2 line Set and Assign value to fName structure, as shown above, that latter one liner approach being more strictly adhering to the advice from the MVP, but that did not work either.

I have tried every combination of pairs of quote, double quote, and square bracket that I can think of, but none work.

I have searched the web for a simple basic statement of how to pass the name of an external workbook into a worksheet function, but I cannot find one.

With thanks in anticipation

Philip
Bendigo, Victoria
Australia

excel

Formulas and Functions

Macros and VBA

Subodh_Tiwari_sktneer

Nov 23, 2019

PMHunt1955

Sorry I misunderstood. I thought you were calling the function within the module itself.

You may tweak your function like this and let me know if that works for you...

Function Sheet_Name_from_Sheet_Num(ByVal Workbook_Source As String, ByVal Worksheet_Number As Long) As String

Dim wbSource As Workbook
Dim wsSource As Worksheet

If IsWorkbookOpened(Workbook_Source) Then
    Set wbSource = Workbooks(Workbook_Source)
Else
    Sheet_Name_from_Sheet_Num = "Workbook is not opened!"
    Exit Function
End If

If SheetExists(wbSource, Worksheet_Number) Then
    Set wsSource = wbSource.Worksheets(Worksheet_Number)
Else
    Sheet_Name_from_Sheet_Num = "Worksheet with Index " & Worksheet_Number & " was not found!"
    Exit Function
End If

Sheet_Name_from_Sheet_Num = wsSource.Name

End Function

Function IsWorkbookOpened(ByVal wbName As String) As Boolean
Dim wb As Workbook

On Error Resume Next
Set wb = Workbooks(wbName)
On Error GoTo 0

If Not wb Is Nothing Then IsWorkbookOpened = True
End Function

Function SheetExists(wb As Workbook, ByVal wsIndex As Long) As Boolean
Dim ws As Worksheet

On Error Resume Next
Set ws = wb.Worksheets(wsIndex)
On Error GoTo 0
If Not ws Is Nothing Then SheetExists = True
End Function

And then you may call this function on the worksheet like this...

=Sheet_Name_from_Sheet_Num("Johnson_Project_Patriarchal_Lines.xlsm",A2)

5 Replies

Subodh_Tiwari_sktneer
Silver Contributor
Nov 22, 2019
PMHunt1955

In the line Workbooks.Open(fWorkbook_Source), fWorkbook_Source is supposed to contain the full path of the file you are trying to open but it doesn't seem so by looking at the way you call the function.

Also, the second argument in the function expects a number but you are providing A2 as the second argument. If the cell A2 contains a number, you should either use [A2] or Range("A2").Value as the second argument.

Is the source workbook, you are dealing with, already open?
- PMHunt1955
  Brass Contributor
  Nov 22, 2019
  Subodh_Tiwari_sktneer
  
  Dear Subodh
  
  Thank you for contributing.
  
  From what you are saying, I have made errors in the calling for both parameters.
  
  First Parameter:
  
  I am trying to look across from a worksheet in one open workbook to a worksheet in another open workbook. The name of the workbook that I wish to look across at is -
  
  Johnson_Project_Patriarchal_Lines.xlsm
  
  You say that I need to specify the full path of the workbook file that I am trying to open.
  
  My questions from that comment are therefore -
  
  How do I obtain the full path of an open workbook?
  
  Where do I do this obtaining of the full path, i.e. in the call statement or in the function?
  
  What is the correct punctuation to be placed around the file name, within this statement to obtain the full path?
  Second parameter
  
  The cell A2, in the calling worksheet, contains a number, e.g. 1. I have never heard of this need to enclose the using of a cell reference in a function call within square brackets, or a range statement. I have always just referred to a cell in a call, given it a field name within the brackets of the function line and then duly used that field name within the function. Is it because I am using these rather 'special' Workbook open statements etcetera, in the function, that I have to use this, what for me, is an unusual approach to passing the contents of a cell into a function?
  
  Just as an example, and it is quite a long call -
  
  =Surname_for_Letter(A273,(IF((ISERROR((FIND(" ",A273)))),(IF((A273=""),""," ")),((RIGHT(A273,LEN(A273)-FIND("*",SUBSTITUTE(A273," ","*",LEN(A273)-LEN(SUBSTITUTE(A273," ",""))))))))),(IF((ISERROR((FIND(" ",A273)))),0,(FIND(" ",A273)))),(IF((ISERROR(FIND(" née ",A273))),0,(FIND(" née ",A273)))))
  
  Here I am passing the contents of the cell A273 into a perfectly working function called Surname_for_Letter. Within that function's Function line I assign field names to the various parameters and then the function uses those field names accordingly.
  
  Function Surname_for_Letter(Passed_Name As String, Passed_Surname As String, _ First_Space_Pos As Long, Née_Point As Long) As String
  
  Why then in this function, which I am needing help on, are you suggesting that I need to use this square bracket or range structure and if so, precisely, please, how and where do I use this square bracket or range structure?
  
  I look forward to hearing from you again.
  - Subodh_Tiwari_sktneer
    Silver Contributor
    Nov 23, 2019
    PMHunt1955
    Sorry I misunderstood. I thought you were calling the function within the module itself.
    
    You may tweak your function like this and let me know if that works for you...
    
    Function Sheet_Name_from_Sheet_Num(ByVal Workbook_Source As String, ByVal Worksheet_Number As Long) As String Dim wbSource As Workbook Dim wsSource As Worksheet If IsWorkbookOpened(Workbook_Source) Then Set wbSource = Workbooks(Workbook_Source) Else Sheet_Name_from_Sheet_Num = "Workbook is not opened!" Exit Function End If If SheetExists(wbSource, Worksheet_Number) Then Set wsSource = wbSource.Worksheets(Worksheet_Number) Else Sheet_Name_from_Sheet_Num = "Worksheet with Index " & Worksheet_Number & " was not found!" Exit Function End If Sheet_Name_from_Sheet_Num = wsSource.Name End Function Function IsWorkbookOpened(ByVal wbName As String) As Boolean Dim wb As Workbook On Error Resume Next Set wb = Workbooks(wbName) On Error GoTo 0 If Not wb Is Nothing Then IsWorkbookOpened = True End Function Function SheetExists(wb As Workbook, ByVal wsIndex As Long) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = wb.Worksheets(wsIndex) On Error GoTo 0 If Not ws Is Nothing Then SheetExists = True End Function
    
    And then you may call this function on the worksheet like this...
    
    =Sheet_Name_from_Sheet_Num("Johnson_Project_Patriarchal_Lines.xlsm",A2)