Forum Discussion
VBA Code help
Hi Good day,
Could any one help me in understand the following code and what it does.
strFilename = Dir(MyPath & "\*.xls*", vbNormal)
Thanks
MyPath is a variable that holds the path of a folder.
Dir(MyPath & "\*.xls*", vbNormal) looks for all files in that folder whose extension starts with xls, i.e. Excel workbooks:
*.xls
*.xlsx
*.xlsm
*.xlsb
It returns the name of the first such file that it encounters to the variable strFilename.
A line like this is commonly used to start a loop through all Excel workbooks in a folder:
Sub Test() Dim MyPath As String Dim strFilename As String Dim wbk As Workbook ' Speed up execution by not diplaying changes Application.ScreenUpdating = False ' Specify the folder MyPath = "..." ' Get the first filename strFilename = Dir(MyPath & "\*.xls*") ' vbNormal is assumed if not specified ' Loop through the files Do While strFile <> "" ' Open the workbook Set wbk = Workbooks.Open(Filename:=strFilename) ' Do something with the workbook ' ... ' Close the workbook wbk.Close SaveChanges:=True ' True to save changes, False to dismiss them ' Find the next filename strFilename = Dir Loop ' Refresh the screen again Application.ScreenUpdating = True End Sub
2 Replies
MyPath is a variable that holds the path of a folder.
Dir(MyPath & "\*.xls*", vbNormal) looks for all files in that folder whose extension starts with xls, i.e. Excel workbooks:
*.xls
*.xlsx
*.xlsm
*.xlsb
It returns the name of the first such file that it encounters to the variable strFilename.
A line like this is commonly used to start a loop through all Excel workbooks in a folder:
Sub Test() Dim MyPath As String Dim strFilename As String Dim wbk As Workbook ' Speed up execution by not diplaying changes Application.ScreenUpdating = False ' Specify the folder MyPath = "..." ' Get the first filename strFilename = Dir(MyPath & "\*.xls*") ' vbNormal is assumed if not specified ' Loop through the files Do While strFile <> "" ' Open the workbook Set wbk = Workbooks.Open(Filename:=strFilename) ' Do something with the workbook ' ... ' Close the workbook wbk.Close SaveChanges:=True ' True to save changes, False to dismiss them ' Find the next filename strFilename = Dir Loop ' Refresh the screen again Application.ScreenUpdating = True End Sub
- RudrabhadraBrass ContributorThanks a lot.. for the detailed explanation.