#31
|
||||
|
||||
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, x 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: x = 0 With .Tables(i) With .Range .Find.Execute FindText:="<PV:>", MatchWildcards:=True, Wrap:=wdFindStop If .Find.Found = True Then x = .Range.Cells(1).RowIndex End With If x > 0 Then .Cell(x, 2).Range.Text = "PV: " & xlWkSht.Range("B" & r).Value With .Cell(x + 1, 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(x + 1, 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 If 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] |
#32
|
|||
|
|||
Thanks Much Paul!!
|
#33
|
|||
|
|||
Hi Paul!!
Thanks for provide the new code. When i try to run this code i have the error on line no 23.... Please find the screenshot and advise. |
#34
|
||||
|
||||
Change:
x = .Range.Cells(1).RowIndex to: x = .Cells(1).RowIndex
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#35
|
|||
|
|||
Hi Paul
Thanks for the reply. Still the check boxes are not checked for versions (Version1, Version2, Version3). Please check and advise. |
#36
|
||||
|
||||
They were in the code for the sample document you provided. If they're not being checked now, that suggests there are yet more differences than you admitted to in posts 28 & 30...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#37
|
|||
|
|||
Hi Paul
Thanks for the reply. If any para exist in the "PV" cell, how to retain that para without deleting that one? Also, the excel data that i provided sometimes protected. So, is there any possible to detect the values from the drop down in the word based on the excel data instead of copy (ex., Esy=1-Easy etc...) from the excel (using "case" option). Please advise. thanks again ganesang |
#38
|
||||
|
||||
Yet another difference you failed to mention...
To deal with that, you might change: .Cell(x, 2).Range.Text = "PV: " & xlWkSht.Range("B" & r).Value to: .Cell(x, 2).Range.InsertAfter xlWkSht.Range("B" & r).Value
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#39
|
|||
|
|||
Hi Paul
Yes!. i forgot to mention that.. I am little learner in VBA. So please forgive me if i am not on the same route. IF, Insertion text (PV if may be add with some additional text, how will change the code? for ex., from "PV:" to insert "PV:0" |
#40
|
||||
|
||||
The last lot of code I gave you inserts the string at the end of the cell; it has no effect on anything already there.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#41
|
|||
|
|||
Hi Paul
If i have to change from "PV: RR" to "PV: 0" how code will change? |
#42
|
||||
|
||||
I am getting tired of your seemingly never-ending revisiting this topic because of your document's inconsistencies. Instead of expecting me to modify the code, how about you doing a Find/Replace, to change "PV: RR" to "PV: "???
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#43
|
|||
|
|||
Sorry for the inconvenience Paul
I don't realize that there are more inconsistencies found this document. Anyway, now if i found "PV: RR", then needs to be insert after as "PV:[point value from excel]" The above coding you given on post 38 only insert what we found. Please advise. |
#44
|
||||
|
||||
I have already suggested how you can fix that...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#45
|
|||
|
|||
The above coding print like that, "PV: RR" to "PV: RR1" instead of "PV:1"
|
|
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 |