Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

How do I get a File / Open prompt in VBA?

Posted by Chris Rock on November 21, 2001 10:03 AM
I need to import some data from several separate files using a simple Copy/Paste macro. However, I'll need to have a prompt to open the files I'll be using.

How can I do this using VBA?

Steps involved:
1. PROMPT for File / Open (starting in a specific directory)
2. Take Active file (the one just opened), and copy A2:R201.
3. Activate the workbook from where the macro was run.
4. Select the first empty cell in column A.
5. Paste.
6. Close workbook that was opened by the macro.
7. Repeat for other workbooks, pasting in the first empty cell in Column A.

I know how to do everything except have Excel prompt for a file/open. Anyone know how to do this?


Check out our Excel VBA Resources

Re: How do I get a File / Open prompt in VBA?

Posted by Lewis on November 21, 2001 10:29 AM


Try
Workbooks.Open Filename:="Workbookname.xls"

Lewis
Lewis Conquer Consultancy Services


Re: How do I get a File / Open prompt in VBA?

Posted by Lewis on November 21, 2001 10:30 AM


Try
Workbooks.Open Filename:="Workbookname.xls"

Lewis
Lewis Conquer Consultancy Services


Re: How do I get a File / Open prompt in VBA?

Posted by Chris Rock on November 21, 2001 10:35 AM

If the filename is different each time, what do you suggest?


Re: How do I get a File / Open prompt in VBA?

Posted by Craig on November 21, 2001 12:20 PM

If you want to prompt the user for each filename, you can try this;

filename = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If filename <> False Then
MsgBox "Open " & filename
End If

this is taken straight from the Excel online help.


Re: How do I get a File / Open prompt in VBA?

Posted by Lewis on November 21, 2001 4:19 PM


If you are using a series of workbooks surely you just have to carry out the suggested command, do what ever it is you require close the workbook then open workbookname2.xls.
I forgot to mention in my last message you have to put the full addresss of the file you are trying to open.

Lewis


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.