CODEWITHSUNDEEP
excelvba
Amirul
Amirul

Reputation: 25

Looping through file , temp file interfering

My script would require accessing all excel file in some other folder. Excel create a temporary file which the script would try to access and return an error.

I dont even know what to look for .

Set System_exp = CreateObject("Scripting.FileSystemObject")

Set folder = System_exp.Getfolder("C:\Sumthing\data")  'Working directory'
Set file_list = folder.Files

For Each file In file_list
       Set Workbook = Workbooks.Open(file)
       [Some loop/function here]
Next


End Sub

Was't expecting the temporary file .

Run-time error '1004' : Excel cannot open the file '~$Data 1.xlsx' because the file format or file extension not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

Apparently it some sort of backup file in case excel crash . What can I do?

Upvotes: 1

Views: 410

Answers (1)

Tim Williams
Tim Williams

Reputation: 166980

You can test the filename: 

For Each file In file_list
    If Not file.Name Like "~*" Then 
       Set Workbook = Workbooks.Open(file)
       [Some loop/function here]
    End If
Next

Upvotes: 1

Related Questions