Getting error : Data Link Properties while opening excel file

[Mahantesh]

Hi All,

I have written macro code to open last modified file from folder; It was working fine. 3 times i test-run it.

My concern is , why "strFilename" storing "Thumbs.db" file instead of last modified file excel file?

But all of sudden it started giving me Data Link Properties dialog box while opening excel file from a drive.
attached is the screenshot.

below is my code i have written to open last modifed file:

mydir = "N:\L'Oreal\Kiehls\Metrics Report\2016\"

Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(mydir)
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open mydir & strFilename, UpdateLinks:=3

Please help!

Regards,
Mahantesh

 

[Chihiro]

Well, you don't have any check for file extension. So if there is Thumbs.db file located in the folder that's going to cause issues (or any other file unrelated to Excel).

Thumbs.db is system generated file which is usually hidden.

Use Instr() function to check if objFile.Name contains ".xls" before further steps, if only wanting Excel files (I.E. .xlsx, .xls, .xlsm, .xlsb).

Edit: Link to Instr() use below.
https://www.techonthenet.com/excel/formulas/instr.php

 

[Mahantesh]

Well, you don't have any check for file extension. So if there is Thumbs.db file located in the folder that's going to cause issues (or any other file unrelated to Excel).

Thumbs.db is system generated file which is usually hidden.

Use Instr() function to check if objFile.Name contains ".xls" before further steps, if only wanting Excel files (I.E. .xlsx, .xls, .xlsm, .xlsb).

Edit: Link to Instr() use below.
https://www.techonthenet.com/excel/formulas/instr.php

--------------------------------
Hi mate,

Thank you for the reply. I will change this line in my code & let me check how it goes?

Regards
Mahantesh