Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get list of subfolders, including shortcuts to folders?
The code below prints a list of subfolders in the specified folder.
However, it overlooks shortcuts to folders. How can I include shortcuts to folders in the list, but not files and shortcuts to files? I think I want to know: how can I find the attribute of the object that a shortcut ultimately links to? (By "ultimately links to", I mean: the non-shortcut object, if a shortcut links to a shortcut, if that is even possible in Win XP.) Shortcuts to folders and files have only the attribute vbArchive on Win XP, the same as normal files. (I suspect GetAttr returns zero if the object has been backed up.) Shortcuts are distinguishable from normal file by the extension ".lnk". But shortcuts to folders seem indistinguishable from shortcuts to files. Sub doit() Const topdir As String = "C:\Documents and Settings\foo\My Documents\bar\" Dim d As String, x As Long d = Dir(topdir, vbDirectory) While d < "" x = GetAttr(topdir & d) If InStr(d, ".lnk") Then stop 'debug If x And vbDirectory Then Debug.Print d d = Dir() Wend End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get list of subfolders, including shortcuts to folders?
Something like what I've got below is a slightly different approach but
should deliver close to what you want. Note that the CreateShortcut method of the "Wscript.Shell" object returns a reference to a shortcut with its properties exposed; a new shortcut is only created if you follow with the Save method which we don't do here. '-------------------------------------------- Sub FindSubfolders() Dim strTopFolder As String Dim strReport As String Set fso = CreateObject("Scripting.FileSystemObject") Set wsh = CreateObject("WScript.Shell") strTopFolder = "C:\Test" strReport = "" If Not fso.FolderExists(strTopFolder) Then Exit Sub End If Set fldr = fso.GetFolder(strTopFolder) For Each subFldr In fldr.Subfolders strReport = strReport & subFldr.Path & vbCrLf Next subFldr For Each myFile In fldr.Files If fso.GetExtensionName(myFile) = "lnk" Then Set myLnk = wsh.CreateShortcut(myFile) If fso.FolderExists(myLnk.TargetPath) Then strReport = strReport & "Shortcut to " & myLnk.TargetPath & vbCrLf End If Set myLnk = Nothing End If Next myFile MsgBox strReport Set fso = Nothing Set wsh = Nothing End Sub '-------------------------------------------- Steve Yandl "JoeU2004" wrote in message ... The code below prints a list of subfolders in the specified folder. However, it overlooks shortcuts to folders. How can I include shortcuts to folders in the list, but not files and shortcuts to files? I think I want to know: how can I find the attribute of the object that a shortcut ultimately links to? (By "ultimately links to", I mean: the non-shortcut object, if a shortcut links to a shortcut, if that is even possible in Win XP.) Shortcuts to folders and files have only the attribute vbArchive on Win XP, the same as normal files. (I suspect GetAttr returns zero if the object has been backed up.) Shortcuts are distinguishable from normal file by the extension ".lnk". But shortcuts to folders seem indistinguishable from shortcuts to files. Sub doit() Const topdir As String = "C:\Documents and Settings\foo\My Documents\bar\" Dim d As String, x As Long d = Dir(topdir, vbDirectory) While d < "" x = GetAttr(topdir & d) If InStr(d, ".lnk") Then stop 'debug If x And vbDirectory Then Debug.Print d d = Dir() Wend End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get list of subfolders, including shortcuts to folders?
It appears that the line
strReport = strReport & "Shortcut to " & myLnk.TargetPath & vbCrLf got broken in my post above (inside the For...Each loop checking shortcuts). Be sure to repair before testing the routine. Steve "Steve Yandl" wrote in message ... Something like what I've got below is a slightly different approach but should deliver close to what you want. Note that the CreateShortcut method of the "Wscript.Shell" object returns a reference to a shortcut with its properties exposed; a new shortcut is only created if you follow with the Save method which we don't do here. '-------------------------------------------- Sub FindSubfolders() Dim strTopFolder As String Dim strReport As String Set fso = CreateObject("Scripting.FileSystemObject") Set wsh = CreateObject("WScript.Shell") strTopFolder = "C:\Test" strReport = "" If Not fso.FolderExists(strTopFolder) Then Exit Sub End If Set fldr = fso.GetFolder(strTopFolder) For Each subFldr In fldr.Subfolders strReport = strReport & subFldr.Path & vbCrLf Next subFldr For Each myFile In fldr.Files If fso.GetExtensionName(myFile) = "lnk" Then Set myLnk = wsh.CreateShortcut(myFile) If fso.FolderExists(myLnk.TargetPath) Then strReport = strReport & "Shortcut to " & myLnk.TargetPath & vbCrLf End If Set myLnk = Nothing End If Next myFile MsgBox strReport Set fso = Nothing Set wsh = Nothing End Sub '-------------------------------------------- Steve Yandl "JoeU2004" wrote in message ... The code below prints a list of subfolders in the specified folder. However, it overlooks shortcuts to folders. How can I include shortcuts to folders in the list, but not files and shortcuts to files? I think I want to know: how can I find the attribute of the object that a shortcut ultimately links to? (By "ultimately links to", I mean: the non-shortcut object, if a shortcut links to a shortcut, if that is even possible in Win XP.) Shortcuts to folders and files have only the attribute vbArchive on Win XP, the same as normal files. (I suspect GetAttr returns zero if the object has been backed up.) Shortcuts are distinguishable from normal file by the extension ".lnk". But shortcuts to folders seem indistinguishable from shortcuts to files. Sub doit() Const topdir As String = "C:\Documents and Settings\foo\My Documents\bar\" Dim d As String, x As Long d = Dir(topdir, vbDirectory) While d < "" x = GetAttr(topdir & d) If InStr(d, ".lnk") Then stop 'debug If x And vbDirectory Then Debug.Print d d = Dir() Wend End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get list of subfolders, including shortcuts to folders?
"Steve Yandl" wrote:
Something like what I've got below is a slightly different approach but should deliver close to what you want. Yes, it seems to do exactly what I want. Thanks. I confess that I don't fully understand why. I certainly understand the logic of VB code, taking some things for granted. But I really have no knowledge of the Scripting.FileSystemObject and WScript.Shell objects. Where should I go to learn all about them? (A Google search turned up some references, some more complete than others. I have not fully explored this.) ----- original message ----- "Steve Yandl" wrote in message ... Something like what I've got below is a slightly different approach but should deliver close to what you want. Note that the CreateShortcut method of the "Wscript.Shell" object returns a reference to a shortcut with its properties exposed; a new shortcut is only created if you follow with the Save method which we don't do here. '-------------------------------------------- Sub FindSubfolders() Dim strTopFolder As String Dim strReport As String Set fso = CreateObject("Scripting.FileSystemObject") Set wsh = CreateObject("WScript.Shell") strTopFolder = "C:\Test" strReport = "" If Not fso.FolderExists(strTopFolder) Then Exit Sub End If Set fldr = fso.GetFolder(strTopFolder) For Each subFldr In fldr.Subfolders strReport = strReport & subFldr.Path & vbCrLf Next subFldr For Each myFile In fldr.Files If fso.GetExtensionName(myFile) = "lnk" Then Set myLnk = wsh.CreateShortcut(myFile) If fso.FolderExists(myLnk.TargetPath) Then strReport = strReport & "Shortcut to " & myLnk.TargetPath & vbCrLf End If Set myLnk = Nothing End If Next myFile MsgBox strReport Set fso = Nothing Set wsh = Nothing End Sub '-------------------------------------------- Steve Yandl "JoeU2004" wrote in message ... The code below prints a list of subfolders in the specified folder. However, it overlooks shortcuts to folders. How can I include shortcuts to folders in the list, but not files and shortcuts to files? I think I want to know: how can I find the attribute of the object that a shortcut ultimately links to? (By "ultimately links to", I mean: the non-shortcut object, if a shortcut links to a shortcut, if that is even possible in Win XP.) Shortcuts to folders and files have only the attribute vbArchive on Win XP, the same as normal files. (I suspect GetAttr returns zero if the object has been backed up.) Shortcuts are distinguishable from normal file by the extension ".lnk". But shortcuts to folders seem indistinguishable from shortcuts to files. Sub doit() Const topdir As String = "C:\Documents and Settings\foo\My Documents\bar\" Dim d As String, x As Long d = Dir(topdir, vbDirectory) While d < "" x = GetAttr(topdir & d) If InStr(d, ".lnk") Then stop 'debug If x And vbDirectory Then Debug.Print d d = Dir() Wend End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get list of subfolders, including shortcuts to folders?
To be honest, my background is much stronger in VBScript than in VBA. For
most tasks you would consider from Excel, Word or other Office applications, VBA is the way to go. I visit this and several other VBA oriented groups to learn from people who really know how to make VBA produce results. However, when one departs from tasks normally associated with the Office applications and you want to retrieve info that would normally be retrieved by some operation in Windows Explorer or Internet Explorer, the scripting runtime libraries offer objects that can often make the task easier. The good news is that anything that can be done with VBScript can be translated to VBA. On those instances when I think I can throw in a tidbit where VBS can be integrated into a VBA routine, I respond in the newsgroup. If you want to learn a bit about VBS, WSH, and now PowerScript, visit http://technet.microsoft.com/en-us/s...r/default.aspx Steve Yandl "JoeU2004" wrote in message ... "Steve Yandl" wrote: Something like what I've got below is a slightly different approach but should deliver close to what you want. Yes, it seems to do exactly what I want. Thanks. I confess that I don't fully understand why. I certainly understand the logic of VB code, taking some things for granted. But I really have no knowledge of the Scripting.FileSystemObject and WScript.Shell objects. Where should I go to learn all about them? (A Google search turned up some references, some more complete than others. I have not fully explored this.) ----- original message ----- "Steve Yandl" wrote in message ... Something like what I've got below is a slightly different approach but should deliver close to what you want. Note that the CreateShortcut method of the "Wscript.Shell" object returns a reference to a shortcut with its properties exposed; a new shortcut is only created if you follow with the Save method which we don't do here. '-------------------------------------------- Sub FindSubfolders() Dim strTopFolder As String Dim strReport As String Set fso = CreateObject("Scripting.FileSystemObject") Set wsh = CreateObject("WScript.Shell") strTopFolder = "C:\Test" strReport = "" If Not fso.FolderExists(strTopFolder) Then Exit Sub End If Set fldr = fso.GetFolder(strTopFolder) For Each subFldr In fldr.Subfolders strReport = strReport & subFldr.Path & vbCrLf Next subFldr For Each myFile In fldr.Files If fso.GetExtensionName(myFile) = "lnk" Then Set myLnk = wsh.CreateShortcut(myFile) If fso.FolderExists(myLnk.TargetPath) Then strReport = strReport & "Shortcut to " & myLnk.TargetPath & vbCrLf End If Set myLnk = Nothing End If Next myFile MsgBox strReport Set fso = Nothing Set wsh = Nothing End Sub '-------------------------------------------- Steve Yandl "JoeU2004" wrote in message ... The code below prints a list of subfolders in the specified folder. However, it overlooks shortcuts to folders. How can I include shortcuts to folders in the list, but not files and shortcuts to files? I think I want to know: how can I find the attribute of the object that a shortcut ultimately links to? (By "ultimately links to", I mean: the non-shortcut object, if a shortcut links to a shortcut, if that is even possible in Win XP.) Shortcuts to folders and files have only the attribute vbArchive on Win XP, the same as normal files. (I suspect GetAttr returns zero if the object has been backed up.) Shortcuts are distinguishable from normal file by the extension ".lnk". But shortcuts to folders seem indistinguishable from shortcuts to files. Sub doit() Const topdir As String = "C:\Documents and Settings\foo\My Documents\bar\" Dim d As String, x As Long d = Dir(topdir, vbDirectory) While d < "" x = GetAttr(topdir & d) If InStr(d, ".lnk") Then stop 'debug If x And vbDirectory Then Debug.Print d d = Dir() Wend End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please help to list folders and subfolders tree in Excel or Word | Excel Discussion (Misc queries) | |||
Opening files in folders and subfolders | Excel Discussion (Misc queries) | |||
linking Outlook email folders/subfolders to excell spreadsheets | Excel Worksheet Functions | |||
Map/List of folders, subfolders & files | Excel Programming | |||
Creating folders and subfolders from excel file list | Excel Discussion (Misc queries) |