Importing from Excel via Dialog Box




I have seen the articles referencing 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.


Function ImportMatrix()
Dim sFilename As Office.FileDialog

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

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

With dlgOpen
.AllowMultiSelect = False
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"

Exit Function

MsgBox Error$
Resume ImportMatrix_Exit

End Function


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


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

End If


End Function

Private Sub Command0_Click()
Call ImportMatrix
End Sub

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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question