Problem assigning a dynamic name for an output text file

[mabrynda]

Dear Experts,
I have the following problem. I'm generating output txt files from access (an access 2002 table is converted into deleimited text with tabs including field names). For that I have a small function that I call from the main VBA script, which includes the so-called Export Specification. Everything works fine as long as the name of the output file is written in characters in the file specification of the DoCmd.TransferT ext command.
Nevertheless, if I try to assign a dynamic name (say FileX where X can vary from 0 to 9) I get a message error. However if I Debug.Print my current string variable containing the path and the file name, I see the correct name (e.g. C:\Directory\Fi leX.txt). Does anybody has an idea what's going on here....

Marcin

The function called from the main code is the following:

 Option Compare Database



Function TXTsendTblFinalMATLABTable1()
WlistNum = DLookup("Lists", "WorkingIndexList")
Debug.Print "WListNum", WlistNum
strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List" & WlistNum & ".txt"
Debug.Print "strJname", strJname



  DoCmd.TransferText transfertype:=acExportDelim, _
   specificationname:="FinalMATLABTable1 Export Specification", _
   TableName:="FinalMATLABTable1", _
   Filename:=strJname, _
   hasfieldnames:=True
End Function

The resulting Debug.Print is:

  OldNameSpecialRegion
NewNameRegion
OldNameSpecialRegionID
NewNameRegionCode
OldNameCodeBloomberg
NewNameCode
WListNum       37 
strJname      C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List37.txt

[ADezii]

Dear Experts,
I have the following problem. I'm generating output txt files from access (an access 2002 table is converted into deleimited text with tabs including field names). For that I have a small function that I call from the main VBA script, which includes the so-called Export Specification. Everything works fine as long as the name of the output file is written in characters in the file specification of the DoCmd.TransferT ext command.
Nevertheless, if I try to assign a dynamic name (say FileX where X can vary from 0 to 9) I get a message error. However if I Debug.Print my current string variable containing the path and the file name, I see the correct name (e.g. C:\Directory\Fi leX.txt). Does anybody has an idea what's going on here....

Marcin

The function called from the main code is the following:

 Option Compare Database



Function TXTsendTblFinalMATLABTable1()
WlistNum = DLookup("Lists", "WorkingIndexList")
Debug.Print "WListNum", WlistNum
strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List" & WlistNum & ".txt"
Debug.Print "strJname", strJname



  DoCmd.TransferText transfertype:=acExportDelim, _
   specificationname:="FinalMATLABTable1 Export Specification", _
   TableName:="FinalMATLABTable1", _
   Filename:=strJname, _
   hasfieldnames:=True
End Function

The resulting Debug.Print is:

  OldNameSpecialRegion
NewNameRegion
OldNameSpecialRegionID
NewNameRegionCode
OldNameCodeBloomberg
NewNameCode
WListNum       37 
strJname      C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List37.txt

1. Is there a Typo in the Absolute Path portion?
2. What happens if you replace Line #8 with:
   

   strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List37.txt"

[jimatqsi]

Looks like it might not like the number followed by the . delimiter for the extension.

What happens if you follow that variable number with a character, i.e List37a.txt or List38x.txt? Or 37List.txt?

Jim

[ADezii]

If everything else is OK, then try:

strJname = "C:\MATLAB\DB\NEWPROJECTS\COUNTRY_SELECT\List" & CStr(WlistNum) & ".txt"

[mabrynda]

Big thanks everybody!
I spent quite a long time figuring out what was wrong. In fact nothing was wrong in the code, however for some reason the ACCESS wasn't accepting the export DoCmd command. After a step-by-step elimination procedure for possible errors, I started to suspect that something was wrong with the program itself. Closing, compacting and opening the database wouldn't help neither. Then, I tried to resave the Export Specification file, but the problem was still persisting. Finally I created a completely new database, and tried to build the same thing in. And this time it finally worked with no hassle!!! In fact all works perfectly (my code and your suggestions too) as it should be. So the last step was to delete a module with my function in the original database and to re-type a new one from scratch.
So now it's time for conclusions:

The possible reason for the error: Some bug in ACCESS 2002 that activates itself under certain (undetected for the moment) circumstances.. ...

The lesson to take: If it ever happens to you, just delete the "faulty" module and rewrite a new one from scratch.

Thanks again for everybody's input!

Marcin

[OldBirdman]

Too late for this time, but you can avoid retyping code easily. Copy/Paste code for entire module into MS Notepad or another text editor without an RTF format.
This strips all special characters, leaving only the text. Then Copy/Paste from Notepad into your new database.

[mabrynda]

Too late for this time, but you can avoid retyping code easily. Copy/Paste code for entire module into MS Notepad or another text editor without an RTF format.
This strips all special characters, leaving only the text. Then Copy/Paste from Notepad into your new database.

Hi OldBirdman,
Thanks for the advice. I always use the Notepad++ to get rid of the hidden characters. Deleting the text and copying/pasting didn't work this time. I had really to physically delete the entire module in the ACCESS database and then put it back. But you are right, I should have pasted the text from the deleted module into the fresh one instead of typing it. Well, I was so mad I couldn't find why it wasn't working that I didnt think about it....

M.