Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2004
    Posts
    139

    Unanswered: Export a table in delimited format

    I would like to send a table out on a weekly basis in delimited format. Is there anyway to do this automatically (macro) without having to use the export wizard? The wizard is great but I do not want to have to manually respond each time I want to send it out. I should be able to set it up once and then send it with a macro.

    Thanks,

  2. #2
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    could you not use an export spec. ?

  3. #3
    Join Date
    Feb 2004
    Posts
    139

    Export Spec?

    I am a beginner and I guess I do not know hwat an export spec. is and how to use. I have used macros heavily to automate my database. Can you give me more information? Would appreciate it.

    Thanks,
    B&R

  4. #4
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    what version of access are you using?

    access 03 you can select to transfer a text file as delimited
    Last edited by michelin man; 02-08-04 at 23:14.

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Mak a new Form, put a Button on it, in the properties of that button, under the events Tab, on Click put the next code

       DoCmd.TransferText acExportDelim, ,"yourTableName", "c:\Temp\anyFileName.txt"

  6. #6
    Join Date
    Feb 2004
    Posts
    139
    Originally posted by michelin man
    what version of access are you using?

    access 03 you can select to transfer a text file as delimited
    I am using Access 2002.

    Thanks,

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Export Spec?

    Originally posted by B&R
    I am a beginner and I guess I do not know hwat an export spec. is and how to use. I have used macros heavily to automate my database. Can you give me more information? Would appreciate it.

    Thanks,
    B&R
    You know how to use the Export Text Wizard... Well all you have to do is save the Export Spec while you are doing the export... Then in a macro you can refer to that particular spec...

    Here.... I just gave someone instructions on how to do this earlier today...

    http://www.dbforums.com/t982259.html

    Check it out...

    HTH

  8. #8
    Join Date
    Feb 2004
    Posts
    139

    Re: Export Spec?

    Originally posted by Trudi
    You know how to use the Export Text Wizard... Well all you have to do is save the Export Spec while you are doing the export... Then in a macro you can refer to that particular spec...

    Here.... I just gave someone instructions on how to do this earlier today...

    http://www.dbforums.com/t982259.html

    Check it out...

    HTH
    TRUDI - I understand what you are saying. Now, how can I send this file on email once I have it delimited. I tried using the sendobject in my macro but that is the problem, it always takes me to the export wizard. I did not want the wizard each time but maybe if I save the specifications as you mentioned, the wizard may not come up???? Any opinions here. I just want to send out a table as delimited via email. Any suggestions are appreciated!!

    Thanks,
    B&R

  9. #9
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Export Spec?

    Originally posted by B&R
    TRUDI - I understand what you are saying. Now, how can I send this file on email once I have it delimited. I tried using the sendobject in my macro but that is the problem, it always takes me to the export wizard. I did not want the wizard each time but maybe if I save the specifications as you mentioned, the wizard may not come up???? Any opinions here. I just want to send out a table as delimited via email. Any suggestions are appreciated!!

    Thanks,
    B&R
    Hmmmm... Well I've done this lots of times using Outlook automation... but let's see if there's an easier way...

    How about if the first time you do this... you export the table as a delimited text file... and then link the text file to the database... Then it'll show up in the tables tab of the database window...

    Then everytime you run your macro you could do two lines... one to export the newest data to the text file (I believe if it has the same name as a file that's already existing, it'll just overwrite the external file) using your saved export specification... and then a second line to sendobject.... (sending the linked file as if it's a table)

    Does that make any sense? lol...

  10. #10
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Export Spec?

    Originally posted by Trudi
    Hmmmm... Well I've done this lots of times using Outlook automation... but let's see if there's an easier way...

    How about if the first time you do this... you export the table as a delimited text file... and then link the text file to the database... Then it'll show up in the tables tab of the database window...

    Then everytime you run your macro you could do two lines... one to export the newest data to the text file (I believe if it has the same name as a file that's already existing, it'll just overwrite the external file) using your saved export specification... and then a second line to sendobject.... (sending the linked file as if it's a table)

    Does that make any sense? lol...
    P.S. I haven't tested this... and I've never tried it... but it's a thought that came to mind...

  11. #11
    Join Date
    Feb 2004
    Posts
    139
    Trudi - I will give your idea a shot! I have never linked a file to the database but I probably can figure that part out. However, I have tried to import into an excel file from the Access database and that will not work because the database is secured. If this does not work maybe you could enlighten me on the outlook automation end of the equation. Thanks for all your thoughts.

    B&R

  12. #12
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Okay... Here's a quick sample I made for you...

    You still need to export the text file once and save the export specification (just like I explained in that other post) ...Save it as the default name (should be "YourTableName Export Specification")...

    Then insert this code into a new module in the database... I call the function in the Sub procedure below, sending all of the applicable arguments in the correct order... You could call it from the On Click event for a command button or wherever you please...


    Code:
    Option Compare Database
    Option Explicit
    
    
    Public Function SendTextFile(strPath, strTableName, strRecipient, strSubject)
    
    Dim myOlApp As Object, myItem As Object, myAttachments As Object
    Dim MyPath As String, MyName As String
    
    'First, export the text file using a saved Export Specification
    DoCmd.TransferText acExportDelim, strTableName & " Export Specification", _
        strTableName, strPath & strTableName & ".txt", False
    
    'Second, create an instance of outlook, new message and attachment object variables
    Set myOlApp = CreateObject("Outlook.Application")
    Set myItem = myOlApp.CreateItem(olMailItem)
    Set myAttachments = myItem.Attachments
    
    'add the recipient(s)
    myItem.Recipients.Add strRecipient
    'add the subject line
    myItem.Subject = strSubject
    
    
    MyPath = strPath ' Set the path.
    MyName = Dir(MyPath) ' Retrieve the first entry.
    
    'Loop through the files in the directory to attach all applicable files
    '(this is really for more than one attachment but it will work for one file too...)
    Do While MyName <> ""   ' Start the loop.
    
        'this is where you decide what test you need... I'm just using the name itself, but you can do things like
        'attach all .txt files, etc...
        If MyName = strTableName & ".txt" Then
             myAttachments.Add MyPath & MyName
        End If
        MyName = Dir
        
    Loop
    
    'send the email
    myItem.Send
    
    'release memory allocated for object variables
    Set myOlApp = Nothing
    Set myItem = Nothing
    Set myAttachments = Nothing
    
    End Function
    
    
    Public Sub TestProcess()
    On Error GoTo TestProcess_Err
    
    'Call the function with all the relevant arguments in the correct order...
    SendTextFile "YourPath", "YourTableName", "your.recipient@somewhere.com", "Your Email Subject Line"
    
    'hypothetical example
    SendTextFile "C:\", "FxActivity_CAD", "tracy.campbell@newbie.com;jane.doe@newbie.com", _
                        "Let's try it with two recipients"
                        
    TestProcess_Exit:
        Exit Sub
        
    TestProcess_Err:
        MsgBox Err.Number & " - " & Err.Description
        Resume TestProcess_Exit
    End Sub

    Hope this makes sense... HTH

  13. #13
    Join Date
    Feb 2004
    Posts
    139
    Trudi -

    I am going to give this a shot but I want to confirm a few things.

    Do I go into this script and place the my path, my tablename, my recipient and my subject in the Public Function row? Will this string automatically populate the rest of the script or will I need to go and put my information in every instance within the script. I know that I will also need to add the export specification in the script.

    Thanks,
    B&R

  14. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by B&R
    Trudi -

    I am going to give this a shot but I want to confirm a few things.

    Do I go into this script and place the my path, my tablename, my recipient and my subject in the Public Function row? Will this string automatically populate the rest of the script or will I need to go and put my information in every instance within the script. I know that I will also need to add the export specification in the script.

    Thanks,
    B&R
    What you need to do is ... wherever it is that you want to run the code... ie ...send the email... you have to add the call line...
    SendTextFile "YourPath", "YourTableName", "your.recipient@somewhere.com", "Your Email Subject Line"

    If it's in a click event for a button... put it in the click event's code...

    What do you mean "Will this string automatically populate the rest of the script"? Once you update the Export spec name, I don't see how you'd have to change anything in the code... unless the tablename, pathname, recipient or subject line is changing... and that would be changed only in your call line... (above)

    Sorry... I'm confused...

  15. #15
    Join Date
    Feb 2004
    Posts
    139
    Trudi -

    I tried to run the code and I received an error. The error stated Compile error Variable not defined. It was on the line item:

    Set my item=myolapp.createitem(olmailitem) with the olmailitem highlighted.

    I have attached a copy of the sample database so you can review and possibly give me some advice. I appreciate all of your help!

    Thanks,
    B&R
    Attached Files Attached Files

Posting Permissions

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