StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POpasting from excel into a word document
text
Body
copied!<p>i am copying cells from excel into an open word document. the way i am doing this is just copying the contents of a cell into the clipboard and REPLACING a specific KEYWORD in the word document like so:</p> <p>if cell <code>A1 = "some word"</code> i need too replace the string "<code>QUERYA1</code>" in the word document</p> <p>i am doing it like this:</p> <pre><code>Sub NoFormatPaste() wdFind.Replacement.Text = "" wdFind.Forward = True wdFind.Wrap = wdFindContinue wdFind.Execute If IsEmpty(Selection.Text) And Len(Selection.Text) = 0 Then ClipEmpty.PutInClipboard appWd.Selection.PasteSpecial DataType:=wdPasteText End Else appWd.Selection.PasteSpecial DataType:=wdPasteText End If CutCopyMode = False End Sub </code></pre> <p>when this sub runs, it works on every field except it gives an error if the cell is empty. i have this formula in the cell: <code>=+IF(K10="XXX","",K10)</code></p> <p>when this formula yields NOTHING or a blank, and i run my macro, i get an error on PASTING this into word. i am getting an error called <code>4168 command failed/command execution</code> on this line:</p> <pre><code>appWd.Selection.PasteSpecial DataType:=wdPasteText </code></pre> <p>here is my complete code:</p> <pre><code>Dim appWd As Word.Application Dim wdFind As Object Dim ClipEmpty As New MSForms.DataObject Dim ClipT As String Sub FormatPaste() wdFind.Replacement.Text = "" wdFind.Forward = True wdFind.Wrap = wdFindContinue wdFind.Execute If IsEmpty(Selection.Text) And Len(Selection.Text) = 0 Then ClipEmpty.PutInClipboard appWd.Selection.Paste End Else appWd.Selection.Paste End If CutCopyMode = False End Sub Sub NoFormatPaste() wdFind.Replacement.Text = "" wdFind.Forward = True wdFind.Wrap = wdFindContinue wdFind.Execute If IsEmpty(Selection.Text) And Len(Selection.Text) = 0 Then ClipEmpty.PutInClipboard appWd.Selection.PasteSpecial DataType:=wdPasteText End Else appWd.Selection.PasteSpecial DataType:=wdPasteText End If CutCopyMode = False End Sub Sub CopyDatatoWord() Dim docWD As Word.Document Dim sheet1 As Object Dim sheet2 As Object Dim SaveCell1 As String Dim SaveCell2 As String Dim SaveCell3 As String Dim Dir1 As String Dim Dir2 As String Set appWd = CreateObject("Word.Application") appWd.Visible = True 'Set docWD = appWD.Documents.Open("S:\Practice Quarterly Reports\2011 Q1 - V5\Practice Profile Template 2011.docx") Set docWD = appWd.Documents.Open("C:\Documents and Settings\jhill\Desktop\Practice Profile Template 2011.docx") 'Select Sheet where copying from in excel Set sheet1 = Sheets("TABLES") Set sheet2 = Sheets("REPORT INFO") Set wdFind = appWd.Selection.Find ClipT = " " ClipEmpty.SetText ClipT sheet1.Range("B3:B6").Copy wdFind.Text = "Qwerty01" Call FormatPaste sheet1.Range("B10:B15").Copy wdFind.Text = "Qwerty02" Call FormatPaste sheet1.Range("C21:D28").Copy wdFind.Text = "Qwerty03" Call FormatPaste sheet1.Range("B32:F42").Copy wdFind.Text = "Qwerty04" Call FormatPaste sheet1.Range("B46:D52").Copy wdFind.Text = "Qwerty05" Call FormatPaste sheet1.Range("B58:F68").Copy wdFind.Text = "Qwerty06" Call FormatPaste sheet1.Range("B74:G84").Copy wdFind.Text = "Qwerty07" Call FormatPaste sheet1.Range("B87").Copy wdFind.Text = "Qwerty08" Call NoFormatPaste sheet1.Range("B88").Copy wdFind.Text = "Qwerty09" Call NoFormatPaste sheet1.Range("B89").Copy wdFind.Text = "Qwerty10" Call NoFormatPaste sheet1.Range("B90").Copy wdFind.Text = "Qwerty11" Call NoFormatPaste sheet1.Range("B91").Copy wdFind.Text = "Qwerty12" Call NoFormatPaste sheet1.Range("B92").Copy wdFind.Text = "Qwerty13" Call NoFormatPaste sheet1.Range("B93").Copy wdFind.Text = "Qwerty14" Call NoFormatPaste sheet1.Range("B94").Copy wdFind.Text = "Qwerty15" Call NoFormatPaste sheet2.Range("D4").Copy wdFind.Text = "Qwerty16" Call NoFormatPaste sheet2.Range("B5").Copy wdFind.Text = "Qwerty17" Call NoFormatPaste sheet2.Range("D4").Copy wdFind.Text = "Qwerty18" Call NoFormatPaste sheet2.Range("B8").Copy wdFind.Text = "Qwerty19" Call NoFormatPaste sheet2.Range("B9").Copy wdFind.Text = "Qwerty20" Call NoFormatPaste sheet2.Range("B10").Copy wdFind.Text = "Qwerty21" Call NoFormatPaste sheet2.Range("B11").Copy wdFind.Text = "Qwerty22" Call NoFormatPaste sheet2.Range("B12").Copy wdFind.Text = "Qwerty23" Call NoFormatPaste sheet2.Range("B13").Copy wdFind.Text = "Qwerty24" Call NoFormatPaste sheet2.Range("B14").Copy wdFind.Text = "Qwerty25" Call NoFormatPaste sheet2.Range("B15").Copy wdFind.Text = "Qwerty26" Call NoFormatPaste sheet2.Range("B16").Copy wdFind.Text = "Qwerty27" Call NoFormatPaste sheet2.Range("B17").Copy wdFind.Text = "Qwerty28" Call NoFormatPaste sheet2.Range("B5").Copy wdFind.Text = "Qwerty29" Call NoFormatPaste sheet2.Range("B5").Copy wdFind.Text = "Qwerty30" Call NoFormatPaste sheet2.Range("B5").Copy wdFind.Text = "Qwerty31" Call NoFormatPaste SaveCell1 = sheet2.Range("D3").Text SaveCell2 = sheet2.Range("B6").Text SaveCell3 = SaveCell2 & "\" & SaveCell1 Dir1 = "\\annapurna\Shared\Practice Quarterly Reports\2011 Q1 - V5\ & SaveCell2" Dir2 = "\\annapurna\Shared\Practice Quarterly Reports\2011 Q1 - V5\ & SaveCell3" If Len(Dir1) = False Then MkDir Dir1 End If 'docWD.SaveAs (Dir2 & ".docx") docWD.SaveAs ("\\annapurna\Shared\Practice Quarterly Reports\Q1_2011\Test.docx") 'appWD.Quit Set appWd = Nothing Set docWD = Nothing Set appXL = Nothing Set wbXL = Nothing End Sub </code></pre> <p>what am i doing wrong? what is the reason i get an error only on the paste of a blank</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload