Import data into table from Excel using command button

[ccepaulb]

I'm looking for someone to help me with a solution to my problem of importing data into a data table.
What I'd like to do is have a command button on a form. When this button is clicked the records in a table are cleared out. Then I'd like for a browse window to come up to locate an Excel file. The user would select this file and the data would be imported into the data table that was just cleared.
Can this be done without too much trouble?

Thanks, Paul

 

[jebry]

Hi!

To delete all the records from a table all you need to do is run a delete query:

strSQL = "Delete * From YourTable"

CurrentDb.Execute strSQL

The following will allow the user to select an Excel file and it will import it into your table:

Dim fd As FileDialog
Dim FileOpen As Variant
Dim FileName As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)

FileOpen = fd.show = True

For Each FileName In fd.SelectedItems
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "YourTable", FileName
Next FileName
Set fd = Nothing

hth



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[ssatech]

1) Copy and Paste these codes in your module:
---------------Begin Code -------------------------------
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hWndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Public Function ImportTextFile(Optional strInitialDirectory As String, Optional strDialogTitle As String) As String

Dim FileFilter As String, FileboxTitle As String, InitialDirectory As String
FileFilter = "MS Excel Files (*.xls)" & Chr(0) & "*.xls" & Chr(0)

If strDialogTitle = "" Then
strDialogTitle = "Import File"
End If

If strInitialDirectory = "" Then
InitialDirectory = "C:\"
Else
InitialDirectory = strInitialDirectory
End If

ImportTextFile = GetFilebox(InitialDirectory, strDialogTitle, FileFilter)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, [highlight]"YourTableName"[/highlight], ImportTextFile, True, ""
MsgBox "Excel File successfully imported to your table"

End Function

Public Function GetFilebox(strInitialD As String, strTitle As String, strFilter As String) As String

On Error GoTo ERR_GetFilebox

Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hWndOwner = Screen.ActiveForm.Hwnd
OpenFile.lpstrFilter = strFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = strInitialD
OpenFile.lpstrTitle = strTitle
OpenFile.flags = 0
Dim strTemp As String

lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
GetFilebox = ""
Else
strTemp = Left$(Trim(OpenFile.lpstrFile), InStr(OpenFile.lpstrFile, vbNullChar) - 1)
If Right$(strTemp, 4) = ".xls" Then
GetFilebox = strTemp
Else
GetFilebox = strTemp & ".xls"
End If
End If

Exit_GetFilebox:

Exit Function

ERR_GetFilebox:

Select Case Err.Number

Case 2475
OpenFile.hWndOwner = 0
Resume Next
Case Else
MsgBox "Error : " & Err.Number & vbCr & vbCr & Err.Description
End Select

Resume Exit_GetFilebox

End Function

---------End Code --------------------------------------

2) Create a Delete Query of the table you want clear and give it name _____

3)On your command button this:

Private Sub cmdButtonName_Click()

DoCmd.SetWarnings False
DoCmd.OpenQuery [highlight]"NameOfDeleteQuery""[/highlight], acNormal, acEdit
DoCmd.SetWarnings True
Call ImportTextFile


End Sub