Command isn't available because there's no open document

Question

I'm trying to import some data from tables in some word documents in excel using macros, but when it comes to open the word document and read it from an excel macro I can't do anything, because it says that I have no open document, but I do.

If I open a doc singularly calling it by its name it's alright, but the problem comes when I open files from a search and a loop.

Sub LoopFile()

    Dim MyFile, MyPath As String
    Dim wrdApp, wrdDoc
    MyPath = "here goes my path with personal info, it points to a folder"
    MyFile = Dir(MyPath)
    Set wrdApp = CreateObject("Word.Application")
    Do While MyFile <> ""

'parameters for the files to search

        If MyFile Like "*.docx" And MyFile Like "All*" Then
            wrdApp.Visible = True
            Set wrdDoc = wrdApp.Documents.Open(MyPath & MyFile)
            Call GetID
            wrdApp.Close
        End If
        MyFile = Dir
    Loop
End Sub


Sub GetId()

    Dim cicli, y As Integer
    'counter for iterations
    cicli = cicli + 1
    'if it's first iteration it starts from column E, otherwise the next one
    If (cicli = 1) Then
        y = 5
    Else
        y = y + 1
    End If

    ActiveDocument.Tables(1).Cell(Row:=1, Column:=2).Range.Copy
    ThisWorkbook.Worksheets("Foglio1").Cells(23, y).PasteSpecial xlPasteValues


End Sub

The problem comes when it arrives to

ActiveDocument.Tables(1).Cell(Row:=1, Column:=2).Range.Copy

How can I fix it? Thank you

I would start by avoiding to use ActiveDocument. Instead modify the sub GetId to have the document as an argument such as GetId(wrdDoc as Document) and then continue using wrdDoc inside the sub. Might not solve your issue as I am not certain it is caused by this, but it is better practice to use as this. — Lorne, Sep 13 '19 at 08:11
In general, avoid writing this `MyPath = "here goes my path with personal info, it points to a folder"`, simply write a fake path `C:\some\fake\path`, because the problem could be in it as well, if it does not finish with a `\` or anything similar. — Vityata, Sep 13 '19 at 08:34

score 1 · Accepted Answer · answered Sep 13 '19 at 08:11

1

Pass the document you are referring to and avoid the ActiveDocument. E.g., try to fix it in a way like this:

Set wrdDoc = wrdApp.Documents.Open(MyPath & MyFile)
GetID wrdDoc

And then change a bit the GetId Sub, accepting the wrdDoc parameter.

Sub GetId(wrdDoc as Object)

    Dim cicli, y As Integer
    'counter for iterations
    cicli = cicli + 1
    If (cicli = 1) Then
        y = 5
    Else
        y = y + 1
    End If

    wrdDoc.Tables(1).Cell(Row:=1, Column:=2).Range.Copy
    ThisWorkbook.Worksheets("Foglio1").Cells(23, y).PasteSpecial xlPasteValues

End Sub

How to avoid using Select in Excel VBA

answered Sep 13 '19 at 08:11

Vityata

42,633
8
55
100

This worked, also little note, I had to declare "cicli" in the first sub to make it work as I wanted because everytime it went back in the 2nd sub it would reset it's value causing an infinite loop. Obviously then I passed cicli as a parameter to the second sub. – Otori Sep 13 '19 at 11:26
@Otori - cool that you have managed ot work it out. In general, consider naming the variables in English, because `cicli` would probably not mean a lot to many people. – Vityata Sep 13 '19 at 16:33

Command isn't available because there's no open document

1 Answers1