Creating a Macro replacing data in Table with data in an Excel file

[Shaun]

This is what I'd like to do:

Have a button on my form that will import an excel file replacing the
data in my Access Table.

On my form I'd like to have a text box listing the file path of the
excel file. I want the user to have the ability to change the file path
through this text box. This way, if the excel file is moved the end
user can easily update the file path on the form.

Any Ideas?

 

[Klatuu]

Here is a site that will give you the ability to open the common dialog box
without have to use an ActiveX control. It will allow the user to select the
file to open:

http://www.mvps.org/access/api/api0001.htm

Here is an example of how to use it:

Private Sub CmdImport_Click()
Dim varGetFileName As Variant

On Error GoTo ImportPipeline_Err

'Get the file to import
Do While True
varGetFileName = ahtCommonFileOpenSave(ahtOFN_OVERWRITEPROMPT, _
"\\rsltx1-bm01\busmgmt\Vought " & Me.txtCurrYear & "\Pipeline\", _
"Excel Spreadsheets (*.xls) *.xls", , _
"xls", Me!txtCurrYear & " " & Left(Me!cboPeriod.Column(1), 3) _
& " Pipeline.xls", "Select Pipeline", , True)
If varGetFileName = "" Then
If MsgBox("Cancel Import?", vbQuestion + vbYesNo, "Import
PipeLine") _
= vbYes Then
Exit Sub
End If
Else
Exit Do
End If
Loop

DoCmd.TransferSpreadsheet acLink, 8, "PipelineLink", _
varGetFileName, True, "80%!A3300"

MsgBox "Import Complete", vbOKOnly, "Import Pipeline"
ImportPipeline_Exit:
On Error Resume Next
DoCmd.DeleteObject acTable, "PipelineLink"
Exit Sub
ImportPipeline_Err:
If Err = 3011 Then
MsgBox varGetFileName & " is not a Pipeline File" _
& vbNewLine & "or is incorrectly formatted", vbCritical +
vbOKOnly, _
"Import Error"
Else
MsgBox Err.Number & " " & Err.Description, vbCritical + vbOKOnly,
"Import Error"
End If

End Sub

You can put this code in the Click event of the command button and it will
do what you want.

Put the code from the site above into a standard module per the instructions
so this code can call it.