Automating Import process...

[ThriftyFinanceGirl]

I am automating an import of a text file each month and I need to let the
users pick the file... This is the function that I have so far BUT I don't
know how to call the windows common dialog control to allow them to set the
path. Any help would be appreciated....(Obiviously the code below the
location is hard coded and that is what I'm trying to fix.)

Function R5561115Import()
On Error GoTo R5561115Import_Err

DoCmd.TransferText acImportDelim, "R5561115ImportSpecs",
"R5561115Import", "C:\Documents and
Settings\kjarre1\Desktop\R5561115_06192009.txt", False, ""


R5561115Import_Exit:
Exit Function

R5561115Import_Err:
MsgBox Error$
Resume R5561115Import_Exit

End Function

 

[Steve Sanford]

If you want to call the standard Windows File Open/Save dialog box, see:

"Call the standard Windows File Open/Save dialog box"
http://mvps.org/access/api/api0001.htm


This is what I use.

HTH

 

[ThriftyFinanceGirl]

Okay, so I looked at the page that you referred to and it looks like this is
a whole lot of code to just grab the PATH and FILENAME so that I can put it
in my "transfer txt file" code. Sorry that I don't understand it all, but
when I copied it into my project the very first Declare Function has an
error....

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean


error: User-defined type not defined.

This was just when I was running the debug/compile so that I could test it.

I really need to grab the path and filename so that I can put it into my
transfer.txt code. Could someone either help explain the previous response,
or give me another option?

 

[Steve Sanford]

This is what I did:

Create a new standard module.... I named mine "modOpenSaveFile" (no quotes)

From here > http://mvps.org/access/api/api0001.htm

Copy everything between

'***************** Code Start **************

and

'************** Code End *****************

and paste it in the module you just created.

Change your function to look like:

'------------------------------------------
Function R5561115Import()
On Error GoTo R5561115Import_Err

Dim FileToOpen As String
Dim strFilter As String

'-----------------------------------------------------
'Open the OpenFile Dialog box to get the .csv filename
'-----------------------------------------------------
strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
FileToOpen = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

If nz(FileToOpen,"") = "" Then
MsgBox "No file selected!! Aborting"
Exit Sub
End If

'FileToOpen now holds the path & file name of the file to transfer

DoCmd.TransferText acImportDelim, "R5561115ImportSpecs",
"R5561115Import", FileToOpen, False, ""


R5561115Import_Exit:
Exit Function

R5561115Import_Err:
MsgBox Error$
Resume R5561115Import_Exit

End Function
'------------------------------------------

Post back if you still have problems


HTH