daily report

[mikeKBRdallas]

can i create a report log for each day and make a macro
button to allow users to purge into a wrkbook. Can the
wrkbook be set up to take in each day log as a worksheet?
How many days can i do? basically i want a journal of
activity recorded on site and to have in here on
spreadshhet. OR is it better to link it to another
program such as word or access? any ideas as to which is
effective for use.
THANKS!

 

[Dave Peterson]

The number of worksheets in a workbook is limited by your pc's memory.

Have you thought of adding a time/date to your log and bringing the log into
just one worksheet (limit of 65536 rows, here).

But you may find it easier to summarize your data
(charts/subtotals/pivottables/etc) if the data is all in one worksheet.

And you could record a macro when you open the log file and then just rerun that
whenever you needed to refresh your excel file.

 

[Guest]

thank you , how do i go about recording a macro for this
execution? do you have an sugggestion as the best way to
go ? I would like it to record the date and time and
then move the log into a file under one heading or book
and then refresh the template for a new data and notes.
say... liek i write one report for today and then when i
am done to hit the button and it goes to the file for
this "x" project daily reports" and then gives me an
empty page for tuesday. and etc..

thanks for helping! mike

 

[Dave Peterson]

I may have misunderstood.

I thought you had the log file (from a different application) and you wanted to
use excel to analyze it.

If that were true, then you could record the macro when you opened that log file
in excel.

You could write to a text file:

Option Explicit
Sub UpdateLog()
Dim oFilenum As Long
oFilenum = FreeFile()
Close #oFilenum
Open "C:\output.txt" For Append As #oFilenum
With Worksheets("sheet1")
Print #oFilenum, Time & vbTab & Date & vbTab _
& .Range("a1").Text & vbTab; .Range("b2").Text

'clear the cells
.Range("a1,b2").ClearContents
End With
Close #oFilenum
End Sub

Or you could just use an extra worksheet in the workbook:

Option Explicit
Sub UpdateLog2()

Dim LogWks As Worksheet
Dim DestCell As Range

Set LogWks = Worksheet("Log")

With LogWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

With Worksheets("sheet1")
DestCell.Value = Date
DestCell.Offset(0, 1).Value = Time
DestCell.Offset(0, 2).Value = .Range("a1").Value
DestCell.Offset(0, 3).Value = .Range("b2").Value

'clear the cells
.Range("a1,b2").ClearContents
End With

End Sub

You'd have to add all the cells that you'd want to log in either case.