Hi,
is there a VBA way to determine an unknown path in which a KNOWN WB is located !?
Assume a WB named: 1X1.xls Located at C:\TEMP\
[In case the user uses more than one partition and/or more than one HD - it might alse be located at: D:\TEMP\
The known open command for a known file AND Path is:
My question is - how can one open such a WB, WITHOUT knowing its location throughout his HD !?
Please Login or Register to view this content.
Thanks, Elm
Last edited by ElmerS; 06-22-2009 at 04:01 PM.
I thought maybe the dir command except it appears that it will cause an error if the drive doesn't exist.
Please Login or Register to view this content.
The dir function will return the filename if it exists or an empty string if it doesn't. The only problem is that it appears that it errors out if the drive doesn't exist. I guess you could disable or trap the error.
Or you could use the filesystem object. It's pretty easy to use but you'll need to set a reference to microsoft scripting runtime (tools - references).
Then
Should work. And it doesn't cause an error if the drive doesn't exist.Please Login or Register to view this content.
You'll need to replace the comment lines with the applicable open workbook code or set the filepath, etc.
Thanks, Cyclops.
I don't think you understood my question.
As I mentioned before the PATH is unknown.
If it is too difficult to find the KNOWN file in several Hard-Disks, let us Concentrate in one HD, say disk C:
In that HD there could be 587 directories/Sub-Directories and the exact path, to the file, is unknown.
I could search for it with "My computer" and then type the path into the open command.
My request is to eliminate the use of "outside" Accessories.
I can understand that it might not be an easy task to open a file located in an unknown path - but I do hope there is a VBA way to accomplish that.
Elm
Last edited by ElmerS; 06-18-2009 at 03:35 AM.
Oh, OK. I thought the directory structure was essentially the same and you were just looking to find the drive.
Off the top of my head, I don't know any easy way to do this. You could use the FileSystemObject to get all directories and check each one for the file but that would probably take a while to run. I would think you could do a search using an API call but I'm not sure on that. I'll do a little digging and let you know if I find anything out.
Actually it looks like Application.FileSearch might do what you want. I'm not sure of the syntax though because I've never used it.
You would probably need to use the filesystemobject to get all the drives then search each one for the file. It looks like filesearch will look in subfolders so the code shouldn't be too bad.
If you are using 2007, you can't use Application.Filesearch unfortunately.
Remember what the dormouse said
Feed your head
In 2003 is there also a limit to how many subfolders FileSearch will process?
If I tell it to start at "C:\Documents and Settings" it doesn't find anything, but if I start at "C:\Documents and Settings\MyUsername" then it does?!?
If you find the response helpful please click the scales in the blue bar above and rate it
If you don't like the response, don't bother with the scales, they are not for you
Not that I'm aware of. Works fine on my PC from there (though I don't have a lot of subfolders in docs and settings.)
romperstomper,
Would you be so kind to present the complete code that searches Drive C for: 1X1.XLS and at the end returns the correct path into a variable to be used (inserted) into the Application.Workbooks.Open command.
Thanks, Elm
Out of interest then, what does the following macro report for you, and does it seem reasonable?
For me it reports 3 files found, although I know there are significantly more. Is there possibly something stopping the search from traversing the "documents and settings" folder? If I start it inside my user folder it works fine!Please Login or Register to view this content.
This would return an array of filenames (in case there's more than one match) using Filesearch:
Please Login or Register to view this content.
it returns 527 for me. Does it make any difference if you specify "C:\" rather than just "C:" as a matter of interest?Originally Posted by Phil_V
No, judging from the MsgBox at the end the FileSearch is appending that trailing slash anyway.
Just did a little experiment by moving an Excel file around.
It can find the file fine on most of the PC, but if I move the file to any of the following folders or lower it doesn't find it:
C:\Documents and Settings\All Users
C:\Documents and Settings\Default User
C:\Documents and Settings\Phil
C:\Documents and Settings\LocalService
C:\Documents and Settings\NetworkService
It does however still find it within:
C:\Documents and Settings\Administrator
So who knows, maybe some strange setup on this PC, I just wish I knew what
Phil,
I mentioned right in the beginning that I would like to search for a known file.
When I replace your: *.xls with: 1x1.xls it reports the Parent drive which is, in fact, C:\ - BUT it does not present the whole path (incl. the sub-folder) which is, in my case, C:\TEMP\.
I would like to emphasize that there is only one 1x1.xls located in derive C
Elm
Sorry Elm, I got disctracted with why code I KNEW should work, wasn't working on my PC. The following function should work for you:
Call it like this:Please Login or Register to view this content.
If the file is not found then file_location will be "Not Found"Please Login or Register to view this content.
Due to the line ".FileType = msoFileTypeExcelWorkbooks" this will only find Excel workbooks, if you wanted to let it find any file you specified simply remove that line.
Phil,
This was really a "BINGO" and it works fine despite the fact that I had to change:
.Filename = "filename"
into:
.Filename = file_to_find
Thanks a lot,
Elm
Last edited by ElmerS; 06-19-2009 at 02:34 AM.
I must have been having a bad-brain moment - lol
Well spotted, glad it helped you out
Phil
Hi,
Although everything works fine - I wonder if there is a simpler Macro something without UDF - such as:
The above code is not completed and I am sure it will be a 'piece of cake' for you, guys, to make it work.Please Login or Register to view this content.
Thanks, Elm
Well, Guys,
No more suggestions as per the last code ?
Elm
Please Login or Register to view this content.
Entia non sunt multiplicanda sine necessitate
Thank you shg,
but I could not get it to work after combining the two Procs.
Please take a moment to combine them and show, me, the complete code.
Have a nice weekend,
Elm
I'll have to leave that to Phil. I use a different approach without FileSearch.
No matter what approach you are using as long as it works.
I managed to combine the codes and the result runs well but it takes about 20 seconds until it finds & presents the file.
[It is located in the 6th., sub-folders beneath the root directory of drive D]
If some of you can find a solution which will accomplish the task in much less time - I'll appreciate it.Please Login or Register to view this content.
Thanks, Elm
Last edited by ElmerS; 06-20-2009 at 09:06 AM.
Unless you can reduce the locations that the file might be, I don't think you'll be able to speed things up at all.
Assuming there is a way to differentiate between users, you may want to store the file locations for each user. Then, only run the code if the file isn't where you expect it to be.
Basic steps
1. try to open the file based on the user (user / filepath info stored on a worksheet)
2. If the file doesn't exist or there isn't an entry for the user, execute search
3. After executing search, save file location and user to the worksheet.
In theory, the only time the actual search code would be run is for new users. Unless they move the file...
Thanks a lot.
Elm
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks