Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put worksheet names from inactive file into cells of active worksh
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put worksheet names from inactive file into cells of active worksh
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Put worksheet names from inactive file into cells of active wo
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! Dave Peterson" wrote: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL 2007: can't change Inactive Excel Add ins to Active (DLL Com Addin as Visible) | Excel Programming | |||
Average IF? A1:A1000 has numbers - B1:B1000 has Active or Inactive | Excel Discussion (Misc queries) | |||
How can I have formatting options like merge cells ,Bold,active for the unlocked cells of the protected worksheet.Is it possible in excel? | Excel Programming | |||
Excel should indicate a list of inactive names | Excel Discussion (Misc queries) | |||
Multiselect ListBox - Active/Inactive Items | Excel Programming |