transfer text with wildcard

[mpearce]

I am attempting to execute a transfertext statement to import a file. The file contains a time stamp. Is there anyway to have a wildcard character as a part of the file name without it being recognized as a literal character?

The file name would be in the form of:

DECO_RECON_052810_080006.txt

with the red portion being a time stamp.

if i use this lines of code to set the file path:
[vba]filename = "x:\frankford\refrec\DECO_RECON_" & newdate & "*.txt"
[/vba]
where newdate is set to an inputbox prompting the user for a date i get:

x:\frankford\refrec\DECO_RECON_newdate_*.txt

which needs to look like this:

x:\frankford\refrec\DECO_RECON_newdate_080006.txt

the problem is the red text will be different from day to day.

Any thoughts on how to do this?

Thanks in advance.

[OBP]

Can I suggest that you use a Browser to let them select the file?
The problem with a wild card is that it would work with any file starting with
x:\frankford\refrec\DECO_RECON_newdate
so if there was more than one you would have a conflict as Access wouldn't be able to open more than one.

[mpearce]

Can I suggest that you use a Browser to let them select the file?
The problem with a wild card is that it would work with any file starting with
x:\frankford\refrec\DECO_RECON_newdate
so if there was more than one you would have a conflict as Access wouldn't be able to open more than one.

alright perhaps I give a little more detail. this is a small application that reads in 2 text files to 2 tables that on joined on a field. It then runs and unmatched query to find the differences between the two tables. those results are then exported to a text file for import into a larger database.

so in choosing the two files the dates will be different and will not repeat. i could use a file browser to do this but the install of office is running on a vista machine and i get an error reading "run-time error '429' ActiveX component can't create object". the debugger points to this line:

[VBA]Set objdialog = CreateObject("UserAccounts.CommonDialog")[/VBA]

so that error is the main reason why i didnt choose a file browser to do this. how can i resolve that error. I do not get this error if running this on an XP machine.

[OBP]

There are a couple of versions of Browsers, I use this one
[VBA]
Function dbc_OpenFile(Optional strFile As Variant = Null, Optional strFilter As String = "All Files (*.*)", Optional strDir As String = "", Optional strTitle As String = "", Optional strButton As String = "")
'Use to choose a file to open
Dim FSI As FileSelInfo

On Error GoTo ErrorHandler
With FSI
.lngFlags = 0
.strFilter = RTrim(strFilter) & vbNullChar
.lngIndex = CInt("0")
If Not IsNull(strFile) Then
.strFile = RTrim(strFile) & vbNullChar
End If
.strTitle = RTrim(strTitle) & vbNullChar
.strButton = RTrim(strButton) & vbNullChar
.strDir = RTrim(strDir) & vbNullChar
End With

If GetFileInfo(FSI, True) = 0 Then dbc_OpenFile = dbc_TrimNull(FSI.strFile)
GoTo Done

ErrorHandler:
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Done:
End Function[/VBA]

[mpearce]

How can i rename this file:

x:\frankford\refrec\DECO_RECON_061010_080006.txt

to:

x:\frankford\refrec\DECO_RECON_061010.txt

the 080006 portion of the path is a time stamp and will be different day to day.

[OBP]

Name Statement
Name oldpathname As newpathname
From the help file
[vba]
Name Statement Example@import url(office.css);
Dim OldName, NewName
OldName = "OLDFILE": NewName = "NEWFILE"
' Define file names.
Name OldName As NewName
' Rename file.
OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE" Name OldName As NewName
' Move and rename file. [/vba]