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?
Posted by Lewis on November 21, 2001 10:29 AM
Try
Workbooks.Open Filename:="Workbookname.xls"
Lewis
Lewis Conquer Consultancy Services
Posted by Lewis on November 21, 2001 10:30 AM
Try
Workbooks.Open Filename:="Workbookname.xls"
Lewis
Lewis Conquer Consultancy Services
Posted by Chris Rock on November 21, 2001 10:35 AM
If the filename is different each time, what do you suggest?
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.
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
