Posts by sleeper108

Re: Accessing word macros from excel

I am getting a

Quote

Run-time error '9':

Subscript out of range

and here's the code I was trying from above

Option Explicit 
Sub FindReplaceSample() '
    Dim my_replacements(2, 2)
    Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
    Dim wrdDoc As Object '// Word.Document       same
    Dim iTemp As Integer
     
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Open("C:\Users\my comp\Desktop\test.docx")
     
    Const wdReplaceAll As Integer = 2
     
     '// Set wrdDoc1 = wrdApp.ActiveDocument
     
    my_replacements(1, 1) = "[cli]"
    my_replacements(1, 2) = "[cli-ad]"
    my_replacements(1, 3) = "[da-ac]"
    my_replacements(1, 4) = "[pp-co]"
    my_replacements(1, 5) = "[pp-dri]"
    my_replacements(1, 6) = "[cli-ins]"
    my_replacements(1, 7) = "[pp-ins]"
    my_replacements(1, 8) = "[test]"
    
     
    For iTemp = 1 To 2 '       reaches breakpoint 1 OK
        With wrdDoc.Range.Find
             '// Search value in cell
            .Text = my_replacements(iTemp, 1)
            .Replacement.Text = my_replacements(iTemp, 2)
             '// Other parameters - check if are as required.
            .Forward = True
             '// Go replace
            .Execute Replace:=wdReplaceAll '    reaches breakpoint 2 here
        End With
    Next
     '   fails with needs object - wrdDoc & wrdDoc1 appears as "Document1"
    With wrdApp
        .ActiveDocument.SaveAs Filename:="C:\Users\my comp\Desktop\new-test.doc"
         ' Close this new word document
        .ActiveDocument.Close
        .Quit '// Need to add more code to save, email or whatever you want to do...
    End With
     
    Set wrdApp = Nothing
    Set wrdDoc = Nothing
     
End Sub

I click debug on the error window, and it highlights the 3rd my replacements line, with "da-ac"

it may also help if I give some context as to what I am doing. I have about 40-50 pages of letters, documents, etc... in word that are now 1 file, and have generics (ie: [da-ac]) and an excel file where in A1, I have [da-ac] and the text I intend to replace it with in B1. Down to 13
I've been struggling with writing this piece of code for a little over a month and a half now, so anything to point me in the right direction is appreciated more than you can imagine.

Re: copying & replace from excel to word

Thankyou! I'll be sure to try it out and come back if I have more questions.

Thanks!

Re: copying & replace from excel to word

still looking for a solution! anything will be helpful!

For roughly a week now, I've been looking for a way to copy and past cells from excel to word. this is how my set up is supposed to work.

1. I have a generic version of a document in word, and in it are things like "subject 1"
2. in Excel I have a spread sheet that has something like cell a1 = "subject 1" then cell B1 = whatever the subject actually is

I found and unsuccessfully tried multiple macros that will automatically replace the generic words in the word document with the specific words needed in the excel document. Doing the normal, find -> replace and repeat has been time consuming since there are roughly 22 cells of data that need to be searched and replaced in a 20 page document.

I tried following This Link's instructions, but it seems that I am just having trouble understanding how to do this.