Put worksheet names from inactive file into cells of active worksh

[modo8]

Hello-

I'm building a macro to go through a list of file names; for any .xls files
in that list, I want to create a list of the worksheets in that file. I want
to create this new list in the "master" file that stores the macro.

I would prefer if I could keep the focus on the "master" sheet, and pull in
the names from the open, but inactive, .xls file. The code below is my
attempt to do that, but I get a "Subscript out of range (Error 9)".

I'm open to other solutions that have the end result of putting a list of
tab names on the master sheet without altering any content in the other .xls
files. I also tried keeping the focus on the .xls file and sending the
results to the inactive master sheet, but that didn't fare any better.

Thank you all, these boards are incredibly helpful to those people like me
who get in over their heads with random Excel projects

My code:

Do Until HRow = GRow
Windows("Info Delivery Version 2.xls").Activate ***The "Master Sheet"
Sheets("Sheet1").Activate

FileName = Range("C" & HRow)
FileType = Range("D" & HRow)

If FileType <> ".zip" Then
Application.EnableEvents = False
Workbooks.Open FileName:=Path & FileName
End If

Windows("Info Delivery Version 2.xls").Activate
Sheets("Sheet1").Activate

If FileType = ".xls" Then
Dim WS As Worksheet
For Each WS In Windows("FileName").Sheets ***Here's where I get an
error
Range("G" & IRow) = FileName
Range("H" & IRow) = WS.Name
IRow = IRow + 1
Next
ElseIf FileType <> ".zip" Then
Range("G" & IRow) = Range("C" & HRow)
IRow = IRow + 1
End If
HRow = HRow + 1
Loop

 

[Dave Peterson]

Windows() won't have any worksheets. But workbooks() will.

I'm not quite sure how your data is laid out, but maybe this will help:

Option Explicit
Sub testme()

Dim myPath As String
Dim myCell As Range
Dim FileNameRng As Range
Dim myFileName As String
Dim myFileType As String
Dim InfoWkbk As Workbook
Dim RptWks As Worksheet
Dim MstrWks As Worksheet
Dim TempWkbk As Workbook
Dim wks As Worksheet
Dim DestCell As Range
Dim cCtr As Long

myPath = "C:\my documents\excel\"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

Set MstrWks = ThisWorkbook.Worksheets("master wks name here")

Set RptWks = ThisWorkbook.Worksheets.Add
With RptWks
.Name = Format(Now, "yyyymmdd--hhmmss")
.Range("A1").Value = "FileName"
.Range("B1").Value = "Worksheet names -->"
Set DestCell = .Range("A1")
'all text.
'Don't lose leading 0's in worksheet names.
'don't convert names that look like dates to dates.
.Cells.NumberFormat = "@"
End With

With MstrWks
Set FileNameRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
End With

For Each myCell In FileNameRng.Cells
myFileName = myCell.Value
myFileType = myCell.Offset(0, 1).Value

Application.EnableEvents = False
Set TempWkbk = Nothing
On Error Resume Next
Set TempWkbk = Workbooks.Open _
(Filename:=myPath & myFileName & "." & myFileType, ReadOnly:=True)
On Error GoTo 0
Application.EnableEvents = True
If TempWkbk Is Nothing Then
'the file couldn't be opened!
Else
Set DestCell = DestCell.Offset(1, 0)
DestCell.Value = TempWkbk.FullName
cCtr = 1
For Each wks In TempWkbk.Worksheets
DestCell.Offset(0, cCtr).Value = wks.Name
cCtr = cCtr + 1
If cCtr > RptWks.Columns.Count - 1 Then
MsgBox "too many worksheets in file: " & vbLf _
& TempWkbk.FullName
Exit For
End If
Next wks

Application.EnableEvents = False
TempWkbk.Close savechanges:=False
Application.EnableEvents = True
End If
Next myCell
End Sub

 

[modo8]

Thanks, Dave-

Your code gave me the idea of creating a temporary worksheet, putting the
other sheet names into cells of the temporary worksheet, then copying them to
the master workbook and deleteing the temporary worksheet. It gets the job
done, at least. Thanks!