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

cuddling101

New Member
Joined
Feb 10, 2009
Messages
34
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 -

Code:
=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

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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Not sure if I understood correctly but try:
VBA Code:
Sub test()
    MsgBox Sheets(Workbooks("Johnson_Project_Patriarchal_Lines.xlsm").Range("A2").Value).Name
End Sub
This assumes that the workbook is already open.
 
Upvote 0
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.
 
Upvote 0
How about
VBA Code:
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
 
Upvote 0
How about
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
Dear Fluff

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

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top