Names and number of Sheets in external workbooks

[mzpedersen]

Our shipping department creates an Excel workbook per job/project. Each job has a varying number of shipments. Shipping will add a new sheet with the details for every shipment - everytime a shipment goes out.

The naming of the sheets varies and the number of sheets varies as the job progresses.

How can I from another Excel workbook extract the names of the sheets and the number of sheets (shipments)?

Something like

For i:=0 to ('[Job2122.xlsx]NoOfSheets'!)-1 do begin

Value := '[Job2122.xlsx]Sheet'!$C$1 (** for example to extract and create a list of shipping dates **)

End

In other words indirect addressing of a specific cell for an unknown number of sheets with unknown names, unknown from an external point of view?

Thanks in advance,

mzpedersen

 

[Wookiee]

I put together this macro and it worked for me. It opens a read-only copy of the file from which you want to capture data. It cycles through all worksheets (including hidden or very hidden sheets). It then pops up 2 message boxes. The first simply states how many sheets are contained in the file. The second breaks it down with much greater detail. It lists all worksheets and displays the contents of Cell C1 for each.

Sub Open_And_Capture_SheetNames()

'Declare Variables
Dim wkbFile As Workbook, strFileName As String, intCount As Integer
Dim intLoop As Integer, strMsg As String

'Set FileName
strFileName = "Macro Test.xlsx"

'Open File
Set wkbFile = Workbooks.Open(Filename:="C:\Your Chosen Path\" & strFileName, _
    ReadOnly:=True)

'Capture Sheet Data
With wkbFile
    intCount = .Sheets.Count
    For intLoop = 1 To intCount
        strMsg = strMsg & Sheets(intLoop).Name & ":  " & _
        Sheets(intLoop).Range("C1").Value & vbNewLine & vbNewLine
    Next intLoop
End With

'Display Messages Pertaining To Specific File
MsgBox strFileName & " has " & intCount & " worksheets.", vbOKOnly
MsgBox strMsg, vbOKOnly, "Job Summary For " & strFileName

'Close Source File
wkbFile.Close SaveChanges:=False
Set wkbFile = Nothing

End Sub

I hope this helps.

 

[Wookiee]

Sometimes it seems I just can't stop re-editing my work. Anyways, it occurred to me that you'll probably be opening several different files, so I revised the code to automatically open a file list from the root directory you choose (just update the strPath constant). Once you choose the file, it will perform the same functions as my original code.

Sub Open_And_Capture_SheetNames()
        
'Declare Variables
Dim strOriginalDirectory As String, strSource As String
Dim strFileName As String, intCount As Integer
Dim intLoop As Integer, strMsg As String, wkbFile As Workbook, varFile

'Set Variable Values
Const strPath As String = "D:\Your Root Path\"
Const strFilters As String = "Excel Files (*.xl*),*.xl*,"

'Disable Alerts & Updates
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Determine Current Default Location for Opening Files
strOriginalDirectory = Application.DefaultFilePath

'Force Excel to Look for Files in Specified Folder
ChDrive "D"
ChDir strPath

'Prompt User to Choose File
varFile = Application.GetOpenFilename(strFilters, 1, MultiSelect:=False)
strSource = varFile
strFileName = Dir(strSource)
Set wkbFile = Workbooks.Open(strSource, ReadOnly:=True)

'Open File And Capture Sheet Data
With wkbFile
    intCount = .Sheets.Count
    For intLoop = 1 To intCount
        strMsg = strMsg & Sheets(intLoop).Name & ":  " & _
        Sheets(intLoop).Range("C1").Value & vbNewLine & vbNewLine
    Next intLoop
End With

'Display Messages Pertaining To Specific File
MsgBox strFileName & " has " & intCount & " worksheets.", vbOKOnly
MsgBox strMsg, vbOKOnly, "Job Summary For " & strFileName

'Close Source File
wkbFile.Close SaveChanges:=False
Set wkbFile = Nothing

'Reset Default Location for Opening Files
ChDrive "C"
ChDir strOriginalDirectory

'Restore Alerts & Updates
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Wookiee]

Did either one of my suggestions help?