i would suggest making up your own form to appear
as a dialogue box.set the forms modal property to yes
pop up to yes,bordertype to dialogue
put a command button and a text box on this form
if you have a lot of excel files i would suggest
using a combo box filled with the file names
in the form where your macro is triggered
replace your macro name with an event procedure
in the event procedure paste this code and replace the red text
''''''''''''''''''''''''''''''''''''''''
DoCmd.OpenForm "the daialogueformname"
'''''''''''''''''''''''''''''''''''''''''
file named: d:/TGreen_V1/Master~1.xls
i have assumed the filename you want to type in would be Master in the
following code
on your pop up form command button use the onclick event to
fire another event procedure
in the event procedure paste this code and replace the red text
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim CtlName As Control
Set CtlName = Me![textboxnamethat holds excelfilename]
'test to see if there is a name
If IsNull(CtlName) Then
Beep
MsgBox "Excel File Name Required", vbOKOnly, "File Name Required"
Exit Sub
End If
On Error GoTo HandleError:
Dim ExcelFileName As String
Dim StrPath As String
ExcelFileName = CtlName
StrPath = "d:/TGreen_V1/" & ExcelFileName & "~1.xls"
DoCmd.TransferSpreadsheet acImport, 8, "thenameofthetabletoimportto", StrPath, False, ""
MsgBox "Transfer Complete", vbOKOnly, "Congralulations"
ExitHere:
Exit Sub
DoCmd.Close acForm, "popupformName"
HandleError:
MsgBox "No Excel File Found At " & CtlName
Resume ExitHere:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
i hope this is not to confusing
