Create a New Workbook

[Guest]

I created a worksheet template but the problem is that a new workbook is not
created when the new worksheet is created. The purpose of a new workbook is
to get the creation date of the workbook for subtracting time out from time
in.(See Code Below)

Cells(30, 3).Value =
Format(Application.ActiveWorkbook.BuiltinDocumentProperties("Creation Date"),
"h:mm am/pm")

How do I use VBA to create a new workbook and can I put an icon on the
desktop to run this code?

 

[Dave Peterson]

Are you looking to return the current time?

You can use VBA's builtin Time or Date:

Option Explicit
Sub testme01()

Dim justTime As Date
justTime = Time
MsgBox justTime

'or
Dim DateAndTime As Date
DateAndTime = Now
MsgBox DateAndTime

'or
Dim justDate As Date
justDate = Date
MsgBox justDate

End Sub

 

[Guest]

Thank you Dave for your help. If I return to current time in the cell
specified, will that time change when the sheet is recalculated? I need a
time that will never change but will always be the time that the worksheet
was created. I do not want the user to have to put in the current time as
his TIME IN or any changes to the TIME IN time. According to the help in
Excel VBA inserting the current time can change if the sheet is updated.

 

[Dave Peterson]

It depends on what you put in that cell.

If you put a formula:
=now()-int(now())
or
=mod(now(),1)
this will change each time the worksheet is recalculated.

But if you put a value in that cell, it won't change (well, unless you change
it).

Are you sure you saw that in help?

 

[Guest]

I'm sorry Dave, I was referring to using Time everytime the worksheet open.
Getting back to my original question is there any way to use VBA to create a
new workbook? If so, where does the code go? Is it a module, in This
Workbook, saved as an xla file?
Thanks so much for your help.

 

[Guest]

Thanks Dave for all your help. I think I found the solution to my problem.
I used the following code and it does just what I want.

Private Sub Workbook_Open()

With Sheets("WorkOrder").Copy
With Sheets("WorkOrder")
.Cells(30, 5).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), "h:mm
am/pm")
.Cells(13, 4).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"),
"mm/dd/yyyy")
End With

ActiveWorkbook.SaveAs Filename:="I:\Service Orders\Technician Work
Order " & Format(Date, "mm-dd-yyyy")
ThisWorkbook.Close SaveChanges:=False


End Sub

Thanks again!

 

[Dave Peterson]

Are you sure it did what you wanted?

When I ran your code, it put the time/date into the workbook that I opened--not
that one I created.

I put this in a general module (auto_open instead of workbook_open) and it
worked ok:

Option Explicit
Sub auto_open()

Dim newWks As Worksheet

Worksheets("WorkOrder").Copy
Set newWks = ActiveSheet

With newWks
With .Cells(30, 5)
.Value = Time
.NumberFormat = "h:mm am/pm"
End With
With .Cells(13, 4)
.Value = Date
.NumberFormat = "mm-dd-yyyy"
End With
End With

Application.DisplayAlerts = False
newWks.Parent.SaveAs _
Filename:="I:\Service Orders\Technician Work Order " _
& Format(Date, "mm-dd-yyyy")
Application.DisplayAlerts = True
ThisWorkbook.Close savechanges:=False


End Sub

Thanks Dave for all your help. I think I found the solution to my problem.
I used the following code and it does just what I want.

Private Sub Workbook_Open()

With Sheets("WorkOrder").Copy
With Sheets("WorkOrder")
.Cells(30, 5).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), "h:mm
am/pm")
.Cells(13, 4).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"),
"mm/dd/yyyy")
End With

ActiveWorkbook.SaveAs Filename:="I:\Service Orders\Technician Work
Order " & Format(Date, "mm-dd-yyyy")
ThisWorkbook.Close SaveChanges:=False


End Sub

Thanks again!

 

[Guest]

Hello Dave,
I must have copied the wrong code. If you add ActiveWorkbook:

With ActiveWorkbook.Sheets("WorkOrder")
.Cells(30, 5).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"), "h:mm
am/pm")
.Cells(13, 4).Value =
Format(ActiveWorkbook.BuiltinDocumentProperties("Creation Date"),
"mm/dd/yyyy")
End With

it put the time and date into the new workbook.

Thanks again!