Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TransferText Macro (2002)

    I set up a macro to transfer a Table to a text file using hte TransferText macro. Unfortunately, it requires me to name the output file ("Output File.txt"), and because of this it replaces the prior Output File.txt every time the macro is run. Is there a way to run this macro that would create a new text file every time rather than overwriting the existing one?

    Thanks,
    Craig

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Craig,

    unless you change the name of the file you can't have a file with the same name in the same folder. You will either have to have a new name each time (output file time stamp.txt perhaps) or save the file off some where each time you run the macro.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Craig,

    Here is just one method using a function. Perhaps adding a time of day stamp if you creat the file more than onec a day.

    '------------------------------------------------------------
    ' Text_Out
    '
    '------------------------------------------------------------
    Function Text_Out()
    On Error GoTo Text_Out_Err

    DoCmd.TransferText acExportDelim, "", "tbl_Accounts", "cutputfile" & Year(Date) & Month(Date) & day(Date) & ".txt", False, ""

    Text_Out_Exit:
    Exit Function

    Text_Out_Err:
    MsgBox Error$
    Resume Text_Out_Exit

    End Function
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: TransferText Macro (2002)

    Hope he does not do this more than once per day, else it will overide the previous one.

  5. #5
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Don:

    Thanks for the answer. Two quick follow up questions:
    1. In the TransferText Macro I am using a Specification Name of "Routing Table Export Specification" and the table is called "Routing Table". Would the function be modified then to read: DoCmd.TransferText acExprotDelim,"Routing Table Export Specification","tbl_Routing Table", ""cutputfile" & Year(Date) & Month(Date) & day(Date) & ".txt", False, "?

    2. Where do I put this? In a new "Module", and if so, does it then over ride the macro I previously created?

    Thanks,
    Craig

  6. #6
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Is there a way to add a time stamp as well so this can be done more than once per day?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: TransferText Macro (2002)

    Use Hour(Now) and Minute(Now)

  8. #8
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Thanks...works perfect!

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    Craig,

    Sorry you got me while I was heading home.

    If you go to your macro and do a Save As and then select Module you will see your macro code and can then modify it and then run the code from the macro.

    Don
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  10. #10
    New Lounger
    Join Date
    Nov 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    One last thing...It runs great once I open the Module and hit Run from the menu bar, but at the main Module screen if I highlight the module it only gives the Design and New options (not Run, which is greyed out). Any thoughts?

  11. #11
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText Macro (2002)

    To run a module you need to be in the VB editor. To run it from your Form you will need to call the function. (Shown as a button click).

    Private Sub SomeButton_Click()
    On Error GoTo Err_SomeButton_Click

    Text_Out ("Your table name here") ' Call function to write to text file

    Exit_SomeButton_Click:
    Exit Sub

    Err_SomeButton_Click:
    MsgBox Err.Description
    Resume Exit_SomeButton_Click

    End Sub

    '------------------------------------------------------------
    ' Text_Out
    '
    '------------------------------------------------------------
    Function Text_Out(tblName as String)
    On Error GoTo Text_Out_Err

    tab]DoCmd.TransferText acExportDelim, "", tblName, "cutputfile" & Year(Date) & Month(Date) & Day(Date) & ".txt", False, ""

    Text_Out_Exit:
    Exit Function

    Text_Out_Err:
    MsgBox Error$
    Resume Text_Out_Exit

    End Function
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •