Defining path-to-files to import 1

[SkyHigh]

Hi Folks

I am using TransferText command in a module to import a number of files and the path is hardcoded, what I want is if the files are not found in the hardcoded path, it should prompt a msg to this effect on a dialogbox from which the user should be able to browse to the folder where the files are and the path-to-files should change to what user browses to.

I am using the following code :

Public Function transfertext()
DoCmd.transfertext acImportDelim, , "table1", "c:\sample\table1.txt", True
DoCmd.transfertext acImportDelim, , "table2", "c:\sample\table2.txt", True
DoCmd.transfertext acImportDelim, , "table3", "c:\sample\table3.txt", True
DoCmd.transfertext acImportDelim, , "table4", "c:\sample\table4.txt", True
DoCmd.transfertext acImportDelim, , "table5", "c:\sample\table5.txt", True
DoCmd.transfertext acImportDelim, , "table6", "c:\sample\table6.txt", True
DoCmd.transfertext acImportDelim, , "table7", "c:\sample\table7.txt", True
End Function


Thanks for your help
Brenda

 

[SIG357]

You can test to see if the file exists using the Dir() function. If it doesn't (Dir returns "&quot then pop open a Common Dialog Cotrol (Open File mode) style, and let the user brose for the requested file. Check back in if you need help with the syntax.

 

[jfgambit]

The following will open a common dialog box and store the selection in a file called Textbox1. Then have your code point to the textbox.


Option Compare Database
'Open a Common Dialog box to export file too
Private Declare Function apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Function LaunchCD(strform As Form) As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
OpenFile.hwndOwner = strform.Hwnd
sFilter = "Text Document (*.txt*)" & Chr(0) & "*.*"
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Select a file for import"
OpenFile.flags = 0
lReturn = apiGetSaveFileName(OpenFile)
If lReturn = 0 Then
MsgBox "A file was not selected!", vbInformation, _
"Select a file"
Else
LaunchCD = Trim(OpenFile.lpstrFile)
End If
End Function



Private Sub cmdTextbox1_Click()
On Error GoTo Err_cmd_Click

Me.Textbox1 = LaunchCD(Me)

Exit_Close_Click:
Exit Sub

Err_cmd_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"