Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 08-12-2018, 10:06 PM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Post 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
Attached Files
File Type: xlsx Data to fill.xlsx (10.9 KB, 3 views)
File Type: docx Fill from excel.docx (47.9 KB, 4 views)
Reply With Quote
  #2  
Old 08-12-2018, 10:53 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

This has been answered in different ways in multiple posts...

See, for example: http://www.msofficeforums.com/word-v...html#post46287
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 08-12-2018, 11:43 PM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

Hi Paul,

If you provide more specified macro based on my attachments it will helpful for me..

thanks
Reply With Quote
  #4  
Old 08-13-2018, 01:33 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

In your first post, you said:
Quote:
Originally Posted by ganesang View Post
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.
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
[MS MVP - Word]
Reply With Quote
  #5  
Old 08-13-2018, 02:23 AM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 08-13-2018, 03:36 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
[MS MVP - Word]
Reply With Quote
  #7  
Old 08-13-2018, 03:41 AM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

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...
Reply With Quote
  #8  
Old 08-13-2018, 04:20 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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
Note: You would get more meaningful content in the dropdowns if your Excel data actually matched the dropdown choices...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 08-13-2018, 05:06 AM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

Thanks for the code Paul

I will check and get back to you....
Reply With Quote
  #10  
Old 08-13-2018, 10:10 PM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

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..
Attached Images
File Type: png Excel object.PNG (254.4 KB, 10 views)
Reply With Quote
  #11  
Old 08-13-2018, 11:20 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The code works fine for me. Which code line was highlighted when the error was reported?
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #12  
Old 08-13-2018, 11:23 PM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

There is no line highlighted when error was reported.

Please find the screenshot.
Attached Images
File Type: png Error 424.PNG (213.2 KB, 9 views)
Reply With Quote
  #13  
Old 08-13-2018, 11:25 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Try logging off, then back on again - and don't open the workbook before running the macro.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #14  
Old 08-13-2018, 11:35 PM
ganesang ganesang is offline Windows XP Office 2016
Advanced Beginner
 
Join Date: Jul 2018
Posts: 42
ganesang is on a distinguished road
Default

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?
Reply With Quote
  #15  
Old 08-13-2018, 11:44 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,045
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

The macro is a Word macro, not an Excel macro. You add it to the document, not to the workbook...
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 04:55 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft