Application Defined error on creating new workbook

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
I have the following sub to create a new workbook, close it, copy a worksheet in the active workbook, open the new workbook, and then paste it in. It worked in Excel 2007, but I've switched to 2010 and now I get an Application Defined or Object defined error (1004). It stops at the third line (ActiveWorkbook.SaveAs......) with the error. It does create a new workbook, but it's a Bookn file name (as in Book1 etc.), but never saves it. UserName is the output of an InputBox (Dim UserName As String, ). This works great and puts the string in a cell. I've tried using the Worksheet Range where UserName is stored in the file name and still get the same error.



Sub CreateCopy()

Workbooks.Add
ChDir "C:\Users\Jim 2\Desktop\Sample Results"
ActiveWorkbook.SaveAs Filename:="C:\Users\Jim 2\Desktop\Sample_Results\"UserName & Format(Now(), "mm-dd-yyyy hh:mm").xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Jim 2\Desktop\Sample_Results\UserName.xlsx"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close

Application.DisplayAlerts = True
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That line of code doesn't compile. It should be:

Code:
    ActiveWorkbook.SaveAs Filename:="C:\Users\Jim 2\Desktop\Sample_Results\" & UserName & Format(Now(), "mm-dd-yyyy hh:mm") & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
OK. Fixed the".xlsx: goof. Still having the same error occur and a new blank workbook "Bookxx" is created but never saved. I get UserName from the following Public Sub.
Thought this made it available to all procedures in the module?

Public Sub UserName()

Dim UserName As String
Sheets("Calc").Select
ActiveSheet.Range("R1").Select

UserName = Application.InputBox("Enter your Name in the box below and Click OK. " _
, Default:=" ", Title:="User Name")
Sheets("Calc").Range("T1") = UserName
End Sub
 

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
UpDate - Boy does this log you off fast...

Everything works OK now until I get to the "Workbooks.Open ...." line and then I get an Application-defined error. I can see the created workbook with the correct name, including date/time, and I can see that the source sheet has stepped into the copy function, but it hits Workbooks.Open it and chokes. I did a ? curdir in the Immediate window and the correct directory is current so it must be looking there, but doesn't recognize the file for some reason. I set a Watch at the offending line and it reports <expression not="" defined="" in="" context=""> underValue "Expression not defined in context"</expression><expression not="" defined="" in="" context=""><expression not="" defined="" in="" context=""> , and Type = Empty.

The weird thing is the code is from a recorded Macro I did and all I added was the Format(Now... part.

Sub NewWB()
'' NewWB Macro
' Workbooks.Add
ChDir "C:\Users\Jim 2\Desktop\Sample Results"
ActiveWorkbook.SaveAs Filename:= _

"C:\Users\Jim 2\Desktop\Sample Results\CopiedResults" & Format(Now(), " mmm-dd-yyyy hh.mm") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Cells.Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\Jim 2\Desktop\Sample Results\CopiedResults"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub</expression></expression>
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

You haven't specified the entire file name,
 

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
Thanks Andrew. That's what I thought. Problem is the file name changes every time it is created and there may be hundreds of files in the directory to reopen for the paste. File name created is "CopiedResults 9/08/2014 12:26" for one I just ran. How do I pick the last file created n the directory to use in the statement to open and paste into?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

Can't you store the file name in a variable?

Code:
    Dim FileName As String
    ChDir "C:\Users\Jim 2\Desktop\Sample Results"
    FileName = "CopiedResults" & Format(Now(), " mmm-dd-yyyy hh.mm") & ".xlsx"
    ActiveWorkbook.SaveAs FileName:= _
        "C:\Users\Jim 2\Desktop\Sample Results\" & FileName, _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
    Cells.Select
    Selection.Copy
    Workbooks.Open FileName:="C:\Users\Jim 2\Desktop\Sample Results\CopiedResults\" & FileName
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close

But why are you closing then reopening it?
 

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
Andrew, Your fix works great. It now creates the file OK.

About your ? on closing the file: If I don't, it chokes at the Cell.Select line where I want to copy from the source file and then go back to the new file and paste it. If I leave in the close it closes the source file and stop, but the new file has the data OK. The sequence I need is:

Create new file
Copy source to it
Close new file
Stay in source file

I'm sure it's simple, but as a VBA newbie I am lost.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What's name of the Worksheet that you want to copy? Is it in the Workbook that contains your VBA code?
 

Jimmy P

New Member
Joined
Aug 23, 2014
Messages
45
Yes. Workbook name for source file is Test1.xlsx Workbook name to be created is C:\Users\Jim 2\Desktop\Sample Result + date/time .xlsx
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,875
Members
414,106
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top