Searching for a folder in VBA
DonGosma
Posted messages
10
Status
Member
-
DonGosma Posted messages 10 Status Member -
DonGosma Posted messages 10 Status Member -
Hello,
I need to do some VBA and I cannot do otherwise because there are thousands of files:
I have a small problem because I want to open a file that is located in a directory and always starts with PKR_HIJ_1.xls or PKR_HIJ_2.xls or PKR_HIJ_3.xls or PKR_HIJ_4.xls.
However, the term HIJ can change, but I have already solved this issue. The problem is that some people want to add comments at the end of the files like for example: PKR_HIJ_1___(stair).xls; so I am stuck because I have made a loop that varies the number from 1 to 4, but I cannot find a file if there is a comment in parentheses. Is it possible to do an approximate search for example with just the beginning of the file?
Thank you in advance
Configuration: Windows XP / Firefox 3.6.12
I need to do some VBA and I cannot do otherwise because there are thousands of files:
I have a small problem because I want to open a file that is located in a directory and always starts with PKR_HIJ_1.xls or PKR_HIJ_2.xls or PKR_HIJ_3.xls or PKR_HIJ_4.xls.
However, the term HIJ can change, but I have already solved this issue. The problem is that some people want to add comments at the end of the files like for example: PKR_HIJ_1___(stair).xls; so I am stuck because I have made a loop that varies the number from 1 to 4, but I cannot find a file if there is a comment in parentheses. Is it possible to do an approximate search for example with just the beginning of the file?
Thank you in advance
Configuration: Windows XP / Firefox 3.6.12
3 answers
-
Hello,
First, thank you for your response, but I wasn't able to use it correctly in my code.
I'll try to explain more clearly:
I am looping for the name HIJ first, so it varies. Then, I want to find a file named PKR_HIJ_n__(comments).xls with n also varying; however, the comments in parentheses change for each file, so I want to open the files that start with "PKR_HIJ_n" and, in a way, tell the computer that the rest of the filename doesn't matter; I did it like this:
chantier = AZE
For ChiffreUnderscore = 1 To 4
nomfichier = "PKR_" & Chantier & "_" & ChiffreUnderscore & "*.xls"
with application.filesearch
For iSearch = 1 To .FoundFiles.Count ' the loop will continue as long as we find PKR files on the disk
If GetFileNameFromPath(.FoundFiles(iSearch)) = nomfichier Then ' if we find the desired file using a function created beforehand
Workbooks.Open Filename:=.FoundFiles(iSearch), UpdateLinks:=0, ReadOnly:=True
' CODE THAT WORKS on the PKR file I just opened, which in this case would first be called, for example, PKR_AZE_1__(comments).xls or PKR_AZE_1_(azerty).xls
end if
next iSearch
end with-
Re,
Attention: Filesearch is no longer supported in XL2007 and you risk portability issues if your application is installed on a computer with 2007
see this article
https://silkyroad.developpez.com/vba/classefilesearch/
here is the principle (I cannot test on a prototype) of a code that searches for the workbook while disregarding comments. Once found and opened, we exit the macro
Sub select_workbook() Dim list, cptr As Integer ReDim list(0) As String site = "HIJ" site = site & "_" Set dico = CreateObject("Scripting.Dictionary") 'establish the list of files starting with PKR_site path = "D\......" 'to be adapted ChDir path file = Dir("PKR_" & site & "*.xls") While file <> "" ReDim Preserve list(cptr) list(cptr) = file cptr = cptr + 1 Wend For index = 1 To 4 For cptr = 0 To UBound(list) If list(cptr) Like "PKR_ " & site & index & "*" Then 'check the syntax of the like Workbooks.Open Filename:=list(cptr) 'see if concatenating path & "\" & list(cptr) Exit Sub Next Next End Sub
-
-
Hello
path = "g:\ mimi\..... " 'directory path ChDir path fich = Dir("PKR_" & "*.xls") While fich <> "" 'code of what you want to do... fich = Dir Wend End Sub
--
Michel -
Hello,
I don't understand because my file stays empty even though I've checked that there are files with the correct name in the PATH in question...
Thank you for your reply.