File Location Prompt

[mccallen60]

Good morning,
Thanks in advance for reading this. I am a VB newbie. I found this code
online that allows me to import multiple txt files (with different names)
into a table in my Access 2003 database table. It works like a charm.
However, I want to modify this code to have it prompt me for the file
location of the txt files to import, then prompt me again to where I want to
send the txt files to archive. The code is as follows:

'This sub will import all text files @ C:\Import TXT files\ and
'move them to the C:\Archived TXT Files\ folder. This example imports
'delimited text files with the import specification named TextImportSpecs
'and the imported files do not have field names.

Public Function bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err

Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String

strFolderPath = "h:\Lunch & Learn Activities\FY 2008-09\2009-03\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, "Log Import Specification", "Combined
Logs", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "h:\Lunch & Learn Activities\FY
2008-09\2009-03\Imported\" & objF1.Name 'Move the files to the archive folder
End If
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing

bImportFiles_Click_Exit:
Exit Function

bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit

End Function

Any help is greatly appreciated. Thanks again. Kevin

 

[bismuth83]

You'd probably want to use the folder dialog window, which lets you
browse directly for the folders. There's sample code here:
http://www.mvps.org/access/api/api0002.htm

 

[mccallen60]

Hi bismth83,
Thanks for your reply. But I think this is way over my head. I am too new at
VB to know how to set this up.
Thanks. Kevin

 

[bismuth83]

If you're still looking for a method to use, there aren't many changes
you need to make to the code I linked (http://www.mvps.org/access/api/
api0002.htm).

When you're in code view, go up to the Insert menu and select Module.
This will create an object called Module1 under Modules in your
project explorer on the left, and open up a blank code area. Select
all the lines on the linked page between and including:
' *** Code Start ***
and
' *** Code End ***
Paste this into the new module. No changes need to be made.

Now, whenever you want to prompt for a file location, use the
BrowseFolder() function. This will give you the select location, or
an empty value "" if the user presses Cancel or closes the window.
Your updated code would be as below (I chose to exit the function if
no folder is selected).

Replace:
Dim strFolderPath As String
With:
Dim strFolderPath As String, strImportPath As String

Replace:
strFolderPath = "h:\Lunch & Learn Activities\FY 2008-09\2009-03\"
With:
strFolderPath = BrowseFolder("Select a folder to import from")
If strFolderPath = "" Then
GoTo bImportFiles_Click_Exit
End If

And replace this (I assume this really fits on one line):
DoCmd.TransferText acImportDelim, "Log Import Specification",
"Combined"
Logs ", strFolderPath & objF1.Name, False"
Name strFolderPath & objF1.Name As "h:\Lunch & Learn Activities
\FY"
2008-09\2009-03\Imported\" & objF1.Name 'Move the files to the
archive folder
With this:
strImportPath = BrowseFolder("Select a folder to import to")
If strImportPath = "" Then
GoTo bImportFiles_Click_Exit
End If
DoCmd.TransferText acImportDelim, "Log Import Specification",
"Combined"
Logs ", strFolderPath & objF1.Name, False"
Name strFolderPath & objF1.Name As & """" & strImportPath &
"""" & objF1.Name 'Move the files to the archive folder

Again, that last TransferText line should stay on one line. I
replaced the "h:\Lunch...\Imported\" text with the new strImportPath
variable. The bits I added [& """" &] just makes sure the double
quotes show up in your path.

Let me know if you try it and whether it works out.