How to export a fixed txt file with another field value attached to title

[neelsfer]

I managed to export a table called tblrdm, to a folder "C:\rdm\demand\ in" in fixed width format, as "Order_.txt " with the code below. (RDMExport is the specification).

DoCmd.TransferText acExportFixed, "RDMExport", _
    "tblRDM", "C:\rdm\demand\in\Order_.txt"

I now want to add the "ordernumbe r" value of this order to the title, when exporting it.

Dim strFileName As String
Dim strPath As String
        strPath = "C:\rdm\demand\in\"
        strFileName = "Order_" & [Forms]![orderF]![OrderN] + ".txt"
     DoCmd.TransferText acExportFixed, "RDMExport", _
    "tblRDM", strFileName

Error - it does not export to my folder specified
Where do i add the path in this code of mine?:

Any suggestions?

[jimatqsi]

You don't mention what error message you are getting, if any. But one thing is that you didn't include the path in your file name. Another is the use of "+" instead of "&" for the file extension.

Jim

[neelsfer]

Hi Jim
Just nothing happens because i am not sure where to add the path at the end of the code. The strPath and strFilename needs to be combined somehow?

[neelsfer]

Got something working but there is a sting in the tail

 
     DoCmd.TransferText acExportFixed, "RDMExport", _
    "tblRDM", strPath & strFilename

i get a file like this now

Order_4886.txt

With this same code, i need to change the extension to ".in" after exporting it
Suggestions?

[jimatqsi]

So Order_4886.txt is very good, correct? I think that's what you wanted.

You can rename a file by using the NAME function.

NAME OLDName As NewName

and of course OldName and NewName both need to include the path, either as part of a single string or as you did before, strpath & strFilename


Jim

[neelsfer]

thx Jim id id just that and it works

    Dim strOldName As String
Dim strNewName As String
strOldName = "C:\rdm\demand\in\Order_" & [Forms]![orderF]![OrderN] + ".txt"
strNewName = "C:\rdm\demand\in\Order_" & [Forms]![orderF]![OrderN] + ".in"
If Len(Dir(strOldName)) > 0 Then
Name strOldName As strNewName
End If

[jimatqsi]

Great news. Glad it's working for you.

I didn't realize you could use the "&" and "+" interchangeably . I learned something too.

Jim

[zmbd]

Actully, you should not use the "+" and "&" interchangably. I personally couldn't explain why any better than David does here:

In VBA and Jet/ACE SQL the & concatenation operator ignores Null (Null & "" = "") while the + concatenation operator propagates Nulls (Null + "" = Null). Null concatenation is quite useful in expressions like Mid(("12 + LastName) & (", " + FirstName), 3), but you have to be careful not to try to use it with numeric fields (or strings that can be implicitly coerced to numeric values), since while "12" + Null will propagate the Null, "12" + "8" MAY add the numeric values of the two strings (it depends on the context). – David-W-Fenton Sep 10 '10 at 19:30

Also our members should take a look at 102.What is Null?

I can't stress this enough: using the "+" can lead to very un-intended results.