Solved: opening filedialog in excel 2000

[cchris_uk]

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:'

[vba]Sub mmm()

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

End Sub[/vba]
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]

Filedialog came in in Excel 2002 I believe so you need to use GetOpenFilename

[vba]

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

[cchris_uk]

Ah ok,
I wish the IT department would rollout a newer office suite!

Thankyou for the alternate script.

Chris

[cchris_uk]

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.

[vba]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[/vba]
Chris

[Bob Phillips]

Just delete it by default before importing

[vba]

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