VBA to open a shortcut to a workbook

[Walden2]

Is there a way to use VBA to "open" shortcuts to workbooks?

For example,
Book1.xls is in Folder A
There is a "shortcut" to Book1.xls in Folder B
Book2.xls is a workbook in Folder B
When opening Book2.xls, what VBA would look for shortcuts in the
Folder B and open those files?

Thanks,
Walden

 

[Bob Phillips]

Sub LoopFolders()
Dim oFSO As Object
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\Test")

For Each file In Folder.Files
If file.Type Like "*Shortcut*" Then
Workbooks.Open Filename:=file.Path
End If
Next file

Set oFSO = Nothing

End Sub



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jim May]

All you will need to do is in Book2.xls
Alt-F11 and in the ThisWorkbook Code Window paste in:

Private Sub Workbook_Open()
LoopFolders ' Bob's code posted in a Standard module (assumed)
End Sub

Jim May

 

[Walden2]

This worked great. Thanks much.

Walden

Sub LoopFolders()
Dim oFSO As Object
Dim Folder As Object
Dim Files As Object
Dim file As Object

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set Folder = oFSO.GetFolder("c:\Test")

For Each file In Folder.Files
If file.Type Like "*Shortcut*" Then
Workbooks.Open Filename:=file.Path
End If
Next file

Set oFSO = Nothing

End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)








- Show quoted text -