-
Apr 22nd, 2013, 03:52 PM
#1
Thread Starter
New Member
[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
-
Apr 22nd, 2013, 05:16 PM
#2
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.
-
Apr 23rd, 2013, 12:59 AM
#3
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.
-
Apr 23rd, 2013, 02:15 AM
#4
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|