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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,446
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.
 

cuddling101

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
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
 

cuddling101

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,562
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,078,494
Messages
5,340,691
Members
399,390
Latest member
newexcel12

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top