Import Excel Sheet Command button

[kkocak]

I'm deisgning a data-entry form. My short term goal is to have a button that prompts a user to import a excel file, and have that excel file stored in a temp. table. I am struggling with the "DoCmd.TransferSpreadsheet." Am i unaware of a method or do i need another object. Any ideas? I am quite the rookie in VB fyi and some previous posts do not quite touch base with my question

 

[Lister]

You are on the right track.
You just need to finish the code, something like this..

Dim importfile As string
importfile = "C:\Temp\MyDoc.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "YOUR TABLE NAME HERE", importfile, True

Keep searching DoCmd.TransferSpreadSheet and you will find a heap of help.

 

[kkocak]

good help.....


I cannot quite find the proper syntax for actually opening a Pop-up dialog box that asks for the user to locate the file. For instance when you use the DoCmd.TransferSpreadsheet you have all the variables it runs with......one of those is the location of the file to import (i.e...... C:\Documents and Settings\This excel file.xls.....

is it possible to have code that prompts the user to locate the file.......?

 

[Moniker]

That's a FileDialog object. In the VBA code window, you'll need to go to Tools -> References, and then add the "Microsoft Office X.0 Object Library" (where the "X" is the version number) and that will expose the FileDialog object. From there, you need to instantiate it. Then use code to control the window, which will look something like this:

Private Sub cmdSelectExcelFile_Click()

    Dim dlg As FileDialog
    Dim FileSelected As String  

    Set dlg = Application.FileDialog(msoFileDialogFilePicker)
    With dlg
        .Title = "Select the Excelfile to import"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xls", 1
        .Filters.Add "All Files", "*.*", 2
        If .Show = -1 Then
            FileSelected = .SelectedItems(1)
        Else
            Exit Sub
        End If
    End With

End Sub

To see what all those properties and methods are, DIM the FileDialog object as shown with the name dlg, then type dlg. and scroll through the commands. Press F1 on each one that isn't clear to bring up help. Briefly, here's all I used:

.Title : File Dialog box's title
.AllowMultiSelect : False means you can't select more than one file at a time, True means you can select multiple files
.Filters : Lists the description and file extensions displayed in the File Type drop-down under filename in the file dialog
.Show : If it's -1, the user clicked OK. Otherwise, the user clicked Cancel or closed the dialog.
.SelectedItems : The full path and name of the file the user selected

 

[kkocak]

touche my friend....tocuhe

 

[Moniker]

Glad it worked out.

 

[anonymous1]

Lister & Moniker...just wanted to say thank you very much for the code you both posted. i have been trying to figure out how to allow someone to import a file by clicking a button & selecting the file for a while and your code worked.

Thanks again!

 

[HelpTheNewbie]

FileDialog Box

Dear Moniker:

Thank you so much for all of the information you have posted - it has been extremely helpful. I have one quick question, which you can chalk up to my Access ignorance. Can you please elaborate on "will expose the FileDialog object. From there, you need to instantiate it."

Where is this "exposed" and how can I "instantiate it?"

Thanks in advance,

Newbie

 

[namliam]

Exposed: Meaning you can use it in VBA

Instigating it: Declare and use it. Like the sample Moniker gave.

 

[HelpTheNewbie]

That's what I thought. Maybe then I don't understand how to add the "Microsoft Office 2.0 Object Library". I went to Tools > References > and then checked the box next to this option...

When I try and declare the variable "As FileDialog" I get a compile error. Is there a step I am missing?

Thanks again for your help.

 

[namliam]

2.0 ?? You sure?? You are still using Access 97??

I made sure and tested it, it works just fine ....

 

[HelpTheNewbie]

No. I am running 2003...

I checked again. I apologize - i found 11.0 and now everything seems to be working.

Thanks so much for your help!

 

[Sareny]

FileDialog - User Defined Type not defined

I followed the code example and made sure that the right reference was referenced, but I get an error when I click on my button (Compile Error - User Defined Type not Defined).

Can you please help me find out why this is not working?

Code:
Private Sub cmdBrowseforPath_Click()
Dim dlg As FileDialog
Dim strFileName As String

Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "All Files", "*.*", 2
If .Show = -1 Then
strFileName = .SelectedItems(1)
Else
Exit Sub
End If
End With
End Sub

See attached for References that are selected.

Thank you.

 

[Sareny]

Never Mind

Never Mind. I found out that I was looking for the Microsoft Office 11.0 reference and not the Microsoft Access 11.0 reference.