Opening a workbook

[dragon]

Hi there

This is my first post...I hope someone can help. It's a simple problem really, but I just can't seem to see the solution.

I am trying to open an Excel file using VBA, where the file location is fed to the procedure using a variable called "location" and VBA needs to look for a file whose name will end in "List Directory". VBA doesn't seem to like me using an asterisk in the first part of the filename to signify that it can be wild.

The line of code I have looks like...

Workbooks.Open location & "\*List Directory.xls"

Any help would be much appreciated.

[gibbo1715]

If i understand correctly the following should work for you


Workbooks.Open location & "\" & "List Directory.xls"

****Not sure what the font bit is, should read Workbooks.Open location & "" & "List Directory.xls"

[dragon]

Thanks gibbo1715 - but that doesn't work. My question was probably not clear...

I need to have an asterisk before "List Directory" as the filenames will actually all be different depending on which directory or sub directory I am looking at. Therefore the first part of the filename needs to be wild. For example, in folder 01003, the list directory file would be called "01003 - List Directory", in folder 01005 the list directory file would be "01005 - List Directory", and so on.

Thanks

[ALe]

I'm not sure I got the problem.
If "Location" contains the first part of the name of the file (for example 01003 -, 01005 -) and its number of character is fix, you could extract it and then put it at the beginning of the name of file.

For example, if the number of characters of the folders is eight:

firstpart = Mid(Location, Len(Location) - 8, 100)
Workbooks.Open location & "/" & firstpart & "List Directory.xls"

Hope it could be of use

[Bob Phillips]

sfile = Dir(location & "\*List Directory.xls")
If sfile <> "" Then
Workbooks.Open sFile

[dragon]

Thanks for your efforts ALe and xLd but....

ALe - "Location" contains the path to the file. It does not have a fixed number of characters, path can be different every time.

Each folder has a file listing all the files in that folder. the first part of the file name is made up from the name of the folder it resides in, and the second part of the file name is "List Directory". Therefore if you look in the folder S:\01001 - Contaminated land\01003 - 5 year strategy, the directory list file in this folder you would want to open will be called "01003 - 5 year strategy list directory".


xLd - Your solution also has an asterisk in it, just like my initial effort, and i can't get it to work. VBA does not appear to like concatenating a variable with a string containing an asterisk. If I remove the asterisk and simply look for a file called "list directory" there's no problem (as long as the file exists). But this would mean that all the directory list files would need to have the same name regardless of which folder they were in - and i feel uneasy about doing that.


Sorry - but this appears to be a bigger problem then it initiallt appeared to be. Help....

[Bob Phillips]

xLd - Your solution also has an asterisk in it, just like my initial effort, and i can't get it to work. VBA does not appear to like concatenating a variable with a string containing an asterisk. If I remove the asterisk and simply look for a file called "list directory" there's no problem (as long as the file exists). But this would mean that all the directory list files would need to have the same name regardless of which folder they were in - and i feel uneasy about doing that.

My code works fine. Dir accepts * in the string, indeed one of the examples in help uses a * wildcard

MyFile = Dir("*.TXT", vbHidden)

[ALe]

Ok Dragon. Try this. If it doesn't work contact me again. we'll find another way.


Dim stLocation As String
Dim stFirstPart As String
Dim MyFileName As String
Dim k As Integer
k = InStrRev(stLocation, "\") 'get the last \ of location
stFirstPart = Mid(stLocation, k + 1, 100) 'get everything after the last \
MyFileName = stLocation & MyFileName & "List Directory.xls" 'create the path string
Workbooks.Open MyFileName 'open

if location finishes with \, you have to cancel it before creating the path string

[mdmackillop]

Similar to XLD's solution, this works for me.


Sub Openit()
Location = "C:\AAA\"
MyFile = Dir(Location & "*est.xls")
Workbooks.Open Location & MyFile
End Sub

[dragon]

Thanks ALe - I finally managed to get everything working by slightly modifying the last two lines of your code above...


MyFileName = stLocation & "\" & stFirstPart & " List Directory.xls" 'create the path string
Workbooks.Open MyFileName 'open

Thank you to everyone who replied, I'm going to mark this as solved now.

:rotlaugh: