Looping through folder with DIR ???

[ksor]

I have this sub to sequentualy run through the files in the folder:

Sub loopThroughFiles(folder As String)
    ' Treversing through files in the folder and each
    ' Hyperlink (if any) is tested for existing FILE or URL
    Dim strFile As String
    strFile = Dir(folder)
    Do While Len(strFile) > 0
        loopThroughHyperlinks (folder & strFile)
        strFile = Dir()
        DoEvents
    Loop
End Sub

But in a specific folder I get run time error 5 = Invalid procedure call or argument.


There is ONLY Word documents in the folder - no hidden files !


What is wrong ?

 

[theDBguy]

Hi. Have you tried stepping through the code and examining the variable contents when you get the error?

 

[theDBguy]

Also, does the "specific folder" name have any special characters in it? Just curious...

 

[Gasman]

Is it on this line?

loopThroughHyperlinks

What are the parameters being passed when it fails.?

 

[ksor]

Oh, _BEEP_ it's in this line:


strFile = Dir



Folder name is:


C:\Users\ksor\Documents\Slægtsforskning\Slægtsforskning_V4\BO_docs\


Stepping through is tidious 1000's of files in each folder. There is NO subfolders.





there was NO problems in a folder ending on BAFO_Docs with Word documents too

 

[ksor]

Is it on this line?

loopThroughHyperlinks

What are the parameters being passed when it fails.?

As I write just under the code.

 

[theDBguy]

Oh, _BEEP_ it's in this line:


strFile = Dir



Folder name is:


C:\Users\ksor\Documents\Slægtsforskning\Slægtsforskning_V4\BO_docs\


Stepping through is tidious 1000's of files in each folder. There is NO subfolders.





there was NO problems in a folder ending on BAFO_Docs with Word documents too

Hi. Just as a test, could you try renaming the folder with something simple without a special character like the "ae" part? Just curious...

 

[ksor]

Hi. Just as a test, could you try renaming the folder with something simple without a special character like the "ae" part? Just curious...

But this danish 'ae' is too in the folder name on the folder that runs with NO error.

I only gave the LAST part of the folder name

C:\Users\ksor\Documents\Slægtsforskning\Slægtsfors kning_V4\BAFO_docs\
gives NO error !

C:\Users\ksor\Documents\Slægtsforskning\Slægtsfors kning_V4\BO_docs\ - gives error

 

[Gasman]

Well you could put an error section in there and then use On Error Go To ?
Have a breakpoint in there, so the code stopps when it finds an errant file ?

Then you can inspect the variables.

Alternatively copy suspected files to another folder and test on thos ?

Stepping through is tidious 1000's of files in each folder. There is NO subfolders.

 

[theDBguy]

But this danish 'ae' is too in the folder name on the folder that runs with NO error.

I only gave the LAST part of the folder name

C:\Users\ksor\Documents\Slægtsforskning\Slægtsfors kning_V4\BAFO_docs\
gives NO error !

C:\Users\ksor\Documents\Slægtsforskning\Slægtsfors kning_V4\BO_docs\ - gives error

Then, perhaps the error is in the filename. What is the filename when you get an error?

 

[theDBguy]

Stepping through is tidious 1000's of files in each folder. There is NO subfolders.

Didn't notice this earlier. You can set it up so you'll only have to step-through the code after you get the first error message.

 

[ksor]

Didn't notice this earlier. You can set it up so you'll only have to step-through the code after you get the first error message.

I don't get you there ... it stoppes at the specific line I pointed out earlier and if I step further I just get the same error message again ????????



I have Debug.Print the files and it runs through and the suddenly the "chain" is broken and the error comes up ... there are much more files in that folder.


The file names are just numbers like 12-2456.docx


Could the "directory chain" be broken - I don't get any warnings in Windows ???

 

[theDBguy]

I don't get you there ... it stoppes at the specific line I pointed out earlier and if I step further I just get the same error message again ????????



I have Debug.Print the files and it runs through and the suddenly the "chain" is broken and the error comes up ... there are much more files in that folder.


The file names are just numbers like 12-2456.docx


Could the "directory chain" be broken - I don't get any warnings in Windows ???

Hi. Part of my troubleshooting process is to pause the code, look at the variable contents, and also do some interactive statements through the Immediate Window and then slowly step through the code to monitor its progress. It would be hard for us to help you troubleshoot this if we can't see everything. Can you even tell the name of the file when the error happens? If so, is it always the same file? If so, if you move it out of the folder, does the error go away?

 

[ksor]

Here is a similar problem:


https://bytes.com/topic/access/answers/192904-dir-function-problem

 

[Gasman]

Why is this marked [Solved] ?

 

[ksor]

Why is this marked [Solved] ?

Because the link I gave tells what's the problem = Dir() command CAN*T be used "nested" - the second use will _BEEP_ the first use.


In MY case I had to collect ALL file names in an array first and then run through this array and examin each file for hyperlinks - and it works nicely !

 

[gemma-the-husky]

dir() is not recursive, so you can't read a sub directory, and then return to carry on testing the main directory.

However you said there were no subfolders - so either there were subfolders, or maybe an unexpected character in a file name caused a problem.

I think it just wasn't clear what the issue was.

 

[ksor]

dir() is not recursive, so you can't read a sub directory, and then return to carry on testing the main directory.

However you said there were no subfolders - so either there were subfolders, or maybe an unexpected character in a file name caused a problem.

I think it just wasn't clear what the issue was.

The overall goal is here to test if the Hyperlinks links to an existing local file OR an URL.


A part of testing for a local file is using Dir() - that's the problem !:banghead:


So I just collected all file names into an array and use this array as the primary Dir() - and It works like a good dream

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Gasman]

Really?

As pretty much all usage I have seen of that function does eaxctly that.

https://www.exceltrick.com/formulas_macros/vba-dir-function/

https://trumpexcel.com/vba-dir-function/

Plus I used your code to print out all the files in my temp folder without any issue.?

Because the link I gave tells what's the problem = Dir() command CAN*T be used "nested" - the second use will _BEEP_ the first use.


In MY case I had to collect ALL file names in an array first and then run through this array and examin each file for hyperlinks - and it works nicely !