EXCEL VBA - LOOP error

Question

I am trying to run a particular function, I tested this yesterday and it works. I wanted this looped but when excel tries the same function for the next row, I get a file path error.

Any ideas? Any help would be much appreciated.

Option Explicit

Sub odoc()

Dim fpath As String
Dim objWord As Object
Dim cel As Range
Dim selectedRange As Range

Set objWord = CreateObject("Word.Application")
fpath = Application.ActiveCell.Value
Set selectedRange = Application.Selection

For Each cel In selectedRange.Cells
objWord.Documents.Open (fpath)
objWord.Visible = True
objWord.Application.Run MacroName:="CopySAM"
ActiveCell.Offset(0, 14).Select
ActiveSheet.Paste
objWord.Application.Quit
ActiveCell.Offset(1, -14).Select
Next cel

End Sub

Thanks.

on which line do you get an error and what is the exact error message? Also, try to avoid using ActiveCell/Activesheet references where possible and use names e.g. loop a range held in a variable or work with a sheet by name. — QHarr
– QHarr, Commented Jan 5, 2018 at 10:29
I'm a little confused by what you're trying to do here. Every cell in the range contains a filepath? If so, you'd want objWord.Documents.Open(cel.value) in your loop. Right now, it looks like it's opening the same word document over and over. — John Ink
– John Ink, Commented Jan 5, 2018 at 10:46
@QHarr - I get Runtime error '-2147417848 (80010108) Automation Error - The Object invoked has disconnected from its clients. — user7041266
– user7041266, Commented Jan 5, 2018 at 10:48
That error is because you quit the application at the end of your loop with objWord.Application.Quit. The next time the loop comes around, Word is no longer open. — John Ink
– John Ink, Commented Jan 5, 2018 at 11:17

CLR · Accepted Answer · 2018-01-05 13:26:44Z

1

This tidies up your loop - it should now work. Though I have to say there are better ways to paste the data than the way you're using..

Sub odoc()

    Dim objWord As Object
    Dim cel As Range
    Dim selectedRange As Range

    Set selectedRange = Application.Selection

    For Each cel In selectedRange.Cells
        Set objWord = CreateObject("Word.Application")
        objWord.Documents.Open (cel)
        objWord.Visible = True
        objWord.Application.Run MacroName:="CopySAM"
        cel.Offset(0, 14).Select
        ActiveSheet.Paste
        objWord.Application.Quit
    Next cel

End Sub

answered Jan 5, 2018 at 13:26

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user7041266 Over a year ago

OMG thank you so much - this works now. I cant believe it I was thinking all this time about this. You mentioned a better approach to this, I not very good at VBA so my approach my very noob. how would you suggest I should have approached this.

CLR Over a year ago

You could replace the two lines: cel.Offset(0, 14).Select and ActiveSheet.Paste with one line: cel.Offset(0, 14).PasteSpecial xlPasteAll for instance.

CLR Over a year ago

Also, instead of creating your Word.Application and killing it repeatedly, you could create it outside the loop and kill it after the loop finishes by within the loop closing the document, rather than quitting Word itself. I think it's something like objWord.ActiveDocument.Close but I'm not sure.

user7041266 Over a year ago

Thank you for this, It works so much faster now also.

CLR Over a year ago

No problem. If you're happy with the answer, don't forget to mark it answered.

Collectives™ on Stack Overflow

EXCEL VBA - LOOP error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related