Hi! I have a file which is kept on the C Drive. Each day, a different excel file is downloaded to a usb memory stick. The first file reads data from this file. The problem is, the drive letter keeps changing from E to F and the macro then crashes out.
The file on the memory stick always has the same name, so is there a macro to find the drive letter of this file and then use it in my existing macro?
Many thanks for anyone who can help!
Hello andyrads,
Welcome to the Form!
This macro will search all the local drives for the file, and returns either an empty string "" or the file name when the search is successful. Add your file folder path and the file name to the macro. These are marked in red. Copy this code into a standard VBA module.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi! And thanks for the quick reply. I've copied and pasted the code into my current module, but this causes a syntax error "end sub expected". I think I'm doing something wrong?
Hi Leith Ross! I've tried again and put the code into a standard macro. I now get an error message "Invalid outside procedure". Could you put the code above into a complete macro for me to fiddle with? Many thanks for your help.
Here is another method.
Please Login or Register to view this content.
Hello andyrads,
Sorry for the delay. I had to leave for awhile. The "Sub" statement should be "Function". I write more "Sub" procedures than functions and just automatically type "Sub". Also, I changed the code to start with drive "E", since "A-D" are generally hard drives.
Please Login or Register to view this content.
Hiya! And once again thanks for the reply. I can get the function to run by calling it from a cell within the spreadsheet. But the result only gives me the name of the file I'm looking for..."test.xls". The file I'm trying to find on the usb memory stick has no path (it's not contained within a folder on the stick), it is just saved on it. The path is E:\[name of file], or whatever drive letter is assigned to it.
Two things then, can I call the function from within a macro? and secondly, can this function be adapted to find the file and open it?
Many thanks for all your help.
Hello andyrads,
So, you need to be able to search for a given file name on all local disks that are removable and return the full path of the file if it is found. Did I miss anything?
Hi again! Yep, you're right. But what I need is to either open the file "text.xls" when it is found or to have the drive letter as say x$ so that I can use that to point to the right drive. Hope all that makes sense and many thanks for you help. Just one other thing. Having never used a funtion before, how do I call or run the function from within a macro?Originally Posted by Leith Ross
Many thanks for all your help.
OK, I cannot help you with the macro, but I would have a different solution. You wrote in your first post that the drive letter is always jumping between E and F.
Lets say you want it to be always E, then the next time it jumps to F:
Assuming you are using windows:
1. Press START - RUN (or alternatively Windows-Key+R)
2. enter "diskmgmt.msc" (without ")
3. hit run
4. select your USB stick
5. right clickk - change drive letter
6. select drive letter E from the list
7. OK
Done.
The next time your stick is connected to that USB port it will be on E as well.
Still, its just for your information, surely the macro helps you more
greets,
A2k
Hello andyrads,
Finding a file by name on any drive is something that comes up rather frequently. I decided to bite the bullet and write the code. This code makes use of the File System Object which is part of the Microsoft Scripting Runtime Library. This first appeared in Excel 2000. If your version of Excel is earlier than 2000, the code won't run.
The macro "FindFileNamed" has 2 arguments: the File Path and the File Name. The file path can be the root directory, like "C:\", a partial path which will check all sub directories, or a full path, or no path (an empty string). If no path is chosen then all local drives are checked. Successful search paths are saved in a Public Collection Object "PathsFound". The directory paths are available to all procedures because the object is public. The information only changes when the macro "FindFileNamed" is run.
Macro Code
ExamplesPlease Login or Register to view this content.
Setting a Reference in VBAPlease Login or Register to view this content.
- Open your workbook and then press ALT+F11 to open the VBE.
- Press ALT+T followed by the Enter key.
- Scroll down the list until you find Microsoft Scripting Runtime Library.
- Press the Space bar to select it, and press Enter
- Press CTRL+S to save the reference in the project
Last edited by Leith Ross; 06-15-2009 at 05:34 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks