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

[cuddling101]

I am wanting to find out the worksheet name for a worksheet in another workbook. I have another function that does the reverse but I now can't find where I use it, in amongst 20+ spreadsheets, so this is very frustrating.

I have adapted the name to number spreadsheet, 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 -

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

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

fName = fExt_Worksheet.Name

Sheet_Name_from_Sheet_Num = fName

End Function

With thanks in anticipation

Philip
Bendigo, Victoria
Australia

 

[mumps]

Not sure if I understood correctly but try:

Sub test()
    MsgBox Sheets(Workbooks("Johnson_Project_Patriarchal_Lines.xlsm").Range("A2").Value).Name
End Sub

This assumes that the workbook is already open.

 

[cuddling101]

I am sorry but that did not work at all. I created a Sub in the calling worksheets Subroutines area and I got the message

Run-time error '438':
Object doesn't support this property or method.

A2, in the calling worksheet, is where the sheet number for the worksheet in the 'external' open workbook is stored. I am also wanting a function,+ as this call needs to occur in multiple places in the calling worksheet.

 

[Fluff]

How about

Function Sheet_Name_from_Sheet_Num(Workbook_Source As String, Worksheet_Number As Long) As String
    With Workbooks.Open(Workbook_Source)
        Sheet_Name_from_Sheet_Num = .Worksheets(Worksheet_Number).Name
    End With
End Function

 

[cuddling101]

How about

Function Sheet_Name_from_Sheet_Num(Workbook_Source As String, Worksheet_Number As Long) As String
    With Workbooks.Open(Workbook_Source)
        Sheet_Name_from_Sheet_Num = .Worksheets(Worksheet_Number).Name
    End With
End Function

Unfortunately that did not work. 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 I 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 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.

 

[Fluff]

I missed the fact that you are calling the function from a sheet and as far as I know, you cannot open a workbook with a User Defined Function.

 

[cuddling101]

Dear Fluff

Thank you for trying to help. The answer received on another board is -

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

Best regards

Philip

 

[Fluff]

Glad you got it sorted & thanks for the feedback.
However for future reference
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.