PDA

View Full Version : Solved: opening filedialog in excel 2000



cchris_uk
02-15-2008, 03:52 AM
Hi,

I am trying to open a 'open file' dialog window using vba, but the following code (found on this site) gives me a 'runtime error 438:'

Sub mmm()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
MsgBox .SelectedItems(1)
End If
End With

End Sub
I looked in the Object Browser and could not see that application > filedialog listed; is this not available in Excel2000?

What I want to do is:
1. Open a filedialogue box so that the user can select a .xls workbook.
2. Import the 1st worksheet of that workbook as a worksheet called "raw_data"

Chris

Bob Phillips
02-15-2008, 03:59 AM
Filedialog came in in Excel 2002 I believe so you need to use GetOpenFilename



Sub OpenFiles()
Dim mpFilename As Variant
mpFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If mpFilename <> False Then
MsgBox "Open " & mpFilename
End If
End Sub

cchris_uk
02-15-2008, 04:05 AM
Ah ok,
I wish the IT department would rollout a newer office suite!

Thankyou for the alternate script.

Chris

cchris_uk
02-15-2008, 04:46 AM
Nice, this code now works, I call the script from a button on sheet 2.
It copies the 1st sheet of the opened file and places it before the sheet named "-----3".

My next question is, how do I make the imported sheet overwrite a sheet if one of the same name exists?

i.e. i want to be able to import the sheet if any changes are made, but dont want endless copies such as data, data(1), data(2) etc.

Sub mpImport_data()
Dim mpFilename As Variant
Dim mpWorkbookData As Workbook
Dim mpWorkbookCurrent As Workbook
mpFilename = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
Set mpWorkbookCurrent = ActiveWorkbook
Set mpWorkbookData = Workbooks.Open(mpFilename)
mpWorkbookData.Sheets(1).Copy mpWorkbookCurrent.Worksheets("-----3")
mpWorkbookData.Close
Sheets(2).Select
End Sub
Chris

Bob Phillips
02-15-2008, 05:16 AM
Just delete it by default before importing



Application.DisplayAlerts = False
On Error Resume Next
Worksheets("data").Delete
On Error Goto 0
Application.DisplayAlerts = True