| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting worksheet names
hi,
I added below code, but I cannot see worksheet names in workbooks in sub folders ? what is the problem ? SearchSubFolders = IncludeSubFolder If I want to see the directory of each worksheet next to the name of it how can we revise the code? for example; book ( name of worksheet ) D:\library\... -- SAHRAYICEDIT-ISTANBUL Sub GetAllWorksheetNames() Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim wSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "C:\my documents" 'amend to suit SearchSubFolders = IncludeSubFolder .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then For i = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(.FoundFiles(i)) wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name) For Each wSheet In wbResults.Worksheets wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = wSheet.Name Next wSheet wbResults.Close SaveChanges:=False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub Will that do? Mike "excel-tr" wrote: Hi, for example assume , I have 30 workbooks and 30 worksheets in each workbook.totally 900 worksheets. I do not want to open each workbook to browse, there must be a way to see all worksheets in a way ? in a list ? -- SAHRAYICEDIT-ISTANBUL |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting worksheet names
Hi excel-tr
See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "excel-tr" wrote in message ... hi, I added below code, but I cannot see worksheet names in workbooks in sub folders ? what is the problem ? SearchSubFolders = IncludeSubFolder If I want to see the directory of each worksheet next to the name of it how can we revise the code? for example; book ( name of worksheet ) D:\library\... -- SAHRAYICEDIT-ISTANBUL Sub GetAllWorksheetNames() Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim wSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "C:\my documents" 'amend to suit SearchSubFolders = IncludeSubFolder .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then For i = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(.FoundFiles(i)) wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name) For Each wSheet In wbResults.Worksheets wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = wSheet.Name Next wSheet wbResults.Close SaveChanges:=False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub Will that do? Mike "excel-tr" wrote: Hi, for example assume , I have 30 workbooks and 30 worksheets in each workbook.totally 900 worksheets. I do not want to open each workbook to browse, there must be a way to see all worksheets in a way ? in a list ? -- SAHRAYICEDIT-ISTANBUL |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting worksheet names
Oops, wrong reply
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Hi excel-tr See http://www.xldynamic.com/source/xld.xlFAQ0002.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "excel-tr" wrote in message ... hi, I added below code, but I cannot see worksheet names in workbooks in sub folders ? what is the problem ? SearchSubFolders = IncludeSubFolder If I want to see the directory of each worksheet next to the name of it how can we revise the code? for example; book ( name of worksheet ) D:\library\... -- SAHRAYICEDIT-ISTANBUL Sub GetAllWorksheetNames() Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim wSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "C:\my documents" 'amend to suit SearchSubFolders = IncludeSubFolder .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then For i = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(.FoundFiles(i)) wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name) For Each wSheet In wbResults.Worksheets wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = wSheet.Name Next wSheet wbResults.Close SaveChanges:=False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub Will that do? Mike "excel-tr" wrote: Hi, for example assume , I have 30 workbooks and 30 worksheets in each workbook.totally 900 worksheets. I do not want to open each workbook to browse, there must be a way to see all worksheets in a way ? in a list ? -- SAHRAYICEDIT-ISTANBUL |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting worksheet names
Hi,
try: ..SearchSubFolders = True I dont know, where "IncludeSubFolder" comes from. -- Greetings from Bavaria, Germany Helmut Weber, MVP WordVBA Win XP, Office 2003 "red.sys" & Chr$(64) & "t-online.de" |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
getting worksheet names
Try
..SearchSubFolders = True -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "excel-tr" wrote in message ... hi, I added below code, but I cannot see worksheet names in workbooks in sub folders ? what is the problem ? SearchSubFolders = IncludeSubFolder If I want to see the directory of each worksheet next to the name of it how can we revise the code? for example; book ( name of worksheet ) D:\library\... -- SAHRAYICEDIT-ISTANBUL Sub GetAllWorksheetNames() Dim i As Integer Dim wbResults As Workbook Dim wbCodeBook As Workbook Dim wSheet As Worksheet Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch .LookIn = "C:\my documents" 'amend to suit SearchSubFolders = IncludeSubFolder .FileType = msoFileTypeExcelWorkbooks If .Execute 0 Then For i = 1 To .FoundFiles.Count Set wbResults = Workbooks.Open(.FoundFiles(i)) wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = UCase(wbResults.Name) For Each wSheet In wbResults.Worksheets wbCodeBook.Sheets(1).Range _ ("A65536").End(xlUp)(2, 1) = wSheet.Name Next wSheet wbResults.Close SaveChanges:=False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub Will that do? Mike "excel-tr" wrote: Hi, for example assume , I have 30 workbooks and 30 worksheets in each workbook.totally 900 worksheets. I do not want to open each workbook to browse, there must be a way to see all worksheets in a way ? in a list ? -- SAHRAYICEDIT-ISTANBUL |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| using the Excel generic worksheet names instead of user-given names in code | Excel Discussion (Misc queries) | |||
| MAKE A LIST OF NAMES FROM REPEATED NAMES IN THE SAME WORKSHEET | Excel Discussion (Misc queries) | |||
| Changing VB Component Names to match Worksheet names using VBE | Excel Programming | |||
| return all worksheet tab names and chart sheet tab names in report - an example | Excel Programming | |||
| copy range on every worksheet (diff names) to a master worksheet (to be created) | Excel Programming | |||
Hybrid Mode
