Word Or Notepad From Excel
 

Group,
I'm writing my first big VBA project. This group has helped me
along quite a bit the past few weeks. I'm comparing two Excel
spreadsheets and checking, comparing verifying fields etc. What should
be my error report listing strategy be? I'm guessing the best way is
Word, since Word uses VBA also. Should I open a word document paste an
error then close it until the next error is found? Or should I leave
the Word doc open and paste to it as errors are found? Or should I use
Notepad and open/close or open and then paste... well you get the idea.
Your thoughts.

Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=390078

Word Or Notepad From Excel
 

I think it depends on whether you need the data to be nicely formatted
in Word or not. If you're looking to have more of a "log" concept I
would definitely go with creating a text file - so much quicker than
opening Word and cleaner.

Without knowing exactly what you're doing, my personal preference would
be to write everything to log.

I've written an example below. At the end of the code it asks whether or
not you need to view the log. Note that this appends subsequent runs
onto the same file. You may wish to simply overwrite any existing log or
perhaps create a new file for every log.

HTH,
Gareth

'------------------------------
Const myLog as string = "c:\temp\mylog.txt"

Sub MyMainRoutine()

fcnWriteToLog "BEGINNING PROCESS"

'Open up first workbook
fcnWriteToLog " " & wb1.fullname & " opened..."

'Open up second workbook
fcnWriteToLog " " & wb2.fullname & " opened..."

'Check for errors etc.
'begin loop
if errorfound then fcnWriteToLog " An error occurred"
'end loop

fcnWriteToLog "PROCESS ENDED"

If Msgbox ("X number of errors found. " _
& "Do you wish to view the log?" _
,vbyesno) = vbyes then
shell "notepad.exe " & myLog
end if

End sub

Function fcnWriteToLog(myText as string) as boolean
Dim F as integer

on error goto ErrorHandler
F = Freefile
open mylog for append as #F
print #F, Format(now,"dd-mmm-yyyy hh.nn") & " " & myText
close #F
fcnWriteToLogFile = true
ErrorHandler:

End function
'--------------------------------


ajocius wrote:
Group,
I'm writing my first big VBA project. This group has helped me
along quite a bit the past few weeks. I'm comparing two Excel
spreadsheets and checking, comparing verifying fields etc. What should
be my error report listing strategy be? I'm guessing the best way is
Word, since Word uses VBA also. Should I open a word document paste an
error then close it until the next error is found? Or should I leave
the Word doc open and paste to it as errors are found? Or should I use
Notepad and open/close or open and then paste... well you get the idea.
Your thoughts.

Tony

Word Or Notepad From Excel
 

Gareth,
I think your comments make me move to using Notepad. Tomorrow
when I get home from work, I'll give it a try. Basically, this
comparison macro would be run once. Again thank you for the advice.


Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=390078

Word Or Notepad From Excel
 

You're welcome.

Of course the other obvious way to do this, if you want a nice report
you can print or email at the end of the comparison, is simply to write
into our old friend Excel! Just create a new workbook as part of your
code and dump it all in there.

Good luck.
Gareth

ajocius wrote:
Gareth,
I think your comments make me move to using Notepad. Tomorrow
when I get home from work, I'll give it a try. Basically, this
comparison macro would be run once. Again thank you for the advice.


Tony