CODEWITHSUNDEEP
vbams-access
user3242868
user3242868

Reputation: 27

Access VBA code to import excel file

I need an access vba code to import an excel file but I need the user to select which excel file. I'm very new to access vba.

Upvotes: 0

Views: 12820

Answers (4)

Sasa
Sasa

Reputation: 21

Try this.

Sub ExcelImport()
Dim intRet_B As Integer
Dim GetTableName As String
TableName = "table name" 'Put an access table name between "".

SetFile01:
With Application.FileDialog(msoFileDialogOpen)
    .Title = "Select the file" 'Title of dialog box
    .Filters.Clear
    .Filters.Add "Custom Excel Files", "*.xlsx, *.csv, *.xls"
    .AllowMultiSelect = False
    .InitialFileName = CurrentProject.Path
    intRet_B = .Show
    If intRet_B <> 0 Then
        GetTableName = Trim(.SelectedItems.Item(1))
    Else
        GetTableName = ""
        Answ = MsgBox("The file is not selected. If you want to select it, press Yes, No otherwise.", vbQuestion + vbYesNo + vbSystemModal, "File selection")
        If Answ = vbYes Then
            GoTo SetFile01
        Else
            MsgBox "Abort the process.", vbOKOnly + vbInformation + vbSystemModal, "Process Termination"
            DoCmd.Close
            DoCmd.Quit
            End
        End If
    End If
End With


End Sub

Upvotes: 1

mak&#233;man
mak&#233;man

Reputation: 25

Here is the code I like to use:

Dim SelectedFile    As String
Dim FilePicker      As FileDialog
Dim SQLdelete       As String

Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
FilePicker.AllowMultiSelect = False
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show

If FilePicker.SelectedItems.Count <> 0 Then
    SelectedFile = FilePicker.SelectedItems(1)

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tbl_Name", SelectedFile, True

    MsgBox ("The data has been successfully loaded")
End If

Note: tbl_Name is the name of the table you would like the excel data to be saved in.

Upvotes: 0

iveedee
iveedee

Reputation: 582

I assume you would need to give bit more detail about you what you have tried so far. However to start with following code can help.

Function File_dailog() As String

On Error GoTo catchError 
txtPath = ""
Set fso = CreateObject("Scripting.FileSystemObject")
Dim directory As String, fileName As String, total As Integer
Dim fd As Object
Set fd = Application.FileDialog(3) ' this will open the file dailog

With fd                            ' following are the properties of the file dailog
.AllowMultiSelect = False
.Title = "Please select the file."
.Filters.Clear
.Filters.Add "Excel 2010", "*.xlsx?" ' you can add more filters of the file for the users below

If .Show = True Then
txtPath = Dir(.SelectedItems(1))
End If
txtPath = fso.GetFileName(.SelectedItems(1)) ' fetching the file name
End With
File_dailog = txtPath                         'return value
exit_catchError:
  Exit Function
catchError:
  If Err.Number = 5 Then                      ' error handling if nothing selected by the user
    Exit Function
  End If

End Function

To import the excel you can use:

strTable = "temp"     ' name of table you want in your database
strFile = File_Dailog ' you will get the file name from user selection
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, strTable, strFile, True

I hope this helps.

Upvotes: 0

tekrat
tekrat

Reputation: 115

You'll have to have FileOpen Common Dialog Box open up. This will help you on that:

How to show "Open File" Dialog in Access 2007 VBA?

Here is the code to read in the Excel file:

ms-access vba - read from excel and also update that excel

Upvotes: 0

Related Questions