• HOME
  • ABOUT
  • BASIC EXCEL
  • VBA TIP

FUNNY EXCEL

  • HOME
  • ABOUT
  • BASIC EXCEL
  • VBA TIP
DIR FUNCTION IN VBA EXCEL

DIR FUNCTION IN VBA EXCEL

by mrhonghat 01/08/201905/08/2019
01/08/201905/08/2019 287 views

In VBA, there are many interesting functions with its specific functions. In this article, I will share about DIR function and some of its applications in practice.

Dir function

Returns a string representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir [ (pathname, [ attributes ] ) ]

  • Pathname:

The address of the file/folder in the computer.

  • Attributes:

You can choose, you can leave it blank. However, if left blank, some cases even though the path is correct, DIR does not get the value. For example, in the case of hidden files, alias files, …

The attributes argument settings are:

Constant

Description

vbNormal

(Default) Specifies files with no attributes.

vbReadOnly

Specifies read-only files in addition to files with no attributes.

vbHidden

Specifies hidden files in addition to files with no attributes.

vbSystem

Specifies system files in addition to files with no attributes. Not available on the Macintosh.

vbVolume

Specifies volume label; if any other attribute is specified, vbVolume is ignored. Not available on the Macintosh.

vbDirectory

Specifies directories or folders in addition to files with no attributes.

vbAlias

Specified file name is an alias. Available only on the Macintosh.

Some examples of DIR function

1.     Get the File Name

First, we create an excel file called Test.xlsx in drive D. Then we use the code below to get the file name.


Sub GetFileName()

Dim FileName As String

FileName = Dir(“D:\Test.xlsx”)

MsgBox “File Name is” & FileName

End Sub


Through the above code, it is easy to see that Dir function easily returns the filename (name and extension).

2.     Check if a Directory Exists or Not


Sub CheckDirectory ()

Dim FileName As String

FileName = Dir(“D:\Test.xlsx”)

If FileName = “” Then

MsgBox “File is not exist”

Else

MsgBox “File Name is” & FileName

End If

End Sub


Code idea: You can easily determine whether the file exists or not, by checking whether FileName is equal to whitespace.

3.     Get names of all File in One Folder


Sub Listfile()

Dim FileName As String

FileName = Dir(“D:\”, vbHidden)

Do While FileName <> “”

MsgBox FileName

FileName = Dir()

Loop

End Sub


For this case, you use the Do While loop to get name of all File in the directory.

dir functionvba excel
0 comment
2
FacebookTwitterGoogle +Pinterest
previous post
WHAT IS VBA?
next post
EXTRACT UNIQUE ITEMS FROM A LIST USING INDEX, MATCH AND COUNTIF

Leave a Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

VBA TIP

  • CODE VBA TO REMOVE ALL SHAPES QUICKLY IN EXCEL
  • SHARE ADD IN HCACULATOR IN WORD
  • VBA CODE SHORT USD 1.000.000.000 INTO 1 BILLION-DOLLAR
  • CREATE DATABASE ACCESS FROM EXCEL BY VBA
  • SPLIT TEXT STRING BY DELIMITERS
  • DIR FUNCTION IN VBA EXCEL
  • WHAT IS VBA?

@2019 - funyex.com. All Right Reserved. Designed and Developed by PenciDesign


Back To Top