Darrell:
Why do you need to create a new workbook if you only have
3 columns of information to display? It seems to me that
you would want those three columns (Job#, Detail# and Due
Date) and then all your company's information below that
in one workbook. For example:
Job# Detail# Due Date
001 something November
19, 2003
002 something November
30, 2003
003 something February
12, 2004
and so on.
I can maybe see having multiple worksheets in a workbook,
but not multiple workbooks for the same information. If
you want to automate something like this, I would create a
front-end application in Excel or VB. In Excel, using
VBA, you can make a user form (or input boxes, which is
extremely easy) that asks the user questions. The users
answers will be sent into the cells that you want the data
to go. From that point, create a macro that runs on a
timer in Excel to delete the contents of specific cells at
a certain time.
Input Box Example:
Sub Example()
Do
NextRow = Application.WorksheetFunction.CountA
(Range("A:A")) + 1
Job = InputBox("Enter the job number")
Detail = InputBox("Enter the detail number")
DueDate = InputBox("Enter the due date")
If Job = "" Then Exit Sub
If Detail = "" Then Exit Sub
If DueDate = "" Then Exit Sub
Cells(NextRow, 1) = Job
Cells(NextRow, 2) = Detail
Cells(NextRow, 3) = DueDate
Loop
End Sub
This example will ask the user to "Enter the job
number", "Enter the detail number" and finally "Enter the
due date". Once these questions are answered they are
then sent to the next available row in column A.
User Form Example:
Private Sub cmdGO_Click()
Do
NextRow = Application.WorksheetFunction.CountA
(Range("A:A")) + 1
Job = txtJob.Text
Detail = txtDetail.Text
DueDate = txtDate.Text
Cells(NextRow, 1) = Job
Cells(NextRow, 2) = Detail
Cells(NextRow, 3) = DueDate
Loop
End Sub
Create a form that has three text boxes on it called
txtJob, txtDetail and txtDate. Next, create a command
button that sends the users answers to Excel called
cmdGO. As soon as the user clicks the button the
information is sent to Excel.
Finally, to delete information at a certain time:
Public Sub StartTimer()
Dim iTimeItHH As Integer
Dim sTimeItHH As String
Dim sTimeIt As String
cRunWhat = "Update" 'Starts
running.
iTimeItHH = Hour(Time) 'Gets the
hour (in military time).
sTimeItHH = CStr(iTimeItHH) 'Converts
integer to string.
sTimeIt = sTimeItHH & ":00:00" 'Puts hour
in string.
Application.OnTime TimeValue(sTimeIt), cRunWhat 'Runs
every hour
End Sub
Public Sub Update()
Range("A1:A3").Select 'Selects the range A1
through A3.
Selection.ClearContents 'Deletes the contents
in that selection.
StartTimer 'Starts the timer
again.
End Sub
Public Sub StopTimer()
cRunWhat = "Update" 'Stops the timer.
On Error Resume Next
Application.OnTime EarliestTime:=Now, Procedure:=cRunWhat,
Schedule:=False
End Sub
This example will delete the contents in cells A1, A2 and
A3 at the top of each hour. I would put this code in the
workbook_open function so the timer starts on the workbook
open and then continues to run at the top of each hour
until the workbook is closed.
These are all just example, but might get you started!
Ash
