Re: creating an error log

[Kevin Waddle <thewaddles@SBCGLOBAL.NET>]

Steve & Sandy,
This is more than you asked for but...
It will create a file named ErrorLog.txt
Each line of the file will contain...
Your Error message
the computer the user was on when the error was found,
the user's login,
When the error was found,
the path and name of the workbook that contained the error

HTH,
Kevin

'*** Code Start ***
Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nsize As Long) As Long

Declare Function GetComputerName& _
Lib "kernel32" Alias "GetComputerNameA" (ByVal lbbuffer As String, nsize
As Long)

Function ReturnCPUname()
'Find the computer name
Dim CPUname As String
Dim i As Integer
Dim z As String * 64
Call GetComputerName(z, 64)
ReturnCPUname = z

For i = 1 To Len(ReturnCPUname)
If Asc(Mid(ReturnCPUname, i, 1)) = 0 Then
ReturnCPUname = Left(ReturnCPUname, i)
Exit For
End If
Next i


End Function

Sub ErrorLog()
Dim CurUser As String
Dim buffer As String * 100
Dim BuffLen As Long
Dim MyPath As String
Dim FileNum As Integer
Dim i As Integer

'Find the computername
MsgBox Len(ReturnCPUname)

'Find your desktop and put the error log there
'Requires a refernce to the Windows Script Host Object Model
Dim WSHShell As IWshRuntimeLibrary.IWshShell_Class
Set WSHShell = New IWshRuntimeLibrary.IWshShell_Class
MyPath = WSHShell.SpecialFolders.Item("Desktop")

'You can change the MyPath = to match where you want the log

'Find the User Name
BuffLen = 100
GetUserName buffer, BuffLen
CurUser = Left(buffer, BuffLen - 1)

FileNum = FreeFile

'Start your loop here...
'For each...In...your range

'if your error then
Open MyPath & "\ErrorLog.txt" For Append As FileNum
'This will write in your ErrorLog.txt
'Your Error, the computer the user was on, the user's login, when, the
path and name of the workbook
Write #FileNum, "Error", ReturnCPUname, CurUser, Now, ThisWorkbook.Path
& "\" & ThisWorkbook.Name
Close FileNum
'continue your loop
End Sub

'*** Code End ***

-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-G@PEACH.EASE.LSOFT.COM]
On Behalf Of Steve Snow
Sent: Thursday, September 18, 2003 4:58 PM
To: EXCEL-G@PEACH.EASE.LSOFT.COM
Subject: [EXCEL-G] creating an error log


I would appreciate a bit of basic start help to do the following.

I want to be able to check a worksheet (size is about 43 columns by 159
rows).

Each cell has either numeric or text values (no mixing) and should have
limits.

I can do all the criteria things but how would I create a txt file (thus
how do I do the read/write part of the text file)?

Thus - as the error routine checks each cell - how do I get it to write
to a text file (one line) - e.g "Sept 1 0600 dry bulb (then the error
type), (new
line) Sept 4 1500 max temp (then the error) etc?

Thanks

Steve and Sandy Snow

------------------------------------------------------------------------
--
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info and
archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G