Remove Prefix of Query Name when Doing Transfer Text

[Sam]

I am using the code below and it works but, I need to remove the
common query name from the exported name.
All the queries I need to create a .txt file for all start with
"exp_", but when I extact the files I want the "exp_" removed from
the
name.


Example: Exp_test would become text.txt


Any suggestions would be great


Thanks
Sam


Sub ExportqrySam()


On Error GoTo Err_Handler


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strqryName As String


Set db = CurrentDb


For Each qdf In db.QueryDefs
strqryName = qdf.Name
If strqryName Like "exp_*" Then
SysCmd acSysCmdSetStatus, "Exporting " & strqryName &
"..."
DoCmd.TransferText acExportDelim, , strqryName, _
"C:\Documents and Settings\griffis1\My Documents\MASUNL\"
& strqryName & ".txt", True
End If
Next qdf


DoCmd.Hourglass False
MsgBox "Done. Samantha, Please move files to the Appropriate
Transfer Folder!"


Exit_Point:
DoCmd.Hourglass False
SysCmd acSysCmdClearStatus
Set db = Nothing
Exit Sub


Err_Handler:
DoCmd.Hourglass False
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
GoTo Exit_Point

 

[John Spencer]

DoCmd.TransferText acExportDelim, , strqryName, _
"C:\Documents and Settings\griffis1\My Documents\MASUNL\"
& Mid(strqryName,5) & ".txt", True

Or use the following

Dim strDestination as String
If Left(strQryName,4) = "exp_" Then
strDestination = Mid(strQryName,5)
Else
strDestination = strQryName
End if

DoCmd.TransferText acExportDelim, , strqryName, _
"C:\Documents and Settings\griffis1\My Documents\MASUNL\"
& strDestination & ".txt", True


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom van Stiphout]

On Wed, 5 Nov 2008 05:15:44 -0800 (PST), Sam <[email protected]>
wrote:

You can use the Mid$ function to trim off the first characters:
?Mid$("abcdefg", 3)
=> "cdefg"

-Tom.
Microsoft Access MVP