CODEWITHSUNDEEP
vbaexcelms-word
user7041266
user7041266

Reputation: 3

EXCEL VBA - LOOP error

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.

Upvotes: 0

Views: 97

Answers (1)

CLR
CLR

Reputation: 12279

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

Upvotes: 1

Related Questions