Create Microsoft Scripting Runtime Library Reference. Here are the instructions to add reference before running below VBA macro code.
Step by step instructions to add Microsoft Scripting Library reference
Here are the step by step instructions to add reference.
- Go to Tools from Visual Basic Editor (VBE) menu.
- Click on references from the available options.
- Check the Microsoft scripting Runtime.
- Click on OK.
- Please find the below screenshot for your reference.
Create FSO Object
Let us see how to create FSO object in visual basic editor window. It has to be crate in the standard module. It gives access to file system object(FSO) in VBA.
Sub VBAF1_Create_FSO_Object() 'Variable declaration Dim FSO As Scripting.FileSystemObject 'Crate object for FSO Set FSO = New Scripting.FileSystemObject End Sub
VBA FSO Auto List Members
Now you can access all VBA FSO Auto List Members of file system object(FSO). After entering FSO. enter Alt + Space Bar from keyboard to see auto list members.
Enable VBA FSO Auto List Members
You can enable VBA FSO Auto List Members by following step by step instructions.
- Go to Tools from Visual Basic Editor (VBE) menu.
- Click on Options from the available options.
- Go to Editor tab.
- Check Auto List Members(if it is not checked).
- Click Ok.
- You can find following screen shot for visual representation and better understand.
Instructions to Run VBA Macro Code or Procedure:
You can refer the following link for the step by step instructions.
Instructions to run VBA Macro Code
Other Useful Resources:
Click on the following links of the useful resources. These helps to learn and gain more knowledge.
VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables
VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog
Hi! I’m building out a list that opens my files with a macro (ranging from .txt, docx, xlsm etc.), I was using the following tutorial and along with your information was able to finish my task:
https://www.youtube.com/watch?v=_0rdtX9bHmw
Thanks so much for your write-up, & here’s what I was thinking in case someone else can get use out of it (pasted below); next steps for cleaning script up would be to loop it under Shell.Open.
Sub ImportAnyObject()
Dim PathandFile1 As String
Dim PathandFile2 As String
Dim FSO As Object
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Dim Shell As Object
Set Shell = CreateObject(“Shell.Application”)
Dim Workbook1 As String
Dim path1 As String
‘Dim SheetCopyFrom As String
‘Dim RangeName As String
Dim Workbook2 As String
Dim path2 As String
‘Dim fileFound As Boolean
‘Concatenate the path and the file
path1 = Sheets(“Links”).Cells(3, 1) & “\”
Workbook1 = Sheets(“Links”).Cells(3, 2)
PathandFile1 = path1 & Workbook1
‘SheetCopyFrom = Sheets(“Links”).Cells(3, 3)
‘RangeCopyFrom = Sheets(“Links”).Cells(3, 4)
‘PathandFile = Application.GetOpenFilename(, , “Browse for Workbook”)
path2 = Sheets(“Links”).Cells(4, 1) & “\”
Workbook2 = Sheets(“Links”).Cells(4, 2)
PathandFile2 = path2 & Workbook2
Shell.Open (PathandFile1)
Shell.Open (PathandFile2)
End Sub