Macro for Excel to Word

R

Rizwan.maniar

New Member
Joined
Oct 13, 2010
Messages
4
Hi
I have a excel file with specification of instruments. Column A contains the name of the instrument and column B to Z contains specification data for the instument in column A. I am struggling to create a macro that would print the specification in a template (Template can be in Excel or Word) when clicked on the name in column A.:confused:
I have to present the solution to the client as soon as possible. Any help will be appriciated.

Thanks
Riz
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Here's the skeleton of some code which allows you to create a Word document and populate it with data from your workbook, text, graphics and symbols:-
Code: 
Option Explicit
 
Public Sub QuickMakeWordDoc()
 
  Dim wordApp As Word.Application
  Set wordApp = CreateObject("Word.Application")
  Dim wordDoc As Word.Document
  Dim wordRange As Word.Range
  Dim strDocumentName As String
    
  With wordApp
  
    .WindowState = Word.WdWindowState.wdWindowStateMaximize
    .Documents.Add ("normal.dotm")
    Set wordDoc = .ActiveDocument
    Set wordRange = wordDoc.Range
    
    With wordRange
      
      .InlineShapes.AddPicture "[COLOR=red][B]c:\temp\logo.jpg[/B][/COLOR]", False, True
      .Move wdStory, 1
      .InlineShapes.AddHorizontalLineStandard
      .Move wdStory, 1
      .InsertAfter vbCrLf
      
      .Font.Bold = False
      .Font.Size = 12
      .ParagraphFormat.Alignment = wdAlignParagraphLeft
  
      .InsertAfter "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has "
      .InsertAfter "been the industry's standard dummy text ever since the 1500s, when an unknown printer took "
      .InsertAfter "a galley of type and scrambled it to make a type specimen book. It has survived not only five "
      .InsertAfter "centuries, but also the leap into electronic typesetting, remaining essentially unchanged."
      
      .InsertAfter "Data from workbook: " & [COLOR=red][B]Sheets("Sheet1").Range("A1")[/B][/COLOR] & vbCrLf & vbCrLf
      
      .InsertAfter "Document created: " & Format(Now(), "dd/mm/yyyy hh:nn:ss") & "." & vbCrLf
                
    End With
    
    .ActiveDocument.SaveAs "[COLOR=red][B]c:\temp\testdoc[/B][/COLOR]"
    .ActiveDocument.Close
    .Application.Quit
    
    Set wordDoc = Nothing
    Set wordApp = Nothing
  
  End With
    
End Sub
You just need to change the bits in red to get it to run from your workbook, then modify it to suit your purposes.

Hope this helps.
 
Upvote 0
Thanks for the reply.
However while running the macro I get "Compile Error: User-defined type not defined" in the first line itself.:(:(

Riz
 
Upvote 0
Sorry, I forgot to mention: you'll need a reference to the Microsoft Word Object Library. In the VBE, go Tools > References, then select it from the list.
 
Upvote 0
Ok...So far So good
Now how do I hyperlink it with cell from column A and use data from other cells of the selected row.

Thanks
riz
 
Upvote 0
When you say 'hyperlink', do you mean you just want to insert data from the worksheet? If so, just use .InsertAfter as in my example:-
Code: 
.InsertAfter [COLOR=red][B]Sheets("Sheet1").Range("A1")[/B][/COLOR]
For multiple cells, you have to determine how you want them presented, so for example, something like this (not tested):-
Code: 
.InsertAfter "Row 5 of my worksheet contains the following data: "
For i = 1 to 12
  .InsertAfter Cells(5,i).Value
  If i < 12 Then
    .InsertAfter ", "
  Else
    .InsertAfter "." & vbCrLf
  Endif
Next i
might produce something like this in your Word document:-

Row 5 of my worksheet contains the following data: 1, 2, 99, 100, 500, 1, 2, 3, , , , 1000.

If you were filling in a template letter to a customer whose order data was in row intCustomerRow of your worksheet:-

<TABLE style="WIDTH: 243pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=323 border=0><COLGROUP><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 34pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #b8cce4" width=45 height=17>A</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=80>B</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 33pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=44>C</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=78>D</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 57pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #b8cce4" width=76>E</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Title</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Surname</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Qty</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Item</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Delivery</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mr</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Smith</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">5</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Widgets</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">3 days</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Ms</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Jones</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Doodahs</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1 week</TD></TR></TBODY></TABLE>
you might end up with code like this:-
Code: 
.InsertAfter "Dear " & Range("A" & intCustomerRow) & " " & Range("B" & intCustomerRow) & "," & vbCrLf
.InsertAfter vbCrLf
.InsertAfter "I am writing to confirm that your order for " & Range("C" & intCustomerRow) & " boxes of " & Range("D" & intCustomerRow) & " "
.InsertAfter "has left our warehouse." & vbCrLf
.InsertAfter vbCrLf
.InsertAfter "Your delivery should be with you in " & Range("E" & intCustomerRow) & "." & vbCrLf
.InsertAfter vbCrLf
.InsertAfter "Yours sincerely," & vbCrLf
Get the idea? Experiment!
 
Upvote 0
Thanks
I think for most part this will help except the hyperlink part.
With hyperlink, I mean on clicking a particular entry of cell in column A, a word report with the data in that row should be displayed.

Thanks again
Riz
 
Upvote 0
Ah, okay: there's a BeforeDoubleClick() event for the worksheeet which you can use to detect when the user double clicks a cell. The address of the cell is in Target, so use Target.Row to determine which row has been clicked, then use the data in that row to populate your Word document.

Oh, and don't forget to set Cancel to True immediately upon entering the subroutine so that your user doesn't end up in edit mode in the cell he's double-clicked.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
VBA - Excel & Word
Replies
2
Views
146
NdNoviceHlp
NdNoviceHlp
A
  • Question
Excel table protection with formula copying
Replies
2
Views
572
Allie_Excel
A
C
  • Question
Moving sheets with macros and buttons into new workbooks
Replies
2
Views
417
CLDMister1xbilliontwos
C
F
  • Question
VBA code giving error on line .Wrap = wdFindContinue
Replies
2
Views
439
NdNoviceHlp
NdNoviceHlp
S
  • Question
Regarding a real time problem I am facing while working.
Replies
1
Views
192
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,566
Messages
6,125,589
Members
449,237
Latest member
Chase S

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