#1
|
|||
|
|||
Need macro to fill the values in msword based on the excel sheet info
Hi Guys,
I had done several researches in this forum, but i am not able to find to fill values from the drop-down in ms-word based on the excel sheet info's. Here i have attached the both word and excel sheet needs to be fill values in ms-word from excel sheet. I have added the comments in the word file which is the exact direction needs to be done. Can anyone help out on this??? Thanks in advance!! Ganesan. G |
#2
|
||||
|
||||
This has been answered in different ways in multiple posts...
See, for example: https://www.msofficeforums.com/word-...html#post46287
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul,
If you provide more specified macro based on my attachments it will helpful for me.. thanks |
#4
|
||||
|
||||
In your first post, you said:
Having looked at both your document and the workbook, it's not apparent any dropdown values are being filled - what you seem to want is a dropdown display name selected. Furthermore, it's not apparent why, if all you want is a set of fields in the document to be populated from an Excel workbook, you wouldn't do the lot via a mailmerge.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Thanks for the reply Paul
Is there any options to select from the drop-down based on the workbook values... For ex., in excel "Difficulty Level" is "Esy", in the word to be filled from the drop-down to "1-Easy" etc... Please advise. |
#6
|
||||
|
||||
Unless you're proposing to allow someone to change the selection after it's been populated from Excel, there is no reason to use a dropdown.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
|||
|
|||
Yes you're correct...
Someone needs to be changed are updated drop-down list against the excel sheet after populated in the word file maybe after sometime. That's why asking as drop-down list... |
#8
|
||||
|
||||
Try:
Code:
Sub GetExcelData() Application.ScreenUpdating = False 'Note: A VBA Reference to the Excel Object Model is required, via Tools|References Dim xlApp As New Excel.Application, xlWkBk As Excel.Workbook, xlWkSht As Excel.Worksheet Dim StrWkBkNm As String, i As Long, j As Long, r As Long StrWkBkNm = ThisDocument.Path & "\Data to fill.xlsx" If Dir(StrWkBkNm) = "" Then MsgBox "Cannot find the designated workbook: " & StrWkBkNm, vbExclamation Exit Sub End If With xlApp .Visible = False .DisplayAlerts = False Set xlWkBk = .Workbooks.Open(FileName:=StrWkBkNm, ReadOnly:=True, AddToMRU:=False) End With Set xlWkSht = xlWkBk.Worksheets("Sheet1") With ActiveDocument For i = 1 To .Tables.Count r = i + 1 With .Tables(i) .Cell(2, 2).Range.Text = "PV: " & xlWkSht.Range("B" & r).Value With .Cell(3, 2).Range .ContentControls(1).Checked = (xlWkSht.Range("F" & r).Value = "Y") .ContentControls(2).Checked = (xlWkSht.Range("F" & r).Value = "Y") .ContentControls(3).Checked = (xlWkSht.Range("G" & r).Value = "Y") End With With .Cell(3, 1).Range With .ContentControls(1) .Type = wdContentControlText .Range.Text = xlWkSht.Range("A" & r).Value .Type = wdContentControlDropdownList End With With .ContentControls(2) .Type = wdContentControlText .Range.Text = xlWkSht.Range("D" & r).Value .Type = wdContentControlDropdownList End With With .ContentControls(3) .Type = wdContentControlText .Range.Text = xlWkSht.Range("C" & r).Value .Type = wdContentControlDropdownList End With End With End With Next End With xlWkBk.Close False xlApp.Quit Set xlWkSht = Nothing: Set xlWkBk = Nothing: Set xlApp = Nothing Application.ScreenUpdating = True End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
|||
|
|||
Thanks for the code Paul
I will check and get back to you.... |
#10
|
|||
|
|||
Hi Paul!
I am just trying to run this macro but the run time error 424 occurred. I have checked the excel object library via reference in VBA. Please find the attached.. Please check and advise on this.. |
#11
|
||||
|
||||
The code works fine for me. Which code line was highlighted when the error was reported?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#12
|
|||
|
|||
There is no line highlighted when error was reported.
Please find the screenshot. |
#13
|
||||
|
||||
Try logging off, then back on again - and don't open the workbook before running the macro.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#14
|
|||
|
|||
Hi Paul
Still having the issue when i try to run the macro. I am just open the empty macro enabled workbook and paste the code and run it. Both workbook and doc file placed in the same path. Is this right way? If not, can you please tell me the way to run? |
#15
|
||||
|
||||
The macro is a Word macro, not an Excel macro. You add it to the document, not to the workbook...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need macro to fill data from different sheets based on selected item from drop down | skorasika | Excel Programming | 1 | 03-13-2015 11:25 AM |
Newbie to excel for starters, needing to transfer info from sheet2 to universe sheet. | rogcar75 | Excel | 0 | 08-12-2014 07:21 AM |
Color-fill a range of cells, based on text in a different sheet. Possible? | unittwentyfive | Excel | 2 | 06-01-2014 06:48 AM |
link conditional info in word based on excel list | stijnvanhoof | Mail Merge | 1 | 11-13-2012 01:55 PM |
Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248 | Joe Patrick | Word VBA | 2 | 01-30-2012 07:23 AM |