CODEWITHSUNDEEP
macosvbaexcel
S. Cannon
S. Cannon

Reputation: 59

How to create a new excel file using VBA for Mac

I'm creating a macro in Excel 2011 for Mac to create reports. Part of the macro wants to open a file (99% of the time), edit the file, and then close the file (saving changes). On a rare occasion, if a file does not exist, it wants to create a new excel file, edit it, and then close the file (saving changes).

I have successfully created the ability to open an existing file using existing questions and answers online. However, in the event that it fails to find a file, I can't determine a way to create one. If I try to simply open a file that doesn't exist, then it errors out.

Here is the code:

Dim nExcel As Object
Dim nWB As Object
If FileOrFolderExistsOnMac(1, strFile) Then
        Workbooks.Open (strFile)
        ...
        ActiveWorkbook.Close SaveChanges:=True
    Else
        'Note: I understand that this check should be redundant
        Set nExcel = GetObject(, "excel.application") 'gives error 429 if Excel is not open
        If Err = 429 Then
            Set nExcel = CreateObject("excel.application") 'create
            Err.Clear
        End If
        'Set nWB = nExcel.documents.Add
        ...
        ActiveWorkbook.Close SaveChanges:=True
    End If

Ok, the If block of the statement works. In the Else block, the only related post I could find to creating a file that doesn't exist is this: Create Word file from Excel 2011

Obviously Word is not the same as Excel, and hoping that they had similar syntax is a farfetched dream. However, I went ahead and tried it, and it ran until it got to the line that is commented out where I Set nWB = nExcel.documents.Add with runtime error 438: object does not support this property or method.

Upvotes: 0

Views: 1510

Answers (1)

joehanna
joehanna

Reputation: 1489

You don't need the Set nExcel line. This is for controlling excel from another application (on Windows). All you need is to create a new `Workbook’ and since you are already inside Excel, the context is already there so all you need to do is replace it with this and remove the error check :

Workbooks.Add

Upvotes: 3

Related Questions