[RESOLVED] [Excel] Dir() function is not picking up File Shortcuts

[cheongk]

Hi,

I'm using the Excel VBA Dir() function to scan for all "*.xls" files in a directory. It pickus up all the Excel files in the directory but not any files that are shortcuts.

According to MSDN
Public Overloads Function Dir( _
Optional ByVal PathName As String, _
Optional ByVal Attributes As FileAttribute = FileAttribute.Normal _
) As String

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.
vbVolume Specifies volume label; if any other attribute is specified,
vbArchive File has changed since last backup.
vbAlias File has a different name.

I've tried FileAttribute as combinations of the above including "vbAlias And vbHidden And vbNormal And vbReadOnly And vbSystem" but Dir() can't seem to pick up any shortcuts in the directory.

Any hints are appreciated.

Thanks.

Kind regards,
Cheong

[cheongk]

Hi,

Found the problem. Sorry to trouble you guys. In case some one has a similar problem, the links are coming out with a "lnk" as part of the extension. For example:
G:\SHARE\Shortcut to AFile.xls.lnk

I managed to find it by replacing my search with "*.*" rather than "*.xls".

So, I'm running my scanning function as 2 pass:
(1) Look for files *.xls
(2) Look for files *.xls.lnk

I was thinking of just using *.xls.* but it could pick up some garbage files if someone renames their files to *.xls.abc or something.

Hope that it helps someone.

Kind regards,
Cheong