0

I want to search and open an excel file named remote.xls I want to store that excel file into a variable, so i can use it further But the problem is that I dont know the exact path because for every user, the location is different. But I know for certain that it is in C:\ drive. How can I find it using VBA?

I know 2 methods :

1) using dir

2) using application.FileSearch

But the 2nd one is closed now. I am left with the first one..but I am not sure..how to use it there are several other threads. I have tried those method but have not been successful.

Why different? I want to search with the exact file name and not using wildcards

Anirudh Chauhan
  • 111
  • 2
  • 9
  • 1
    Possible duplicate of [Cycle through sub-folders and files in a user-specified root directory](https://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) – CLR Jul 14 '17 at 10:40
  • I dont want to find the workbook using wildcards, since I know the exact file name. kindly understand the issue first,before commenting – Anirudh Chauhan Jul 14 '17 at 10:54
  • Well, I appreciate the question may be *slightly different*, but the answer is the same. And setting a wildcard *to the filename you want* means you will get the file you want. – CLR Jul 14 '17 at 11:03
  • 1
    Also, in your recent questions, you've posted some code and asked what's wrong. That's exactly what S.O. is about. For this question though you've asked an open question 'How can I find it using VBA?' - and a short answer to a question like that would be 'Google it'. – CLR Jul 14 '17 at 11:05
  • If the location of file is unchanged, then it is better to use the exact location for each computer. It would be inefficient to search for a file each time when you know where it is. The other method could be putting the files in the same directory so that you don't have to change any code from any computer. Use `ActiveWorkbook.Path` (or you can use a subfolder this way) – Tehscript Jul 14 '17 at 12:18
  • When you say it's in a different location - is it the same *type* of location? e.g. they're in the users personal folder or on the users desktop (such as `C:\Users\darren.bartrup-cook`)? I ask as these are special folders which can be referenced easily with a little code. – Darren Bartrup-Cook Jul 14 '17 at 13:44
  • @DarrenBartrup-Cook : yes..exactly... so how do you think i can make it work? – Anirudh Chauhan Jul 14 '17 at 16:17
  • 1
    It depends on which special folder the files are in... you could use `environ("userprofile")` to get something like `C:\Users\darren.bartrup-cook` or `environ("localappdata")` or `environ("appdata")` or `CreateObject("WScript.Shell").specialfolders("Desktop")` https://stackoverflow.com/questions/7751464/language-independent-way-to-get-my-documents-folder-in-vba-excel-2003 – Darren Bartrup-Cook Jul 14 '17 at 16:26

0 Answers0