Executing a SaveAs Command in an Open .txt File

Hello!

I'm writing a Macro that writes the contents of the cells in the first column of a worksheet into an already existing .txt file on my desktop. All of that is working fine and dandy until I want to save the .txt file under a new name (See bolded code below). I'm getting an Object Required Error, probably because my syntax is incorrect. Anyone have any helpful corrections?

Thank you!!

    Sheets("Blank").Select
    Range("A1").Select
    Selection.pastespecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Dim r As Integer
    Open "Macintosh HD:Users:Name:Desktop:trial.txt" For Output As #1
        Sheets("Blank").Activate
           ii = Sheets("Blank").Cells(Rows.Count, 1).End(xlUp).Row
            For r = 1 To ii
                data = Cells(r, 1)
                    Print #1, data
            Next r
        ActiveDocument.SaveAs Filename:="NewDocument.txt", _
        FileFormat:=wdFormatText

    Close

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you since it's your first post. --6SJ

Object Required is not a syntax error, it's a runtime error. It most likely means that ActiveDocument is Nothing. Based only on the code you are showing, I see you are opening a file as device #1. As you clearly understand from your code, you can Open, Close, and Print with this file. However, ActiveDocument is a Word object. It has nothing at all to do with a file accessed with the Open statement. They are two completely different animals.

If you describe in a little more detail the steps you want to take with this file, I might be able to recommend a way to accomplish it, although I do not experience with Office or VBA on a Mac. Do you want to make an exact copy of the file after you write to it? Or do you want to save the changes as a new file, leaving the original the way it was before you wrote to it? There is not a way to directly save an I/O file with a new name but there are other commands you can use to rename it (which I suppose would also work on a Mac).

Is this macro in a Word file, or Excel? This is posted to an Excel forum.