Transfer text

[u2cannv]

i'm currently running a macro to import and export files based on a filename that will not change proceeded by todays date dot txt. ex: johndoe101607.txt. is it possible to create the macro so that it will grab johndoe(today's date).txt? if so, how do i set it to do that.

Thanks

 

[pbaldy]

I don't know about a macro, but in VBA it would be simple:

"johndoe" & Format(Date(),"MMDDYY") & ".txt"

 

[u2cannv]

I don't know about a macro, but in VBA it would be simple:

"johndoe" & Format(Date(),"MMDDYY") & ".txt"

thanks a lot. my next question would be, do i have to convert my macro to VB? if so, would i write the code inside my event procedure?

Thanks

 

[boblarson]

thanks a lot. my next question would be, do i have to convert my macro to VB? if so, would i write the code inside my event procedure?

Thanks

The code goes in the VBA Window and not the property dialog (just in case you weren't aware). Check out this link for how to do that (in the example it is showing the Before Update but it is valid for all events):

http://www.btabdevelopment.com/main/LinkClick.aspx?link=56&tabid=55&mid=385

 

[u2cannv]

Thanks a lot

 

[boblarson]

Thanks a lot

My pleasure.

 

[pbaldy]

I'm not saying it can't be done in a macro; I just don't use them so I don't know. You should be able to highlight a macro and click on Tools/Macros/Convert to VB. That will create a module with the name of the macro if I recall correctly. You can then copy/paste that code to the appropriate event. We can then tweak it to do what you want.

 

[u2cannv]

I tried writing the code and even reformatting the date, but since it is formatted in 10/18/07 the file isn't recognized. the date of the file is 101807, without slashes. do you know a way around this.

 

[pbaldy]

I believe I already gave you that:

Format(Date(),"MMDDYY")

 

[u2cannv]

Option Compare Database
Option Explicit





Sub Import()
Dim jane As Date
Dim john As String
Dim RESULT As String
RESULT = "johndoe" & Format(Date, "MMDDYY") & ".tx"
End Sub
here's my code i dont know what i'm doing wrong
Thanks

 

[pbaldy]

That simply builds a string but does nothing with it. You'd want to use RESULT in the code that does the actual import/export.

 

[u2cannv]

Thanks a lot for the help, I finally got the program working, just minor tweaking left. I really appreciate it.
Thanks again

 

[pbaldy]

No problem; if you get a chance, post the working code so that someone searching later may find their answer.

 

[u2cannv]

fileDt = Format(Date, "MMDDYY")
fileNm = "\\jazzs\acc\" & "johndoe" & fileDt & ".txt"
MsgBox (fileNm)
DoCmd.TransferText acImportDelim, "SPECS", "3RPV Export", "\\jazzs\acc\" & ""johndoe" & fileDt & ".txt"ileNm"", False, """
' Runs query a code to 3rpv export