Passing Function a Variable

  • Thread starter Thread starter Zeta
  • Start date Start date
Z

Zeta

Why do I get a compile error on the line
"ImportTextFile (.FoundFiles(i),TableName)"
In the first function? I am trying to pass the second
function two arguements. It works fine if I remove
the "TableName" arguement.

Thanks



Option Compare Database


Function FileSearch()

Dim strFilesFound As String
Dim strImportPath As String
Dim strtblName As String
Dim intResponse As Integer
Dim TableName As String

On Error GoTo Err_FileSearch

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.CursorType = adOpenDynamic
.Open ("tblInfo"), CurrentProject.Connection 'This is
a table where I would store the default import path
'strImportPath = !ImportPath
'Use the function getPath to get the folder path with
data
strImportPath = getPath()
'User must enter the name of the table data will be
imported into
TableName = InputBox("What is the EXACT name of the
table that will accept the import", "LocateFiles ")
.Close
End With

Set rst = Nothing

With Application.FileSearch
.FileName = "*.rw1"
.LookIn = strImportPath
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
strFilesFound = strFilesFound & Chr$(10)
& .FoundFiles(i)
Next i

intResponse = MsgBox(.FoundFiles.Count & " File(s)
Found:" & Chr$(10) & strFilesFound _
& Chr$(10) & Chr$(10) & Format("Import and then
delete files?") _
, vbYesNo, "Confirmation")

If intResponse = vbYes Then
For i = 1 To .FoundFiles.Count
'If ImportTextFile(.FoundFiles(i)) = True Then
Kill (.FoundFiles(i)) 'removed to stop file delete
ImportTextFile (.FoundFiles(i),TableName)
Next i
MsgBox "Import completed"
End If

If intResponse = vbNo Then Exit Function

Else
MsgBox "No importable text files found in " &
strImportPath
End If

End With
Exit Function

Err_FileSearch:
Select Case Err
Case 3021
MsgBox "Import folder must be selected",
vbInformation
DoCmd.OpenForm "frmImportPath", acNormal 'lets
user select import path and save to tblInfo
Exit Function
Case Else
MsgBox Err & " " & Error$
End Select
End Function

Function ImportTextFile(strFileName As String, TableName
As String) As Boolean

On Error GoTo Err_ImportTextFile

DoCmd.TransferText acImportDelim, "CO", TableName,
strFileName, False
ImportTextFile = True
Exit Function

Err_ImportTextFile:
ImportTextFile = False
Select Case Err
Case 2391
MsgBox "An error occured while importing your
file" & strFileName & "." & vbCrLf & Format("File not
Imported nor Deleted", "bold"), vbCritical, "File Import
Error"
Case 2501
Exit Function
Case Else
MsgBox Err & " " & Err.Description
End Select

End Function
 
Here is the getPath function that is called....Sorry

Public Function getPath( _
Optional InitialDir As String, _
Optional FilterMessage As String = "Choose Folder
Only", _
Optional FilterSkelton As String = "*|*", _
Optional File As String = "Folders Only", _
Optional Title As String = "Use the Open Button to
Select") As String
Dim CommDlgError As Long
Dim OFN As OPENFILENAME
If Len(InitialDir) = 0 Then InitialDir = CurDir$()
With OFN
.lStructSize = Len(OFN)
.lpstrFilter = FilterMessage & vbNullChar &
FilterSkelton & String(2, vbNullChar)
.lpstrFile = File & String(MAX_PATH - Len(File),
vbNullChar)
.nMaxFile = MAX_PATH
.lpstrInitialDir = InitialDir & vbNullChar
.lpstrTitle = Title
.lpstrFileTitle = String(MAX_PATH, vbNullChar)
.nMaxFileTitle = MAX_PATH
.Flags = OFN_HIDEREADONLY Or OFN_NOCHANGEDIR Or
OFN_SHAREAWARE
If GetOpenFileName(OFN) <> 0 Then
' .lpstrFileTitle conatins file name only
If FilterSkelton = "*|*" Then
getPath = Left(.lpstrFile, .nFileOffset)
Else
getPath = .lpstrFile
End If
Else
CommDlgError = CommDlgExtendedError
' if not just a cancel
If CommDlgError <> 0 Then
MsgBox "Common Dialog Error # " &
CommDlgError _
& vbCrLf _
& vbCrLf _
& "Consult Common Dialog Documumentation" _
& vbCrLf _
& "(in MSDN Library)" _
& vbCrLf _
& vbCrLf _
& "for meaning.", _
vbCritical, _
"Cyriv Solutions"
End If
End If
End With
End Function
 
Try to change name of variable: TableName in first function
And test for empty string. If You select Cancel in
InputBox, TableName = "". Maybe this causes an error.
 
I tried that with no luck, but the problem is a syntax
error, compiler is "expecting an =" or something to that
effect.

Zeta
 
Got it, the parenthesis were screwing it up.
-----Original Message-----
I tried that with no luck, but the problem is a syntax
error, compiler is "expecting an =" or something to that
effect.

Zeta

.
Click to expand...
 
Back
Top