Hi Dan,
You don't need to understand the code in order to use it. It's
bombproof stuff that's been used in thousands of applications. All you
need to do is to modify the sample GetOpenFile() function to do what
you need.
As it appears on the web page, it opens the dialog at a preset folder
(varDirectory) and lets the user select an Access database, then
returns the path of the selected file. What you need is something like
this:
Function GetWorkbookSpec(Optional varDirectory As Variant, _
Optional varTitleForDialog As Variant) As Variant
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
' Use Flags to specify that the chosen file must already
' exist, don't change directories when you're done
' Also, don't bother displaying
' the read-only box. It'll only confuse people.
lngFlags = ahtOFN_FILEMUSTEXIST Or _
ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
If IsMissing(varDirectory) Then
varDirectory = ""
End If
If IsMissing(varTitleForDialog) Then
varTitleForDialog = ""
End If
' Define the filter string to control which files are
' displayed in the dialog.
strFilter = ahtAddFilterItem(strFilter, _
"Excel (*.xls)", "*.XLS")
' Display the File Open dialog to get the path
varFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
InitialDir:=varDirectory, _
Filter:=strFilter, _
Flags:=lngFlags, _
DialogTitle:=varTitleForDialog)
If Not IsNull(varFileName) Then
'Trim the trailing Null character from the C-style
'string returned by the API function
varFileName = TrimNull(varFileName)
End If
GetWorkbookSpec = varFileName
End Function
Then, assuming that the path is in Me.txtFile, your import code would
be something like this:
Private Sub cmdImport_Click()
Const MSG_TITLE = "Importing Adj Data"
Dim strFileSpec As String
strFileSpec = GetWorkbookSpec(Me.txtFile, _
"Please select the file to import")
'At this point we know (because of the Filter and Flags we put
'in GetWorkbookSpeck()) that strFileSpec contains a valid path
'to a .xls file, so we can go ahead and see if it imports
On Error Resume Next
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel97, "Adj", strFileSpec, True
If Err.Number = 0 Then
MsgBox strFileSpec & " imported.", _
vbOkOnly + vbInformation, MSG_TITLE
Else
MsgBox "Problem importing " & strFileSpec & "." & vbCrLf _
& "Error " & Err.Number & ": " & Err.Description, _
vbOkOnly + vbExclamation, MSG_TITLE
Err.Clear
End If
End Sub
Hi John:
How can this code be modified to change just the file name - not the entire
path?
Private Sub cmdImport_Click()
On Error Resume Next
Dim strMsg As String
Dim strFile As String
strFile = Nz(Me.txtFile, "")
'strFile = "G:\AFFX_common\Proj - FI
2007\AF_FX_Prod_Reconciliation\09-2007 Prod Reconciliation\Adj.xls"
If Dir(strFile) <> "G:\AFFX_common\Proj - FI
2007\AF_FX_Prod_Reconciliation\09-2007 Prod Reconciliation\Adj.xls" Then
strMsg = "You must enter a path for the file" 'named Adj.xls"
MsgBox strMsg, vbExclamation, "Error"
Me.txtFile.SetFocus
Else
'Me.lblMsg.Caption = "Import records."
strMsg = "Enter the path for the file that will be imported: " &
vbCrLf & strFile
'If MsgBox(strMsg, vbQuestion + vbYesNo, "Start Import") = vbYes Then
CurrentDb.Execute "DELETE FROM [Adj]"
'strMsg = ProcessFileImport(Me.txtFile, "Adj")
'MsgBox strMsg, vbInformation, "Finished"
'Me.lblMsg.Caption = strMsg
'DoCmd.RunMacro "ImpCompFile", 1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"Adj", Forms!imp_comp_file!txtFile, True
DoCmd.OpenTable "Adj"
'DoCmd.MoveSize 100, 100, 9500, 6500
'Else
'Me.lblMsg.Caption = "Import Cancelled."
'End If
End If
End Sub
Private Sub Form_Load()
On Error Resume Next
'Me.txtFile = CurrentProject.Path & "\Adj.xls"
Me.txtFile = "G:\AFFX_common\Proj - FI
2007\AF_FX_Prod_Reconciliation\09-2007 Prod Reconciliation" & "\*.xls"
End Sub
****
Thanks,
PS: I could not understand the code you gave me.
:
Hi John:
Thanks! I will see...
Dan
:
I'd be inclined to use the standard Windows File Open dialog, set to
open at the folder in question. This is less trouble than crafting a
custom solution, and in most cases easier for the user.
There's code at
http://www.mvps.org/access/api/api0001.htm to invoke
the dialog. Use the
ahtCommonFileOpenSave()
function, and pass the path to the folder in the InitialDir argument.
Hi:
Can you please tell me how can I import an Excel file using a form where the
user can change the file name - the path(folder) is the same; so wehn
clicking a button, user get a msg. to change the file name; the path up to
file name, will be deafulted in a test box in the form?!
Thanks a lot,
Dan
