0

I am trying to run an Excel VBA macro from a batch file. I don't know much about either so I'm using one of the methods described in this this question. (Specifically I'm using the one suggested by @omegastripes.)

Everything works if I use the dummy macro used to illustrate the concept. However, when I use my actual macro, I get an error: "Method 'Worksheet' of object'_Global' failed." If I simplify by calling the macro every time the Workbook is opened, I still get the error. In that scenario, the macro works if I open the Workbook manually but I receive the same error if I open it with a (one line) batch file.

The macro I'm trying to run is just one that outputs a certain part of my Workbook to a text file. It's just a slight adaptation of this article.

Sub GetClassList()

Dim ws As Worksheet, Rng As Range, TextFile As String, i As Integer, cellValue As Variant

Set ws = Worksheets(1)

TextFile = ThisWorkbook.Path & "\Document Builders\Class List.txt"
Open TextFile For Output As #1

For i = 1 To ws.Range("O19").Value
    cellValue = ws.Range("R3:R10").Cells(i, 1).Value
    Print #1, cellValue
Next i

Close #1

End Sub
David Paige
  • 1
  • 1
  • Just a guess but try `Set ws = Thisworkbook.Worksheets(1)` – CDP1802 Dec 07 '21 at 19:32
  • That did it! Thanks so much. I guess the mechanism used to open the Workbook affects what is considered "global" and without the "Thisworkbook," the function Worksheets(1) was attached to the "global" object? (As I said, I have little experience with VBA.) – David Paige Dec 07 '21 at 19:38

0 Answers0