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%!A3:P300"
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.
"Shaun" wrote:
> 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?
>
>
Similar Threads
