VBA - file search

Posted by Mindy on August 23, 2001 10:58 AM

Hi,
If I open an Excel file (named it: fileA) with macro. The macro is going to look for another 2 excel files and copy the data to the fileA.

Now, my questions:

1. If only file name is given, how should the code to be in VBA to search the window directories?

2. Will it be possible to have a control or form to list the driver and directories?
( I checked the tool bar now which doesn't have driver icon and directory list icon) Any idea of what to do?

Thank you for the help, any suggestion will be real useful, too.

Posted by Barrie Davidson on August 23, 2001 11:27 AM

Mindy, you can open a file using something like this:
Sub YourSub()
Dim FileName As String
'Get the file to open
FileName = Application.GetOpenFilename
'Open the file
Workbooks.Open FileName:=FileName
'the rest of your macro....
End Sub

If you need any help just let me know (via this board since the Yahoo e-mail address is for home).

Regards,
Barrie

Posted by MIndy on August 23, 2001 11:54 AM

Hi Barrie
Thank you for the great tip. However, the two excel files might be located in different directory and with different names. The input paramaters will be needed.

In stead of asking user to type in
ex: c:\My Documents\File1.xls

I was thinking if they can click on the directory (something like window explorer) then, the file names will be retrieved as input.

I think one more way is the seach for the file name in all directory. Not a great idea since it might take a while.

Any suggestion? and thank you very much again

Mindy

Posted by Barrie Davidson on August 23, 2001 11:58 AM

Hi Mindy. How about using two variables (one for each file you want to open)? You can preface the FileOpen box with a message box asking the user to select the file to open, so they know what is happening.

Does this help?
Barrie

Posted by Mindy on August 23, 2001 12:20 PM

Hi Barrie,

This is definitely real helpful. However, how do I code for the FileOpen box to list all the drivers and directories in the box for users to select? Cause it's not under Excel.application

Thank you very much... very fast respond... :) thanks

Mindy

Posted by Barrie Davidson on August 23, 2001 12:26 PM

Mindy, the FileOpen command will open a standard Open DialogBox (just like the box you get when you select FileOpen from the main menu). The syntax I posted will show all files, not just Excel files. Whatever file the user selects is then opened. Paste this in a blank workbook's code and try for yourself.

Sub Test()
Dim FileName As String
MsgBox ("Select file to open")
FileName = Application.GetOpenFilename
Workbooks.Open FileName:=FileName
End Sub

It will open whatever file you select to open.

Barrie

Posted by MIndy on August 23, 2001 12:33 PM

Thank you very much..

Hi Barrie

Thank you. Thank you very much. The FileOpen box is definitely new for me and which is real cool. Then, I will not even worry to create its own file directory list control now. This is definitely real helpful.

Thank you for all the help..I am very very appreciated.

Mindy :)

, the FileOpen command will open a standard Open DialogBox (just like the box you get when you select FileOpen from the main menu). The syntax I posted will show all files, not just Excel files. Whatever file the user selects is then opened. Paste this in a blank workbook's code and try for yourself. Sub Test()