Link Excel to word document with VBA by selecting specific rows

%3CLINGO-SUB%20id%3D%22lingo-sub-1901149%22%20slang%3D%22en-US%22%3ELink%20Excel%20to%20word%20document%20with%20VBA%20by%20selecting%20specific%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1901149%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone!%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20word%20document%20from%20an%20Excel%20sheet.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20instance%2C%20in%20my%20excel%20table%2C%20I%20have%20different%20columns.%20One%20of%20them%20has%20the%20title%20of%20%22colour%22.%20I%20can%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20this%20column%20by%20the%20name%20of%20the%20different%20colours%20(eg%20%22blue%22%20%2C%20%22green%22%2C%20%22yellow%22%20etc.).%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20%3CSTRONG%3EI%20filter%20my%20column%20with%20the%20colour%20%22blue%22.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EWhat%20I%20would%20like%20to%20do%20in%20a%20%3CSTRONG%3EVBA%20script%3C%2FSTRONG%3E%2C%20is%20to%20%3CU%3Eselect%20all%20these%20rows%20that%20have%20the%20colour%20%22blue%22%20in%20commun%20and%20copy-paste%20them%20in%20a%20word%20document.%3C%2FU%3E%3C%2FP%3E%3CP%3EI%20thought%20I%20had%20it%2C%20but%20with%20the%20script%20I%20am%20using%2C%20I%20do%20not%20know%20why%2C%20but%20only%20the%20titles%20of%20my%20different%20columns%20is%20pasted%2C%20regardless%20what%20I%20define.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20already%20done%20such%20a%20thing%2C%20or%20knows%20how%20to%20fix%20this%20script%2C%20I%20would%20be%20grateful%20for%20your%20help!%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ED%C3%A9borah%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20TestOne()%0A%0ADim%20appWD%20As%20Word.Application%2C%20wbXL%20As%20Excel.Workbook%0A%0ASet%20appWD%20%3D%20CreateObject(%22Word.Application.16%22)%0AappWD.Visible%20%3D%20True%0A%0Adebut%20%3D%20Range(%22A13%22).End(xlUp).Row%0Afin%20%3D%20Range(%22A15%22).End(xlUp).Row%0A%0AFor%20i%20%3D%20debut%20To%20fin%0A%20%20%20%20'Copy%20the%20current%20row%0A%20%20%20%20Worksheets(%22Sheet1%22).Rows(i).Copy%0A%20%20%20%20'tell%20word%20to%20create%20new%20document.%0A%20%20%20%20appWD.Documents.Add%0A%20%20%20%20'Tell%20Word%20to%20paste%20the%20contents%20of%20the%20clipboard%20into%20the%20new%20document.%0A%20%20%20%20appWD.Selection.Paste%0ANext%20i%0A%0A'Close%20the%20new%20Word%20document.%0AappWD.ActiveDocument.Close%0A%0A'Save%20the%20new%20document%20with%20a%20sequential%20file%20name.%0AappWD.ActiveDocument.SaveAs%20Filename%3A%3D%22File%22%0A%0A%0A'%20Close%20the%20new%20Word%20application.%0AappWD.Quit%0ASet%20appWD%20%3D%20Nothing%0A%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1901149%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1902356%22%20slang%3D%22de-DE%22%3ESubject%3A%20Link%20Excel%20to%20word%20document%20with%20VBA%20selecting%20by%20specific%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1902356%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871689%22%20target%3D%22_blank%22%3E%40dfhidromod%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWhen%20I%20try%20to%20combine%20your%20description%20with%20your%20code%2C%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20get%20totally%20confused%20and%20can't%20understand%20your%20plan.%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EMaybe%20it's%20me%2C%20maybe%20it's%20in%20the%20translation%20or%20maybe%20it's%20Johnny's%20fault...%20Johnny%20Walker%20the%20blue%20bottle%20%3A).%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ETherefore%2C%20I%20would%20recommend%20you%20send%20a%20file%20(without%20sensitive%20data)%20where%20you%20can%20explain%20your%20plan%20on%20the%20basis%20of%20the%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3ESo%20everyone%20is%20helped%2C%20the%20helpers%20and%20the%20helping%20ones.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*Knowledge%20of%20Excel%20version%20and%20the%20operating%20system%20is%20a%20must%20have%20if%20you%20want%20to%20proposing%20a%20reasonable%20solution%20(Example%3A%20office%20version%20e.g.%202016%20or%202019%20or%20365%20web%20or%20365%20pro%2C%20etc.%20and%20your%20operating%20system%20(e.g.%20Win10%20(2004)%2C%20Win%2010%20(1903)%2C%20Mac%2C%20etc.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hi everyone!

I am trying to create a word document from an Excel sheet. 

For instance, in my excel table, I have different columns. One of them has the title of "colour". I can FILTER this column by the name of the different colours (eg "blue" , "green", "yellow" etc.). 

Let's say I filter my column with the colour "blue".

What I would like to do in a VBA script, is to select all these rows that have the colour "blue" in commun and copy-paste them in a word document.

I thought I had it, but with the script I am using, I do not know why, but only the titles of my different columns is pasted, regardless what I define.

 

If anyone has already done such a thing, or knows how to fix this script, I would be grateful for your help!

Cheers,

 

Déborah

Sub TestOne()

Dim appWD As Word.Application, wbXL As Excel.Workbook

Set appWD = CreateObject("Word.Application.16")
appWD.Visible = True

debut = Range("A13").End(xlUp).Row
fin = Range("A15").End(xlUp).Row

For i = debut To fin
    'Copy the current row
    Worksheets("Sheet1").Rows(i).Copy
    'tell word to create new document.
    appWD.Documents.Add
    'Tell Word to paste the contents of the clipboard into the new document.
    appWD.Selection.Paste
Next i

'Close the new Word document.
appWD.ActiveDocument.Close

'Save the new document with a sequential file name.
appWD.ActiveDocument.SaveAs Filename:="File"


' Close the new Word application.
appWD.Quit
Set appWD = Nothing

End Sub

 

 

1 Reply
Highlighted

@dfhidromod 

When I try to combine your description with your code,

I get totally confused and can't understand your plan.

Maybe it's me, maybe it's in the translation or maybe it's Johnny’s fault... Johnny Walker the blue bottle :).

 

Therefore, I would recommend you send a file (without sensitive data) where you can explain your plan on the basis of the file. So everyone is helped, the helpers and the helping ones.

 

*Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

 

Thank you for your understanding and patience

 

Cheers

Nikolino

 

I know I don't know anything (Socrates)