Excel Data to Word Document

F

flumpajk

New Member
Joined
Jun 5, 2012
Messages
4
Hi all,

Thank you for the help in advance. I have the following code that I have used and created a Word document with bookmarks, however when I run the code it opens a the document, but does not populate it with any variables from the Excel data sheet. If I only use the first line "Buyer" it will populate. But once I introduce the remainder of the code, the document opens but none of the bookmarks populate.

Any help would be great.

Thanks

Code: 
Sub CreateStandardTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Buyer As Excel.Range
Dim BuyerN As Excel.Range
Dim BuyerP As Excel.Range
Dim BuyerF As Excel.Range
Dim BuyerE As Excel.Range
Dim DispatchName As Excel.Range
Dim DispatchPhone As Excel.Range
Dim DispatchFax As Excel.Range
Dim DispatchEmail As Excel.Range
Dim AccountMgr As Excel.Range
Dim AccountMgrP As Excel.Range
Dim AccountMgrF As Excel.Range
Dim AccountMgrE As Excel.Range
Dim BusinessAdd As Excel.Range
Dim BusinessAdd2 As Excel.Range
Dim NoticeAdd As Excel.Range
Dim NoticeAdd2 As Excel.Range
Dim DUNS As Excel.Range
Dim RTLG As Excel.Range
Dim ContractPrice As Excel.Range
Dim TransDate As Excel.Range
Dim PricingMech As Excel.Range
Dim DeliveryPeriod As Excel.Range
Dim DeliveryPoint As Excel.Range
Dim LDC As Excel.Range
Dim Rate As Excel.Range
Dim AccountNum As Excel.Range
Dim LDCAgreementQty As Excel.Range
Dim ServiceAdd As Excel.Range
Dim ServiceAdd2 As Excel.Range
Dim Volumes As Excel.Range
Dim Swing As Excel.Range
Dim Incremental1 As Excel.Range
Dim Incremental2 As Excel.Range
Dim CharacterService As Excel.Range
Dim BuyerSig As Excel.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="G:Standard.doc")
Set Buyer = Sheets("Done Deal - Revised").Range("B8")
Set BuyerN = Sheets("Done Deal - Revised").Range("B10")
Set BuyerP = Sheets("Done Deal - Revised").Range("B11")
Set BuyerF = Sheets("Done Deal - Revised").Range("B12")
Set BuyerE = Sheets("Done Deal - Revised").Range("B13")
Set DispatchName = Sheets("Done Deal - Revised").Range("B15")
Set DispatchPhone = Sheets("Done Deal - Revised").Range("B16")
Set DispatchFax = Sheets("Done Deal - Revised").Range("B17")
Set DispatchEmail = Sheets("Done Deal - Revised").Range("B18")
Set AccountMgr = Sheets("Done Deal - Revised").Range("D3")
Set AccountMgrP = Sheets("Done Deal - Revised").Range("D4")
Set AccountMgrF = Sheets("Done Deal - Revised").Range("D5")
Set AccountMgrE = Sheets("Done Deal - Revised").Range("D6")
Set BusinessAdd = Sheets("Done Deal - Revised").Range("B20")
Set BusinessAdd2 = Sheets("Done Deal - Revised").Range("B21")
Set NoticeAdd = Sheets("Done Deal - Revised").Range("B23")
Set NoticeAdd2 = Sheets("Done Deal - Revised").Range("B24")
Set DUNS = Sheets("Done Deal - Revised").Range("B26")
Set ContractPrice = Sheets("Done Deal - Revised").Range("B28")
Set TransDate = Sheets("Done Deal - Revised").Range("B30")
Set PricingMech = Sheets("Done Deal - Revised").Range("B32")
Set DeliveryPeriod = Sheets("Done Deal - Revised").Range("B34")
Set DeliveryPoint = Sheets("Done Deal - Revised").Range("B36")
Set LDC = Sheets("Done Deal - Revised").Range("B38")
Set AccountNum = Sheets("Done Deal - Revised").Range("B40")
Set LDCAgreementQty = Sheets("Done Deal - Revised").Range("B41")
Set ServiceAdd = Sheets("Done Deal - Revised").Range("42")
Set ServcieAdd2 = Sheets("Done Deal - Revised").Range("B43")
Set Volumes = Sheets("Done Deal - Revised").Range("C57:H78")
Set Swing = Sheets("Done Deal - Revised").Range("B45")
Set Incremental1 = Sheets("Done Deal - Revised").Range("C43")
Set Incremental2 = Sheets("Done Deal - Revised").Range("C48")
Set CharacterService = Sheets("Done Deal - Revised").Range("B55")
Set BuyerSig = Sheets("Done Deal - Revised").Range("B8")
With myDoc.Bookmarks
.Item("Buyer").Range.InsertAfter Buyer.Text
.Item("BuyerN").Range.InsertAfter BuyerName.Text
.Item("BuyerP").Range.InsertAfter BuyerName.Text
.Item("BuyerF").Range.InsertAfter BuyerName.Text
.Item("BuyerE").Range.InsertAfter BuyerName.Text
.Item("DispatchName").Range.InsertAfter BuyerName.Text
.Item("DispatchPhone").Range.InsertAfter BuyerName.Text
.Item("DispatchFax").Range.InsertAfter BuyerName.Text
.Item("DispatchEmail").Range.InsertAfter BuyerName.Text
.Item("AccountMgr").Range.InsertAfter BuyerName.Text
.Item("AccountMgrP").Range.InsertAfter BuyerName.Text
.Item("AccountMgrF").Range.InsertAfter BuyerName.Text
.Item("AccountMgrE").Range.InsertAfter BuyerName.Text
.Item("BusinessAdd").Range.InsertAfter BuyerName.Text
.Item("BusinessAdd2").Range.InsertAfter BuyerName.Text
.Item("NoticeAdd").Range.InsertAfter BuyerName.Text
.Item("NoticeAdd2").Range.InsertAfter BuyerName.Text
.Item("DUNS").Range.InsertAfter BuyerName.Text
.Item("ContractPrice").Range.InsertAfter BuyerName.Text
.Item("TransDate").Range.InsertAfter BuyerName.Text
.Item("PricingMech").Range.InsertAfter BuyerName.Text
.Item("DeliveryPeriod").Range.InsertAfter BuyerName.Text
.Item("DeliveryPoint").Range.InsertAfter BuyerName.Text
.Item("LDC").Range.InsertAfter BuyerName.Text
.Item("AccountNum").Range.InsertAfter BuyerName.Text
.Item("LDCAgreementQty").Range.InsertAfter BuyerName.Text
.Item("ServiceAdd").Range.InsertAfter BuyerName.Text
.Item("ServiceAdd2").Range.InsertAfter BuyerName.Text
.Item("Volumes").Range.InsertAfter BuyerName.Text
.Item("Swing").Range.InsertAfter BuyerName.Text
.Item("Incremental1").Range.InsertAfter BuyerName.Text
.Item("Incremental2").Range.InsertAfter BuyerName.Text
.Item("CharacterService").Range.InsertAfter BuyerName.Text
.Item("BuyerSig").Range.InsertAfter BuyerName.Text
End With
errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
Ha, thanks for the catch. Now the only hicup is the
Code: 
Set Volumes = Sheets("Done Deal - Revised").Range("C57:H78")
where it should be selecting a range of cells and inserting it in a certain position on the word document.

Thanks


Code: 
Sub CreateStandardTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Buyer As Excel.Range
Dim BuyerN As Excel.Range
Dim BuyerP As Excel.Range
Dim BuyerF As Excel.Range
Dim BuyerE As Excel.Range
Dim DispatchName As Excel.Range
Dim DispatchPhone As Excel.Range
Dim DispatchFax As Excel.Range
Dim DispatchEmail As Excel.Range
Dim AccountMgr As Excel.Range
Dim ActMgrP As Excel.Range
Dim ActMgrF As Excel.Range
Dim ActMgrE As Excel.Range
Dim BusinessAdd As Excel.Range
Dim BusinessAdd2 As Excel.Range
Dim NoticeAdd As Excel.Range
Dim NoticeAdd2 As Excel.Range
Dim DUNS As Excel.Range
Dim ContractPrice As Excel.Range
Dim TransDate As Excel.Range
Dim PricingMech As Excel.Range
Dim DeliveryPeriod As Excel.Range
Dim DeliveryPoint As Excel.Range
Dim LDC As Excel.Range
Dim Rate As Excel.Range
Dim AccountNum As Excel.Range
Dim LDCAgreementQty As Excel.Range
Dim ServiceAdd As Excel.Range
Dim ServiceAdd2 As Excel.Range
Dim Volumes As Excel.Range
Dim Swing As Excel.Range
Dim Incremental1 As Excel.Range
Dim Incremental2 As Excel.Range
Dim CharacterService As Excel.Range
Dim BuyerSig As Excel.Range
Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="G:\EMC\Gas\Retail Gas\Contracts\Automation Test\PPL Retail Gas Agreement - FINAL 9-26-11 Standard.doc")
Set Buyer = Sheets("Done Deal - Revised").Range("B8")
Set BuyerN = Sheets("Done Deal - Revised").Range("B10")
Set BuyerP = Sheets("Done Deal - Revised").Range("B11")
Set BuyerF = Sheets("Done Deal - Revised").Range("B12")
Set BuyerE = Sheets("Done Deal - Revised").Range("B13")
Set DispatchName = Sheets("Done Deal - Revised").Range("B15")
Set DispatchPhone = Sheets("Done Deal - Revised").Range("B16")
Set DispatchFax = Sheets("Done Deal - Revised").Range("B17")
Set DispatchEmail = Sheets("Done Deal - Revised").Range("B18")
Set AccountMgr = Sheets("Done Deal - Revised").Range("D3")
Set ActMgrP = Sheets("Done Deal - Revised").Range("D4")
Set ActMgrF = Sheets("Done Deal - Revised").Range("D5")
Set ActMgrE = Sheets("Done Deal - Revised").Range("D6")
Set BusinessAdd = Sheets("Done Deal - Revised").Range("B20")
Set BusinessAdd2 = Sheets("Done Deal - Revised").Range("B21")
Set NoticeAdd = Sheets("Done Deal - Revised").Range("B23")
Set NoticeAdd2 = Sheets("Done Deal - Revised").Range("B24")
Set DUNS = Sheets("Done Deal - Revised").Range("B26")
Set ContractPrice = Sheets("Done Deal - Revised").Range("B28")
Set TransDate = Sheets("Done Deal - Revised").Range("B30")
Set PricingMech = Sheets("Done Deal - Revised").Range("B32")
Set DeliveryPeriod = Sheets("Done Deal - Revised").Range("B34")
Set DeliveryPoint = Sheets("Done Deal - Revised").Range("B36")
Set LDC = Sheets("Done Deal - Revised").Range("B38")
Set Rate = Sheets("Done Deal - Revised").Range("B39")
Set AccountNum = Sheets("Done Deal - Revised").Range("B40")
Set LDCAgreementQty = Sheets("Done Deal - Revised").Range("B41")
Set ServiceAdd = Sheets("Done Deal - Revised").Range("B42")
Set ServiceAdd2 = Sheets("Done Deal - Revised").Range("B43")
Set Volumes = Sheets("Done Deal - Revised").Range("C57:H78")
Set Swing = Sheets("Done Deal - Revised").Range("B45")
Set Incremental1 = Sheets("Done Deal - Revised").Range("C43")
Set Incremental2 = Sheets("Done Deal - Revised").Range("C48")
Set CharacterService = Sheets("Done Deal - Revised").Range("B55")
Set BuyerSig = Sheets("Done Deal - Revised").Range("B8")
With myDoc.Bookmarks
.Item("Buyer").Range.InsertAfter Buyer.Text
.Item("BuyerN").Range.InsertAfter BuyerN.Text
.Item("BuyerP").Range.InsertAfter BuyerP.Text
.Item("BuyerF").Range.InsertAfter BuyerF.Text
.Item("BuyerE").Range.InsertAfter BuyerE.Text
.Item("DispatchName").Range.InsertAfter DispatchName.Text
.Item("DispatchPhone").Range.InsertAfter DispatchPhone.Text
.Item("DispatchFax").Range.InsertAfter DispatchFax.Text
.Item("DispatchEmail").Range.InsertAfter DispatchEmail.Text
.Item("AccountMgr").Range.InsertAfter AccountMgr.Text
.Item("ActMgrP").Range.InsertAfter ActMgrP.Text
.Item("ActMgrF").Range.InsertAfter ActMgrF.Text
.Item("ActMgrE").Range.InsertAfter ActMgrE.Text
.Item("BusinessAdd").Range.InsertAfter BusinessAdd.Text
.Item("BusinessAdd2").Range.InsertAfter BusinessAdd2.Text
.Item("NoticeAdd").Range.InsertAfter NoticeAdd.Text
.Item("NoticeAdd2").Range.InsertAfter NoticeAdd2.Text
.Item("DUNS").Range.InsertAfter DUNS.Text
.Item("ContractPrice").Range.InsertAfter ContractPrice.Text
.Item("TransDate").Range.InsertAfter TransDate.Text
.Item("PricingMech").Range.InsertAfter PricingMech.Text
.Item("DeliveryPeriod").Range.InsertAfter DeliveryPeriod.Text
.Item("DeliveryPoint").Range.InsertAfter DeliveryPoint.Text
.Item("LDC").Range.InsertAfter LDC.Text
.Item("Rate").Range.InsertAfter Rate.Text
.Item("AccountNum").Range.InsertAfter AccountNum.Text
.Item("LDCAgreementQty").Range.InsertAfter LDCAgreementQty.Text
.Item("ServiceAdd").Range.InsertAfter ServiceAdd.Text
.Item("ServiceAdd2").Range.InsertAfter ServiceAdd2.Text
.Item("Volumes").Range.InsertAfter Volumes.Text
.Item("Swing").Range.InsertAfter Swing.Text
.Item("Incremental1").Range.InsertAfter Incremental1.Text
.Item("Incremental2").Range.InsertAfter Incremental2.Text
.Item("CharacterService").Range.InsertAfter CharacterService.Text
.Item("BuyerSig").Range.InsertAfter BuyerSig.Text
End With
errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub
 
Upvote 0
OK, you're dusting off the cobwebs.

Can you simply

Code: 
.Item("Volumes").Range.InsertAfter Volumes
 
Upvote 0
I tried that and it did not work. Could it be something surounding going from a single cell pasted to essentially having to paste a chart into Word?
 
Upvote 0
I was able to 'manually' handle the inserting of the chart, but I was thinking it was possible by defining an object.

Code: 
Sheets("Done Deal - Revised").Range("C57:H78").Copy
.....
.Item("Volumes").Select
.Item("Volumes").Range.Paste
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Copying data from multiple sheets and pasting it on a single destination sheet
Replies
5
Views
904
NdNoviceHlp
NdNoviceHlp
L
  • Solved
Pasting an Excel range of data into a word document but after a logo and Title
Replies
9
Views
1K
NdNoviceHlp
NdNoviceHlp
H
  • Question
Check this code for Copy/Paste Excel Tables(s) into a Word document and Fit the table width
Replies
0
Views
1K
HassanKM
H
G
  • Question
Need help to convert Excel to Word
Replies
2
Views
454
GirishDhruva
G
R
  • Question
Break save a word document from excel using VBA
Replies
1
Views
1K
sijpie
sijpie

Forum statistics

Threads
1,203,205
Messages
6,054,140
Members
444,703
Latest member
pinkyar23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top