TransferText - File Name Issue???

[smedvid]

I am having an issue with using TransferText where the file name is not properly created. see code below...

lcExportFileName = "C:\Commissions\EPI.01.73.CSV"
DoCmd.TransferText acExportDelim, "Export_Spec", "qryADP_PayRoll_Final", lcExportFileName

The file namee created is "EPI#01#73.CSV".

I will research using the file script object to see if I can rename the file... but, hopefully there is a better way.

Any help is appreciated... tia, Steve Medvid
"IT Consultant & Web Master"
web page under development...

 

[rewdee]

Hi Steve

If you know that the period will be replaced with the pound sign all the time, you could always create a procedure that renames the file after it is exported. Maybe something like:

Private Function ReplaceInString(ByVal SourceString As String, That As String, WithThis As String) As String
    ' This is a fairly generic routine that replaces all instances of "That" with "WithThis" in
    ' the string "SourceString". It handles gracefully the case where "That" is not found within
    ' the SourceString. It does even handle the case where "WithThis" contains "That"...
    Dim Position As Long
    Dim SomeString As String
    Dim LeftPart As String
    Dim RightPart As String
    
    SomeString = SourceString
    Position = InStr(1, SourceString, That)
    While Not Position = 0
        If Position = 1 Then
            LeftPart = ""
            RightPart = Mid$(SomeString, Len(That) + 1)
        Else
            If Position = Len(SomeString) Then
                RightPart = ""
                LeftPart = Left$(SomeString, Len(SomeString) - Len(That))
            Else
                LeftPart = Left$(SomeString, Position - 1)
                RightPart = Mid$(SomeString, Position + Len(That))
            End If
        End If
        SomeString = LeftPart & WithThis & RightPart
        Position = InStr(Position + Len(WithThis), SourceString, That)
    Wend
    ReplaceInString = SomeString
End Function

Private Sub ChangeFileName(sFile as String)
    Name ReplaceInString(sFile,".","#") As ReplaceString(sFile,"#",".")
End Sub

Hope this Helps,
Rewdee

 

[smedvid]

Good idea... I was on the same track... Unfortunate we need to compensate for design flaws in Access. Thanks, Steve Steve Medvid
"IT Consultant & Web Master"
web page under development...

 

[p27br]

you could use the CHR() function :

lcExportFileName = "C:\Commissions\EPI" & chr(46) & "01" & chr(46) & "73" & ".CSV"

 

[smedvid]

chr(46)... Sounds like something for future reference to resolve this issue. Thanks. I used a similar approach for handling quotes in a string. Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...