Identify file with a pattern

[ohmbru2]

I have been searching this forum (and others) to learn how to do this.

I currently have an Access DB that runs an import process when it's opened (VBA). I'm trying to use DTS to do this.

I think I have most of the steps figured out except one: Identifying the file name.

In VBA, I look for a pattern to find the file name. The file name is different every day because the date is in the file name (myfile_06152006.dat).I've pasted the code below. How can I do this with DTS?

Sub GetHistory()

Dim ImportFileName As String
Dim ImportFileDirectory As String
Dim ImportFilePattern As String
Dim NewDirectory As String

ImportFileDirectory = "S:\AMLOPSRPT\MOBE\Tower\"
NewDirectory = ImportFileDirectory & "History\"
ImportFilePattern = "polh*.dat"
DoCmd.Hourglass True
ImportFileName = Dir(ImportFileDirectory & ImportFilePattern, vbNormal)
Do While ImportFileName <> ""
If ((GetAttr(ImportFileDirectory & ImportFileName) And vbNormal) = vbNormal) Then
Call ImportHistory(ImportFileDirectory, ImportFileName, NewDirectory)
End If
ImportFileName = Dir
Loop
DoCmd.Hourglass False

End Sub

Sub ImportHistory(ImportFileDirectory As String, ImportFileName As String, NewDirectory As String)
Dim strTableName As String
Dim intPos As Integer
Dim strProcessDate As String
Dim strSQL As String

On Error GoTo ErrorHandler

intPos = InStr(1, ImportFileName, ".", 1)
strTableName = "tblYesterday"
strProcessDate = Mid(ImportFileName, intPos - 4, 4)

DoCmd.SetWarnings False
strSQL = "Delete " & strTableName & ".*" & " From " & strTableName
DoCmd.RunSQL strSQL


DoCmd.TransferText acImportDelim, "TowerHistory", strTableName, ImportFileDirectory & ImportFileName
dteProcessDate = DateValue(Left(strProcessDate, 2) & "/" & Mid(strProcessDate, 3, 2) & "/" & Year(Date))

DoCmd.OpenQuery "qryAppendYesterdayToHistory", acViewNormal, acEdit
DoCmd.SetWarnings True
FileCopy ImportFileDirectory & ImportFileName, NewDirectory & ImportFileName

Call DeleteImportFile(ImportFileDirectory & ImportFileName)
Forms![frmfileinfo]![ImportHistoryTable] = -1
Exit Sub

ErrorHandler:
DoCmd.SetWarnings True
MsgBox ("Import Failure of " & ImportFileName)


DoCmd.Hourglass False
End Sub

 

[mrdenny]

You can use a dynamic properties task to dynamically set the file name for the connection objects for the file.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com