Results 1 to 4 of 4

Thread: [RESOLVED] Exporting data from Excel to Word run-time error 438

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2013
    Posts
    2

    Resolved [RESOLVED] Exporting data from Excel to Word run-time error 438

    I am a novice running VBA in Word 2010. I have data in Excel 2010 that i want to export into Word automatically. The purpose is to automatically print receipts of donations that have been made to our charity and save us having to handwrite them all. The Excel database has a list of all the charitable donations that have been made. I have looked online and through the forums as far as possible and have come up with the following:
    I set a Word document with my template receipt and inserted a text box named "Received" beside the donor's name.
    The code for VBA is as follows:

    Private Sub CommandButton1_Click()

    Dim objExcel As New Excel.Application
    Dim exWb As Excel.Workbook

    Set exWb = objExcel.Workbooks.Open("C:\Users\user\Documents\file1.xls")

    ThisDocument.Received.Caption = exWb.Sheets("Sheet1").Cells(2, 5)

    exWb.Close

    Set exWb = Nothing

    End Sub


    When I run it it returns "Run-time error 438" and when I debug highlights the row beginning ThisDocument. I have tried changing the Cells reference to Range but it returns the same error

    I would be grateful for any help or to point to another post I could refer to. Being able to do this will be very useful. Thanks

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: Exporting data from Excel to Word run-time error 438

    You should always tell what the error message is rather than just the number. The number doesn't tell us much and means we would have to look it up in most cases where you could have simply added it to your post.

    The thread needs to be moved to the Office section as it is not using VB6 but is using VBA instead.

  3. #3
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Exporting data from Excel to Word run-time error 438

    One fairly obvious issue is that a TextBox (in Legacy VB and VBA) doesn't have a 'Caption' Property, you should be using the 'Text' Property. (ThisDocument.Received.Text= exWb.Sheets("Sheet1").Cells(2,5))
    Last edited by Doogle; Apr 23rd, 2013 at 01:02 AM.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2013
    Posts
    2

    Re: Exporting data from Excel to Word run-time error 438

    thanks Doogle! That's fixed it. My apologies DataMiser for posting in the wrong section

Tags for this Thread

Posting Permissions

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



Click Here to Expand Forum to Full Width