Mismatch Error when adding a new workbook

Question

UPDATE #2: I have split the line from Set ExcelBook = Workbooks.Add to 2 separate lines (Workbooks.Add and Set ExcelBook = ActiveWorkbook) and am still randomly receiving the error. It does not happen every time, but when it does it directs me to the line Workbooks.Add (still receiving the same type mismatch error).

Sub NewWorkbook(CompanyName As String, OutputDirectory As String, Scenario As String)

Dim ExcelBook As Workbook
Dim CopyArea As Range

Set CopyArea = Range("CopyArea")
Workbooks.Add
Set ExcelBook = ActiveWorkbook

CopyArea.Copy

ExcelBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
ExcelBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
ExcelBook.Sheets("Sheet1").Columns(2).EntireColumn.Delete
ExcelBook.Sheets("Sheet1").Rows(6).EntireRow.Delete
ExcelBook.Sheets("Sheet1").Cells.EntireColumn.AutoFit

Application.DisplayAlerts = False
ExcelBook.Close SaveChanges:=True, Filename:=OutputDirectory + "\" + Replace(Replace(Replace(CompanyName, "\", ""), "/", ""), ":", "") + " - " + Scenario + ".xlsx"
Application.DisplayAlerts = True

Set ExcelBook = Nothing
Set CopyArea = Nothing            

End Sub

UPDATE: I have re-worked the code to remove select/activate, but I am still getting the random mismatch error. When I do receive the error, it does seem to be creating the new workbook, but stops after that (after receiving the error and selecting 'End', there is a blank workbook that was not there before).

ORIGINAL DESCRIPTION: I am receiving a mismatch error in the above code on line 'Set ExcelBook = Workbooks.add'. What is strange is that the error does not occur every time (I can't seem to find any pattern on why it happens sometimes and not others).

I have another macro that calls the below code in a 'For' loop. Sometimes it will finish successfully, other times it will error out on the 1st iteration, other times on the 5th iteration, etc.

Any thoughts?

*another macro that calls the below code in a 'For' loop* - is the other macro also written in Excel? Or do you call it from another application? Also read [How to Avoid Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Scott Holtzman, Feb 05 '16 at 18:58
Yes, all of the macros are in the same workbook (AAAAA.xlsm). I will take a look at the linked post now, thanks. — Steve Just, Feb 05 '16 at 19:24
I get "Error: Type mismatch" and it directs me to the line 'Set ExcelBook = Workbooks.Add'. After closing the error box, there is a new Excel workbook so it does seem to be creating it. — Steve Just, Feb 05 '16 at 20:58
Try separating it into two lines, see if anything changes: `Workbooks.Add` then `Set ExcelBook = ActiveWorkbook` — vacip, Feb 05 '16 at 21:20
This seems to have helped. I'll keep testing and see if it pops up again. Out of curiousity - any specific reason why splitting it into 2 lines would help? — Steve Just, Feb 05 '16 at 22:59
Well, no idea. :) It is just easier this way to spot which command causes the error. If that solved the problem, hooray, but I really have no idea as to why. — vacip, Feb 05 '16 at 23:26
Unfortunately, I am still randomly receiving the error. It is now happening on the 'Workbooks.Add' line. — Steve Just, Feb 06 '16 at 01:39

score 0 · Answer 1 · answered Feb 05 '16 at 20:44

0

The error could be due to lack of specification on the range assignment, change:

Set CopyArea = Range("CopyArea")

to

Set CopyArea = ThisWorkbook.Sheets(1).Range("CopyArea")

or change the number to your sheet's name.

answered Feb 05 '16 at 20:44

Xavier Navarro

36
5

The error occurs after that line of code. The exact line is 'Set ExcelBook = Workbooks.Add'. Also does not happen every time. Seems to be random. – Steve Just Feb 05 '16 at 20:59

score 0 · Answer 2 · answered Feb 08 '16 at 15:11

0

I changed Workbooks.Add to Application.Workbooks.Add and it seems to have helped.

I still randomly get the mismatch error, but it is a bit less frequent. I'll continue to try and further refine.

answered Feb 08 '16 at 15:11

Steve Just

33
7

score 0 · Answer 3 · answered Feb 08 '16 at 15:58

Try this code. The workbook is created and assigned to the variable in the same command.

Sub Test()

    NewWorkbook "aZ\A", "\\OutputdirectoryServer\Test", "ScenarioA"

End Sub

Sub NewWorkbook(CompanyName As String, OutputDirectory As String, Scenario As String)

    Dim ExcelBook As Workbook
    Dim CopyArea As Range

    'Create a new workbook with 1 sheet and assign it to the variable.
    Set ExcelBook = Workbooks.Add(xlWBATWorksheet)

    With ExcelBook.Worksheets(1)

        'Update this line to look at the correct sheet.
        ThisWorkbook.Worksheets("Sheet2").Range("CopyArea").Copy

        With .Range("A1")
            .PasteSpecial xlPasteValues
            .PasteSpecial xlPasteFormats
        End With

        .Columns(2).EntireColumn.Delete
        .Rows(6).EntireRow.Delete
        .Cells.EntireColumn.AutoFit
    End With

    'Save the file using the correct fileformat.
    'For an existing file, the default format is the last file format specified;
    'for a new file, the default is the format of the version of Excel being used.
    Application.DisplayAlerts = False
    ExcelBook.SaveAs OutputDirectory & "\" & _
        Replace(Replace(Replace(CompanyName, "\", ""), "/", ""), ":", "") & " - " & Scenario & ".xlsx", _
        FileFormat:=51
    ExcelBook.Close False
    Application.DisplayAlerts = True

    Set ExcelBook = Nothing
    Set CopyArea = Nothing

End Sub

No luck. Error occurs on line `Set ExcelBook = Workbooks.Add(xlWBATWorksheet)`. Been happening fairly consistently around iteration #2-4 today. — Steve Just, Feb 09 '16 at 22:11
Also, I printed the error results out to a log file and it seems the same line is triggering an "Object variable or With block variable not set" error as well.... — Steve Just, Feb 09 '16 at 22:49

Mismatch Error when adding a new workbook

3 Answers3