Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A way to copy Word text into an Access control?

    I have developed code for quickly writing letters then emailing them by clicking on buttons on an Access form. Each button copies boilerplate text from a "Paragraphs" table and appends it to the text in the letter composition control which is the "Memo" data type. Then, via automating Outlook, it emails the result to the contact's email address. Most letters I send can be thus composed from pre-written paragraphs which are kept in a special Access table. (People keep asking me the same questions!!) However, I now wish to be able to insert the results of an Access report into the email by the same process. The only way I can think of doing this is by using "DoCmd.OutputTo" to convert the report to a text document in Word. I can then copy the document using Control-C, then switching to Access, paste (control-v) the buffer into the Memo field.
    But I want to be able to automate this. You'd think it an easy thing to do but I can't see much that lends itself in VBA. Sure, you can copy text from one control to another using the Value or Text properties and strings but how do you take the body of a text document and turn it into a string. If you could you could simply concatenate strings to get the result. Any ideas?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A way to copy Word text into an Access control?

    Hi,
    Having output the report to a text document, you could simply attach this to the email rather than inserting it into the email's body.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way to copy Word text into an Access control?

    Thanks Rory, I did think of that but it is not really what I want to do. I really wanted the report to be incorporated
    into the plain text body of the email. Also, I'm curious if what I've suggested can be done as it would have many other applications. It is odd that something we do every five minutes (cut and paste) has no easy equivalent in VBA. I am astounded actually. It is almost like having a Pentium iii
    1400Mhz wiz bang computer that can do everything except divide by the number 3.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way to copy Word text into an Access control?

    Well I have come up with a solution to my specific needs but this solution still does not answer the question I originally asked.
    Basically my problem was to take a set of records in a query-generated recordset and include these as a report embedded in an email. I was trying to do this by letting access create the report then export it to a Word document in text format where you could copy and paste the text back into the email (or, rather, the control which turned into an email body later). It occurred to me that you could contruct a string using the query data and things like vbCrLf and vbTab in code which, when entered into the email, gave you a replica of the same report. That is you can use VBA code to "manually" create the same report that Access normally handles automatically. It is becoming more and more common to use VBA in place of standard Access processes to achieve custom results so this is a logical idea. My surprise is that no-one has pointed this out.
    Equally surprising still is the fact that there still seems to be no answer to the need to programmatically copy text from a Word document into a control in Access. It is so easy to do with the mouse and keyboard, why can't VBA handle it?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: A way to copy Word text into an Access control?

    Hi David,
    I've been thinking about your problem and I think you could do what you originally wanted using something along these lines:
    Private Sub Command2_Click()
    Dim appWord As New Word.Application
    Dim docWord As Word.Document
    Set docWord = appWord.Documents.Add
    docWord.StoryRanges(wdMainTextStory) = "hello there"
    Me.txtTextBoxName = Me.docWord.StoryRanges(wdMainTextStory)
    End Sub
    I assume you already have the code to create the word document so all you'd really need would be a variation of the last line. You can incidentally copy ranges from Word using Range.Copy but it's probably slower than directly setting the textbox text to a range.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A way to copy Word text into an Access control?

    Thanks Rory, I will try your idea for another situation.
    For the current one I used code to manually contruct a report as a string so that it appears in its layout like a report created by access. I can post some sample code for anyone interested.

Posting Permissions

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