TransferText action - filename specifications 1

[elvanace1]

I have a macro that uses a query result and the TransferText action to srite a txt file to a server. I would like to encorporate the date & time stamp into the filename. Is that possible? If yes, how?

 

[MacCaillean]

Hi elvanace1

One of the parameters of the TransferText action is the filename you want to output to.

So instead of just supplying a string as a name (e.g. "filename.txt"), you can add variables (e.g. "filename_" & Date() & "_" & Time() & ".txt").

Hope this helps

Mac

 

[elvanace1]

When I put that string into my statement I get this filemane: _ExportIPs__ & Date() & ___ & Time() & _.txt_

 

[MacCaillean]

Okay - set up a variable and then supply that instead.

Dim MyFilename as String

MyFilename="ExportIPs_" & Date() & "_" & Time() & ".txt"

Then in the TransferText command, set the filename parameter to be MyFilename.

Rgds

Mac

 

[elvanace1]

I'm trying to do this within a Macro. I don't know how to accomplish what you are suggesting.

 

[MacCaillean]

Of course you don't - sorry, the word macro just didn't register with me.

Okay, you'll have to convert your macro to VBA.

Highlight your macro in the list and then choose Tools > Macro > Convert Macros to Visual Basic.

The VBA window will open and you can find your converted macro by double-clicking in the project window on Modules > Converted Macro- yourMacroName.

Then replace the filename in the DoCmd.TransferText command with my suggestion from earlier.

Your line of code should then look something like this (make sure it is typed on one line):

DoCmd.TransferText acExportDelim, "", "yourTableName", [b]"c:\yourPath\yourFilename_" & Date() & "_" & Time() & ".txt"[/b], False, ""

To actually use the code, create a new macro using the RunCode action, and enter the name of your new VBA function as the parameter. So for instance, if your converted macro is called Function ExportFile() in the VBA window, in your new macro you should enter ExportFile() in the RunCode parameter. Then run your new macro instead of your old one in whatever way you are doing, e.g. from a command button.

Hope this helps this time

Mac

 

[elvanace1]

I got an error using the date()function so I changed to the Month() and Day() - this worked perfectly. Thanks sooooo much!!!

 

[PHV]

You may consider something like this:
"c:\yourPath\yourFilename_" & Format(Now(), "yyyy-mm-dd_hh.nn.ss")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elvanace1]

That's better yet - because the file is read into Unix and the space before AM or Pm was a problem. This eliminated the problem altogether. Thanks again.

 

[elvanace1]

One last question. Is there a way to onit the headers info when writing the file?

 

[PHV]

Is the 5th argument set to False ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elvanace1]

This is the line: DoCmd.OutputTo acQuery, "TT-TRANSITION BOEING FILE-single", "MicrosoftExcelBiff8(*.xls)", "s:\it\exchange\Rocketdyne-SB_" & Format(Now(), "yyyy-mm-dd") & ".csv", False, "", 0
What am I doing wrong?

 

[PHV]

The starred suggestion was TransferText, not OutputTo.