Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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.BuiltinDocumentP roperties("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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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? kaykayIT wrote: 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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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. "Dave Peterson" wrote: 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? kaykayIT wrote: 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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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("C reation Date"), "h:mm am/pm") .Cells(13, 4).Value = Format(ActiveWorkbook.BuiltinDocumentProperties("C reation 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" wrote: 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? kaykayIT wrote: 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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
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 kaykayIT wrote: 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("C reation Date"), "h:mm am/pm") .Cells(13, 4).Value = Format(ActiveWorkbook.BuiltinDocumentProperties("C reation 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" wrote: 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? kaykayIT wrote: 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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a New Workbook
Hello Dave,
I must have copied the wrong code. If you add ActiveWorkbook: With ActiveWorkbook.Sheets("WorkOrder") .Cells(30, 5).Value = Format(ActiveWorkbook.BuiltinDocumentProperties("C reation Date"), "h:mm am/pm") .Cells(13, 4).Value = Format(ActiveWorkbook.BuiltinDocumentProperties("C reation Date"), "mm/dd/yyyy") End With it put the time and date into the new workbook. Thanks again! "Dave Peterson" wrote: 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 kaykayIT wrote: 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("C reation Date"), "h:mm am/pm") .Cells(13, 4).Value = Format(ActiveWorkbook.BuiltinDocumentProperties("C reation 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" wrote: 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? kaykayIT wrote: 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" wrote: 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 kaykayIT wrote: 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.BuiltinDocumentP roperties("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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create workbook | Excel Discussion (Misc queries) | |||
Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
Taking specific rows from on workbook to create another workbook | Excel Worksheet Functions | |||
Create Workbook | Excel Programming | |||
Create new workbook | Excel Programming |