Importing from Excel via Dialog Box

[Guest]

Hi,

I have seen the articles referencing
http://www.mvps.org/access/api/api0001.htm to call a dialog box, but I can't
seem to get it to work. It always asks me for a macro. At first I was using
the code below from a database I inherited, but it doesn't seem to work in
Access 2002/2003 file formats. That's when I found articles referencing the
API example. I think I just don't understand enough about VBA programming,
so any pointers would be helpful.


Thanks.
-------------

Function ImportMatrix()
Dim sFilename As Office.FileDialog

DoCmd.RunMacro "mcrDeleteMatrix"
DoCmd.SetWarnings (WarningsOn)

Set dlgOpen = Application.FileDialog(dialogtype:=msoFileDialogOpen)

With dlgOpen
.AllowMultiSelect = False
.Show
End With

If dlgOpen.SelectedItems.Count = 0 Then Exit Function
sPath = dlgOpen.SelectedItems.Item(1)

On Error GoTo ImportMatrix_Err

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"tblMatrix", sPath, True
DoCmd.SetWarnings True
MsgBox "Import Complete"

ImportMatrix_Exit:
Exit Function

ImportMatrix_Err:
MsgBox Error$
Resume ImportMatrix_Exit

End Function

 

[Guest]

You're right- at least in MS Access 2003 it doesn't work. Try this code
though, to make the msoFileDialogOpen work-

'from
http://www.microsoft.com/technet/scriptcenter/resources/officetips/aug05/tips0825.mspx

Function ImportMatrix()
Const msoFileDialogOpen = 1

Set objWord = CreateObject("Word.Application")

objWord.ChangeFileOpenDirectory ("C:\")

objWord.FileDialog(msoFileDialogOpen).Title = "Select the files to be deleted"
objWord.FileDialog(msoFileDialogOpen).AllowMultiSelect = True

If objWord.FileDialog(msoFileDialogOpen).Show = -1 Then
objWord.WindowState = 2
For Each objFile In objWord.FileDialog(msoFileDialogOpen).SelectedItems
Wscript.Echo objFile
Next

End If

objWord.Quit

End Function

Private Sub Command0_Click()
Call ImportMatrix
End Sub

THIS should work nicely. Just cut/paste it into a clickbutton's Click command.

Cheers,
Al