Hi,
you can find my answers below.nearly everyday a workbook is added to the
folders or a worksheet is added to a workbook in the shared path in the
company.
--
SAHRAYICEDIT-ISTANBUL
"OssieMac":
Hi SAHRAYICEDIT-ISTANBUL and Jay,
First of all acknowledgement to Jay. All contributions are greatfully
received by me because they all add to one's library of knowledge of how to
tackle a problem. However, as you suggested, I am happy to continue from here
and get back to you if I have a problem.
To SAHRAYICEDIT-ISTANBUL,
Further to Jay’s comments, we have reached the stage where it is now
essential to go to the basics of programming and document exactly what you
are trying to achieve otherwise we are flying blind and creating routines
that do not perform to your requirements. I’ll list the criteria as I
understand it and then add some questions for you to answer so that I can
fully understand what it is you are trying to achieve and work towards that.
1. I understand that the procedure will be run from a workbook located in a
folder on your PC and the folders to be searched are on a network and in
entirely different folders. (If this is correct then there is no need to
check whether the procedure is attempting to re-open the workbook with the
macro.)
***yes,you are right.folders are shared for all users.anyone can reach the
folders in the company.list will be written an a excel file in my computer.
2. The latest example you posted places the worksheets in the first column
and the Workbook names in column 7. Is this essential to your requirements or
can the workbook names and worksheet names be placed in adjacent columns?
***I only want to see worksheetnames, but I must be capable of clicking the
name to open the excel file to see the worksheet.
3. To successfully set up links, the full file path needs to be saved
somewhere. Do you want to be able to view this path or do you just want to be
able to view the workbook names and the worksheet names with the full file
path saved but hidden? I understand that the search starts from a specific
path on the network and searches several folders from that path. If required,
it is possible to just save and hide the main initial search path somewhere
and then include any folder names past that point with the workbook names so
let me know what you want.
*** I do not need to see the path, when the mouse is on the worksheetname, it is enough to see the path,if it is impossible no problem.when I click the name, the workbook will be opened, I will see the name of the workbook, I can find it from the search.
4. Can the sheet where the workbook and worksheet names are saved be cleared
of data prior to running the procedure or do you anticipate running it for a
specific file path and then change the file path and run it again at another
file path and append to previous data. This makes a difference as to the best
way to handle recording the data on the worksheet.
***no, path that will be searched is constant for example D:\costs
5. Do you want column headers in the first row of the data? (eg. Workbook
Name and Worksheet Name).
***ı do not need to see any of them
6. When the links are created to the worksheets, what do you intend doing
after clicking on the link and opening the workbook at the required
worksheet? What I mean by this is will you be simply doing work manually
within the worksheet and then saving and closing it or do you anticipate
having additional automated processing like copying data from the newly
opened workbook to another workbook? Your answer to this makes a difference
as to how to handle the code for this process.
***when I click the worksheetname, the workbook will be opened then I will
print then close the file.
7. Are the workbooks on the network likely to be in use by another user when
you want to access them? That is are they shared workbooks?
***all of the workbooks are shared
8. As per my question in a previous posing, what version of Excel are you
using?
*** ı am using excel 2003 at home and company.but when I run the below code
at home I have no problem, when I try it in the company "sub or function not
defined" error comes.one more problem with this code is that is does not get
all worksheetnames from all workbooks and after running nearly one minute it
gives
"An error occurred... action canceled."
Sub GetAllWorksheetNames()
Dim i As Integer
Dim L As Integer
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbCodeBookws As Worksheet
Dim wSheet As Worksheet
Dim myFolderPath As String
Dim mySubFolderPath As String
On Error GoTo errorHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wbCodeBook = ThisWorkbook
Set wbCodeBookws = ActiveSheet
wbCodeBookws.Cells.Clear
ActiveWindow.FreezePanes = False
Range("A1") = "WorksheetName": Range("B1") = "SheetOrder"
Range("C1") = "FileName": Range("D1") = "FullPath"
pFolder = Application.GetOpenFilename
If pFolder <> "False" Then
pFolder = Left(pFolder, InStrRev(pFolder, "\") - 1)
Else
MsgBox "Procedure canceled. No file selected."
Exit Sub
End If
With Application.FileSearch
.NewSearch
.LookIn = pFolder
.SearchSubFolders = True
..FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
L = InStrRev(.FoundFiles(i), "\")
mySubFolderPath = Left(.FoundFiles(i), L - 1)
If .FoundFiles(i) = ThisWorkbook.Path & "\" & ThisWorkbook.Name _
Or Mid(.FoundFiles(i), L + 1) = ThisWorkbook.Name Then
Set wbResults = ThisWorkbook
Else
Set wbResults = Workbooks.Open(.FoundFiles(i))
End If
'Lay in worksheet names
iw = 0
For Each wSheet In wbResults.Worksheets
If iw = 0 Then tRow = wbCodeBookws. _
Cells(Rows.Count, 1).End(xlUp)(2, 1).Row
wbCodeBookws.Cells(Rows.Count, 1).End(xlUp)(2, 1) _
= wSheet.Name
iw = iw + 1
wbCodeBookws.Cells(Rows.Count, 1).End(xlUp)(1, 2) _
= iw
Next 'wSheet
bRow = tRow + iw - 1
'Lay in filenames
wbCodeBookws.Range(wbCodeBookws.Cells(tRow, 3), _
wbCodeBookws.Cells(bRow, 3)) = Mid(.FoundFiles(i), L + 1)
'Lay in full workbook pathname as a hyperlink
For ih = tRow To bRow
ActiveSheet.Hyperlinks.Add _
Anchor:=wbCodeBookws.Cells(ih, 4), _
Address:=.FoundFiles(i)
Next ih
If wbResults.FullName <> ThisWorkbook.FullName Then _
wbResults.Close SaveChanges:=False
Next i
End If
End With
'Sort list by folderpath, filename, and sheetorder
Range("A1").CurrentRegion.Sort Key1:=Range("D2"), _
Order1:=xlAscending, Key2:=Range("C2"), _
Order2:=xlAscending, Key3:=Range("B2"), _
Order3:=xlAscending, Header:=xlYes
'Format Output
wbCodeBookws.Activate
wbCodeBookws.Cells(2, 1).Select
ActiveWindow.FreezePanes = True
wbCodeBookws.Columns("A
").AutoFit
Selection.AutoFilter
wrapSub:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
errorHandler:
MsgBox "An error occurred... action canceled."
Resume wrapSub
End Sub
