VB Excel open file with incomplete name
Solved
Xixi
-
Papoum -
Papoum -
Hello,
I want to open a file for which I know the directory but not the full name.
Example: open the file D:\Group\Test280520081102.xls
Knowing that I only have one file in my directory that starts with "Test"
Thank you for your help
I want to open a file for which I know the directory but not the full name.
Example: open the file D:\Group\Test280520081102.xls
Knowing that I only have one file in my directory that starts with "Test"
Thank you for your help
Configuration: Windows XP Internet Explorer 6.0
17 answers
Euhhh almost for the first one, but actually Dir doesn’t seem to work apparently.
Sub TestDir()
Dim R As String
R = ThisWorkbook.FullName
R = Left(R, 8) & "*"
MsgBox R 'the truncated name of the workbook and path. OK returns D:\Group
MsgBox Dir(R) 'the full name of the workbook. KO returns nothing
End Sub
On the other hand....
Fich = "D:\Group\" & dir("D:\Group\Test1*") returns the correct file.
:-) :-) :-) :-) :-)
One more little question related to this ;-) since I’m trying to be tidy instead of using D:\Group\, I prefer to use .\ and then to open the workbook I believe you only need the .xls name so how do I eliminate the characters giving the file path to keep only the name knowing that this number of characters is fixed?
Thank you very much lermite
Sub TestDir()
Dim R As String
R = ThisWorkbook.FullName
R = Left(R, 8) & "*"
MsgBox R 'the truncated name of the workbook and path. OK returns D:\Group
MsgBox Dir(R) 'the full name of the workbook. KO returns nothing
End Sub
On the other hand....
Fich = "D:\Group\" & dir("D:\Group\Test1*") returns the correct file.
:-) :-) :-) :-) :-)
One more little question related to this ;-) since I’m trying to be tidy instead of using D:\Group\, I prefer to use .\ and then to open the workbook I believe you only need the .xls name so how do I eliminate the characters giving the file path to keep only the name knowing that this number of characters is fixed?
Thank you very much lermite
Hello, you should be able to manage with the DIR() function which will return the contents of a directory.
```html
try with..
dim Fich as string
Fich = Dir("D:\Group\Test") & "*"
open Fich
It works, I tested it.
See you.
--
Experience teaches more surely than advice. (André Gide) ```
dim Fich as string
Fich = Dir("D:\Group\Test") & "*"
open Fich
It works, I tested it.
See you.
--
Experience teaches more surely than advice. (André Gide) ```
```html
By doing
Fich = Dir("D:\Groupe\") & "*"
MsgBox Fich
I can see the files in my directory
Whereas
Fich = Dir("D:\Groupe\Test") & "*"
MsgBox Fich
Always returns *
Isn't DIR() supposed to list the contents of a directory, thus being given a directory as a parameter and not a directory + file prefix??
Otherwise, as a solution, can we loop through Dir("D:\Groupe\") & "*" and test if the beginning of the returned string matches the correct value?
For example:
in the directory, I have Test128052008 and Test227052008, I know I am looking for a file Test1********, I can probably compare the beginning of Fich to the string "Test1" right? ```
Fich = Dir("D:\Groupe\") & "*"
MsgBox Fich
I can see the files in my directory
Whereas
Fich = Dir("D:\Groupe\Test") & "*"
MsgBox Fich
Always returns *
Isn't DIR() supposed to list the contents of a directory, thus being given a directory as a parameter and not a directory + file prefix??
Otherwise, as a solution, can we loop through Dir("D:\Groupe\") & "*" and test if the beginning of the returned string matches the correct value?
For example:
in the directory, I have Test128052008 and Test227052008, I know I am looking for a file Test1********, I can probably compare the beginning of Fich to the string "Test1" right? ```
And while we're working on files, I have another question (yes, I'm a beginner lol)
When I close a file and I want to (or not) save, I do a File.close True (or False)
If I delete a sheet, I use a Delete, but the system asks me if I want to delete all the data from the sheet. How can I force this deletion? (no way to put True or False apparently)
Similarly, when I open a .xls file that has updates (linked to another file), it asks me if I want to update. But I don’t want to lol, how can I tell it before it asks?
Thank you
When I close a file and I want to (or not) save, I do a File.close True (or False)
If I delete a sheet, I use a Delete, but the system asks me if I want to delete all the data from the sheet. How can I force this deletion? (no way to put True or False apparently)
Similarly, when I open a .xls file that has updates (linked to another file), it asks me if I want to update. But I don’t want to lol, how can I tell it before it asks?
Thank you
You say there is only one file that starts with Test. If that's the case, please send me the only file that starts with Test but complete.
As for your other questions, I'll get back to you later.
EDIT:
I read your second message, couldn't you explain CLEARLY from the start, please? First there is one, then there are several, what is this mess... I'm not going to check every time...
EDIT2:
To prevent Excel from asking for confirmation...
Application.DisplayAlert = false
and to set it back to normal
Application.DisplayAlert = True
EDIT 3:
A=1
File = Dir("D:\Groupe\Test") & A & "*"
or
File = Dir("D:\Groupe\Test1") & "*"
But there must only be one.
And be careful!! with Dir the case matters.. Test is not equal to test.
--
Experience teaches more surely than advice. (André Gide)
As for your other questions, I'll get back to you later.
EDIT:
I read your second message, couldn't you explain CLEARLY from the start, please? First there is one, then there are several, what is this mess... I'm not going to check every time...
EDIT2:
To prevent Excel from asking for confirmation...
Application.DisplayAlert = false
and to set it back to normal
Application.DisplayAlert = True
EDIT 3:
A=1
File = Dir("D:\Groupe\Test") & A & "*"
or
File = Dir("D:\Groupe\Test1") & "*"
But there must only be one.
And be careful!! with Dir the case matters.. Test is not equal to test.
--
Experience teaches more surely than advice. (André Gide)
So I just tested everything ^^
File = Dir("D:\Group\Test1") & "*"
MsgBox File
It returns * and yet I have a file Test128051653 in my folder (and also a Test228051653 but that one should not be taken into account given the hard path) isn't that strange? :'(
Before all my instructions I tried Application.DisplayAlert = false and it crashes :(
Runtime error '438'
Property or method not handled by this object
Sniff :'(
File = Dir("D:\Group\Test1") & "*"
MsgBox File
It returns * and yet I have a file Test128051653 in my folder (and also a Test228051653 but that one should not be taken into account given the hard path) isn't that strange? :'(
Before all my instructions I tried Application.DisplayAlert = false and it crashes :(
Runtime error '438'
Property or method not handled by this object
Sniff :'(
I forgot the "S"
Application.DisplayAlerts = False
for the Dir I Re.Re control.
EDIT:
well, as said... I'm Re.Re checking
I even created a workbook with your name, and contrary to what I said there is no difference between uppercase and lowercase and IT WORKS GREAT
But there can only be one file with that name prefix
check that there are no errors in the directory, the drive letter, etc.
go to the file explorer and check everything.
even if there are several files that start with Test1 DIR returns the first one found....
One more thing, copy/paste the code I put to make sure you don't have any errors.
--
Experience teaches more certainly than advice. (André Gide)
Application.DisplayAlerts = False
for the Dir I Re.Re control.
EDIT:
well, as said... I'm Re.Re checking
I even created a workbook with your name, and contrary to what I said there is no difference between uppercase and lowercase and IT WORKS GREAT
But there can only be one file with that name prefix
check that there are no errors in the directory, the drive letter, etc.
go to the file explorer and check everything.
even if there are several files that start with Test1 DIR returns the first one found....
One more thing, copy/paste the code I put to make sure you don't have any errors.
--
Experience teaches more certainly than advice. (André Gide)
hello,
try to do this:
--
Patience is an art that requires practice
try to do this:
Option Explicit ' option to enable in VBA Tools menu | References: Check Microsoft Scripting Runtime (DO NOT FORGET) Const FolderPath As String = "C:\" 'the path to your directory Sub Open_file() ListFilesIn FolderPath End Sub Private Sub ListFilesIn(ByVal FolderName As String) Dim FSO As Scripting.FileSystemObject Dim SourceFolder As Scripting.Folder Dim File As Scripting.File Dim x As Long Set FSO = New Scripting.FileSystemObject Set SourceFolder = FSO.GetFolder(FolderName) x = 0 For Each File In SourceFolder.Files x = x + 1 Workbooks.Open Filename:=File Next File MsgBox File Set File = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub
--
Patience is an art that requires practice
Uh yeah, isn't there something less complicated? Or can you explain it to me? Sorry, I'm such a noob :-(
Regarding the DIR, it always returns * :-( I don't understand why it works for you, lermite :s is there an option to activate???
Regarding the DIR, it always returns * :-( I don't understand why it works for you, lermite :s is there an option to activate???
No, nothing to add, Dir returns a file or nothing at all if not found ("") but certainly not "*"
so I insist, you have an error either in the directory or in the file names.. it's up to you to find it, I'm not in front of your PC
EDIT:
Copy/paste this sub and press F5
But I think of one thing... Also try.. if it's not in the same directory
Fich = "D:\Group\" & dir(fich)
--
Experience teaches more surely than advice. (André Gide)
so I insist, you have an error either in the directory or in the file names.. it's up to you to find it, I'm not in front of your PC
EDIT:
Copy/paste this sub and press F5
Sub TestDir() Dim R As String R = ThisWorkbook.FullName R = Left(R, 8) & "&" MsgBox R 'truncated workbook name and path. MsgBox Dir(R) 'full workbook name End Sub
But I think of one thing... Also try.. if it's not in the same directory
Fich = "D:\Group\" & dir(fich)
--
Experience teaches more surely than advice. (André Gide)
To open the workbook, it is preferable, if not mandatory, to provide the path + name + extension, that is to say, the complete one.
Why,
It is not because you are working in a directory that it is the one that is active, so if you only give the name, the workbook you are trying to open will use the path of the current directory.
To make a directory active, you need to do a Chdrive and a ChDir. (see help) which does not explain it well, you must first do a ChDrive with the drive letter and then a ChDir on the directory.
See you!
EDIT:
For test 2, replace the line
R = Left(R, 8) & "*"
with
R = Left(R, 10) & "*"
In fact, it's because the \ was missing that it wasn't working.
--
Experience teaches more surely than advice. (André Gide)
Why,
It is not because you are working in a directory that it is the one that is active, so if you only give the name, the workbook you are trying to open will use the path of the current directory.
To make a directory active, you need to do a Chdrive and a ChDir. (see help) which does not explain it well, you must first do a ChDrive with the drive letter and then a ChDir on the directory.
See you!
EDIT:
For test 2, replace the line
R = Left(R, 8) & "*"
with
R = Left(R, 10) & "*"
In fact, it's because the \ was missing that it wasn't working.
--
Experience teaches more surely than advice. (André Gide)
Fich = Dir("D:\Groupe\Test1" & "*")
MsgBox Fich
It returns * and yet I have a file Test128051653 in my folder (and also a Test228051653, but that one shouldn't be taken into account given the hard path) isn't it weird? :'(
Isn't the command rather Fich = Dir("D:\Groupe\Test1" & "*")?
I'm not sure ^^ I'm not thrilled :D
I would like to be able to open my file using a relative path :$
In fact, the only constraint when launching the program is to have the related documents in the same folder as the main one (this allows me to set aside a history every month)
So, sure, with the methods where I hard-code the name D:\Groupe\Test1* it works, but it would be better to have .\Test1* knowing that my main file is certainly in the same Groupe folder *-)
File = ".\" & Dir(".\Test1*") 'this doesn't work, it returns .\
In fact, in the folder, we definitely have principal.xls from where the program is launched and Test130051610.xls that we need to open
the thing is we could very well be in D:\Groupe1\ or in D:\Groupe2\, the only constraint being to have principal.xls in this folder along with the related files.
It's a bit of a hassle actually :-(
I would like to be able to open my file using a relative path :$
In fact, the only constraint when launching the program is to have the related documents in the same folder as the main one (this allows me to set aside a history every month)
So, sure, with the methods where I hard-code the name D:\Groupe\Test1* it works, but it would be better to have .\Test1* knowing that my main file is certainly in the same Groupe folder *-)
File = ".\" & Dir(".\Test1*") 'this doesn't work, it returns .\
In fact, in the folder, we definitely have principal.xls from where the program is launched and Test130051610.xls that we need to open
the thing is we could very well be in D:\Groupe1\ or in D:\Groupe2\, the only constraint being to have principal.xls in this folder along with the related files.
It's a bit of a hassle actually :-(
Hello, I’d like to continue this discussion ;p
First of all, thanks for the code, it's very useful :)
So, let's imagine we have several files in a directory like test1, test2, test4012...
If we want to delete all of them, do we need to use a loop?
I tried this, it works, but I'm worried it might be a bit clumsy ^^'
Dim Fich As String
Fich = "C:\" & Dir("C:\test*")
Do While Fich <> "C:\"
Kill Fich
Fich = "C:\" & Dir("C:\test*")
Loop
Let me know your thoughts ;P
First of all, thanks for the code, it's very useful :)
So, let's imagine we have several files in a directory like test1, test2, test4012...
If we want to delete all of them, do we need to use a loop?
I tried this, it works, but I'm worried it might be a bit clumsy ^^'
Dim Fich As String
Fich = "C:\" & Dir("C:\test*")
Do While Fich <> "C:\"
Kill Fich
Fich = "C:\" & Dir("C:\test*")
Loop
Let me know your thoughts ;P