Open folder from a form and import Excel file

[patheo]

Hi!

Could someone help me on how to create a form that contains a button which can open a folder to select an excel (.xls) file that will be added to an access 2003 table ?

Thank you in advance.

 

[Ranman256]

usage:
vFile = UserPickFile2Save("c:\")

'put in a form

Public Sub SaveMyFile()
dim vRet

vRet = UserPickFile2Save("c:\")
If vRet <> "" Then
    docmd.transferspreadsheet .....vFile
End If

'put in a module

Public Function UserPickFile2Save(Optional pvDir)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
Dim vVal, vFile, vPath
Dim vRet
Dim fd As FileDialog


If IsMissing(pvDir) Then  pvDir = "c:\"
vPath = pvDir & vFile

'------------BE SURE TO SET THIS IN VBE menu,TOOLS,REFERENCES---
'reference  Microsoft Office XX.0 Object Library
'-----------------------------------
Set fd = Application.FileDialog(msoFileDialogSaveAs)    'msoFileDialogPicker
With fd
    .AllowMultiSelect = False
    .Title = "Save File AS"
    .ButtonName = "Save"

    '.Filters.Clear
    '.Filters.Add sDecr, sExt
    '.Filters.Add "Excel Files", "*.xlsx"
    '.Filters.Add "All Files", "*.*"
    
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    .InitialFileName = vPath
        
     If .Show = 0 Then Exit Function                      'There is a problem

     'Save the first file selected
   UserPickFile2Save = Trim(.SelectedItems(1))
End With
End Function

 

[Grumm]

Note that the code from Ranman256 is to save a file.
You need to change the filedialog into msoFileDialogFilePicker.
Here is an example :
https://msdn.microsoft.com/en-us/library/office/ff196794.aspx

 

[sneuberg]

Also be sure to take note of the comment in the filedialog code, i.e.,

'------------BE SURE TO SET THIS IN VBE menu,TOOLS,REFERENCES---
'reference Microsoft Office XX.0 Object Library

Or the compiler will complain about user defined types.

 

[patheo]

Very good, Am gona try that!
Thanx so much!

 

[jdraw]

If you are keen on learning how to import CSV and other delimited files into Access,
then here is a free video
by Steve Bishop that has all the details.

Good luck.

 

[Ranman256]

Oops.i forgot to include that.