transfer text

[aftershokk]

I have a macro which transfers a fixed width file to my desktop. The name is neppow.txt. Can I have this file land with the date dynamcially populated in the name?

Example:

neppow_20070308.txt today

neppow_20070309.txt tomorrow

thanks!

 

[boblarson]

Instead of a macro use VBA, then you can easily incorporate variables:

Dim strFileName As String

strFileName = "C:\Temp\neppow_" & Format(date, "yyyy") & Format(date, "mm") & Format(date, "dd") & ".txt"
DoCmd.TransferText acExportDelim, "YourExportSpecName", "YourTableName", strFileName, True

 

[aftershokk]

reply

I am not good at all at VBA

Right now my macro opens a union query and then transfers the results as the text file on my desktop.

Can I run this VBA in my macro?

thanks!

 

[boblarson]

Sure, in fact there is a function within Access where you can actually ask it to upgrade your macro to Visual Basic. You make sure the Macro is highlighted in the database window and then go to TOOLS > MACRO > CONVERT MACRO TO VISUAL BASIC.

Then you can go into the vb code it creates and tweak your transfertext code to be like the sample I gave you.

 

[aftershokk]

one more question

thanks for your help but where does it fit in?

I am clueless with VBA. Do you recommend any training sites also?

here is my code

' test2
'
'------------------------------------------------------------
Function test2()
On Error GoTo test2_Err

DoCmd.Echo False, ""
DoCmd.SetWarnings False
DoCmd.OpenQuery "updt_sent_date", acViewNormal, acReadOnly
DoCmd.TransferText acExportFixed, "export_all_spec", "test", "C:\Documents and Settings\CCLOCHER\Desktop\nepho_tufts.txt", False, ""
DoCmd.SetWarnings True
DoCmd.Echo True, ""
Beep
MsgBox "File should be on your desktop - nepho_tufts.txt", vbInformation, "Please Read"


test2_Exit:
Exit Function

test2_Err:
MsgBox Error$
Resume test2_Exit

End Function

 

[boblarson]

Function test2()
On Error GoTo test2_Err

[B][COLOR="Red"]Dim strFileName As String

strFileName = ""C:\Documents and Settings\CCLOCHER\Desktop\nepho_tufts_" & Format(date, "yyyy") & Format(date, "mm") & Format(date, "dd") & ".txt"[/COLOR][/B]

DoCmd.Echo False, ""
DoCmd.SetWarnings False
DoCmd.OpenQuery "updt_sent_date", acViewNormal, acReadOnly
DoCmd.TransferText acExportFixed, "export_all_spec", "test", [COLOR="red"][B]strFileName[/B][/COLOR], False, ""
DoCmd.SetWarnings True
DoCmd.Echo True, ""
Beep
MsgBox "File should be on your desktop - [COLOR="red"][B]" & strFileName[/B][/COLOR], vbInformation, "Please Read"


test2_Exit:
Exit Function

test2_Err:
MsgBox Error$
Resume test2_Exit

End Function

 

[aftershokk]

Thanks Bob

Cheers!