Excel Loop to skip "Hidden Files"

[RMM4487]

Hi all,

Full disclosure, I am still learning VBA, am not very good yet, and hoping someone can help. I currently have a script (not written by myself) being used at my office. It used to run fine but now is throwing an error because it is picking up a file called "thumbs.db". After some due diligence, it seems that this file is a "hidden" file which we have no idea why it is now present. Regardless, I'm trying to rearrange a loop in a script to essentially skip this file / file type. Basically the script is meant to do the following (simple):

Goes to a specific path, loops through and opens all the excel files and pulls data into another sheet.

This is what the script looks like currently (original):

Sub Logging(path As String, newpath As String)
Dim objFile As Variant
Dim myFolder
Dim strFileName As String
Dim myDir As String
Dim strFilePath As String
Dim rownum As Integer

'myDir = InputBox(prompt:="Input file path")
rownum = 1895
'InputBox(prompt:="Input row number with formula")


Set FileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSys.GetFolder(path)

For Each objFile In myFolder.Files
strFileName = objFile.Name
strFilePath = objFile.path
MsgBox objFile.path
Log strFilePath, strFileName, rownum, FileDateTime(strFilePath)
Name strFilePath As newpath & "" & strFileName
Next objFile


End Sub

This was my attempt at fixing and skipping hidden files (throws debug error):

Sub Logging(path As String, newpath As String)
Dim objFile As Variant
Dim myFolder
Dim strFileName As String
Dim myDir As String
Dim strFilePath As String
Dim rownum As Integer

'myDir = InputBox(prompt:="Input file path")
rownum = 1895
'InputBox(prompt:="Input row number with formula")


Set FileSys = CreateObject("Scripting.FileSystemObject")
Set myFolder = FileSys.GetFolder(path)

For Each objFile In myFolder.Files

If file.Attributes.tostring <> "Hidden, System" Then
strFileName = objFile.Name
strFilePath = objFile.path
Log strFilePath, strFileName, rownum, FileDateTime(strFilePath)
Name strFilePath As newpath & "" & strFileName
Else
End If
Next objFile
End Sub


Any assistance is greatly appreciated as both attempts throw errors.

 

[Anthony47]

Are you looking for ANY file or some specific extensions?

Bye

 

[V_Malkoti]

Try:

If objFile.Attributes And 2 Then
  ' File is hidden
End If

 

[RMM4487]

All files are Excel based and so the extension would be .xls etc. However, I'd like to skip over the file that has the extension ".db"

 

[RMM4487]

Are you looking for ANY file or some specific extensions?

Bye

All files are Excel based and so the extension would be .xls etc. However, I'd like to skip over the file that has the extension ".db"

 

[Anthony47]

I insist you use "positive list of extensions", not "negative ones"
This said, that is my suggestion:

Dim myExt As String, mySplit

myExt = ".txt .csv .xls .xlsx .xlsm .xlsb"        '<<< List of desired Extensions, in this format
mySplit = Split(" " & strFileName, ".", , vbTextCompare)
If InStr(1, myExt, mySplit(UBound(mySplit)), vbTextCompare) > 0 Then
    'IfYes - Code if file has one of the desired extensions
Else
    'IfNot - Code for files with unwanted extensions
End If

If you prefer declaring extensions to be ignored, the approach could be the same, but you will revert the IfYes and IfNot concept.

Bye