Thomason,
In my original post I offered the following code sample for openeing a past performance file using VB or VBA in Access:
Using VB to read a past performance file is not all that hard to do. Here's a code snippet that I've used with VB6 with great success for years. Something very similar should work as VBA code inside of Access 97 or Access 2000.
Create a standard EXE VB project and add a command button to your form.
In the declarations portion of your VB Project, paste in the following code:
Dim FieldVal (1435)
Public FILE as String
And then place this bit of code in the click event procedure behind the command button:
FILE = InputBox("Enter File Name:")
If Not FILE = "" Then
call ReadAFile(FILE)
end if
And then create a subroutine named ReadAFile. Here's the code for that; you can just copy and paste it in:
Private Sub ReadAFile(FILE)
Err.Clear
On Error Goto handler:
Open FILE for input as #1
Do While Not EOF(1)
'Read A Single Horse:
For j = 1 To 1435
Input #1, fileField
FieldVal(j) = fileField
Next j
'Done With Single Horse, now do something interesting.
Loop
handler:
If Not Err.Number = 0 then
MsgBox Err.Number & " " & Err.Description, vbCritical, "Error"
Err.Clear
Yesterday, you asked me how I would go about sending output
from the opened file to an html report.
HTML offers a great deal of power and flexibility for formatting
output. I greatly prefer it over using the Access Report Engine for
many many reasons. One of those reasons is that HTML is
portable. It's very easy to post a link to an html report on a
webpage. An html report sitting on a client machine folder will
open and display in a user's web browser whenever the user
gives the file a double click, etc. On top of that, once you
understand html and/or the DOM, you gain complete control over
the appearance of every element found in the document, etc.
I'm getting sidetracked here. My goal here isn't to tout HTML over
the Access Report Engine. Both are what they are.
Using VB or VBA, you can write every part of an html document to
an HTML file. This includes all tags, including meta tags,
style tags, script tags, head tag, body tag, as well as the more
common html tags for tables, paragraphs, break tags, etc.
One way to embed an HTML report into the above code snippet
would be to do something like this:
1. Create a Sub like the one below:
Private Sub WriteHTMLReport(FieldVal)
Open "HTMLReport.html" for Output as #2
'``````````````````````````````````````````
'Write title, style, script, html, and body tags
'``````````````````````````````````````````
strHorseHTML = "<html><head><title>JCapper HTML Report1</title>"
strHorseHTML = strHorseHTML & "<STYLE>" & vbCrLf & "<!--" & vbCrLf
strHorseHTML = strHorseHTML & vbCrLf
strHorseHTML = strHorseHTML & ".Content" & vbCrLf & "{" & vbCrLf & "FONT-WEIGHT: normal;" & vbCrLf & "FONT-SIZE: 11px;" & vbCrLf & "Font-FAMILY: Sans-Serif , Verdana , Arial" & vbCrLf & "}" & vbCrLf
strHorseHTML = strHorseHTML & ".ContentGray" & vbCrLf & "{" & vbCrLf & "FONT-WEIGHT: normal;" & vbCrLf & "FONT-SIZE: 11px;" & vbCrLf & "Color: gray;" & "Font-FAMILY: Verdana , Arial" & vbCrLf & "}" & vbCrLf
strHorseHTML = strHorseHTML & " -->" & vbCrLf & "</style>" & vbCrLf
'``````````````````````````````````````````
'script tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "<script language=" & """" & "javascript" & """" & ">" & vbCrLf
strHorseHTML = strHorseHTML & "<!--" & vbCrLf
'``````````````````````````````````````````
'JavaScript functions
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & vbCrLf & vbCrLf
'displayInfoPassedIn
strHorseHTML = strHorseHTML & "function displayInfoPassedIn(sInfo)" & vbCrLf
strHorseHTML = strHorseHTML & " // display the info passed in" & vbCrLf
strHorseHTML = strHorseHTML & " {" & vbCrLf
strHorseHTML = strHorseHTML & " alert(sInfo)" & vbCrLf
strHorseHTML = strHorseHTML & " }" & vbCrLf
strHorseHTML = strHorseHTML & vbCrLf & vbCrLf
'``````````````````````````````````````````
'close script tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "-->" & vbCrLf
strHorseHTML = strHorseHTML & "</script>" & vbCrLf
'``````````````````````````````````````````
'close out the head tag/write the body tag
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "</head><body>"
Print #2, strHorseHTML
'``````````````````````````````````````````
'Print Button
'``````````````````````````````````````````
strClickToPrint = " onClick=" & """" & "javacript:self.print(); " & """"
strStyleCursorHand = " style=" & """" & "cursor:hand" & """" & "; "
strHorseHTML = "<table style='border:1px ridge #9966ff; position:relative; left:10px; "
strHorseHTML = strHorseHTML & "top:0px;' bgcolor='#ffffee' >"
strHorseHTML = strHorseHTML & "<tr>"
strHorseHTML = strHorseHTML & "<td width='100%' class='ContentSmall' align='left' valign='top' " & strStyleCursorHand & strClickToPrint & " > "
strHorseHTML = strHorseHTML & "PRINT"
strHorseHTML = strHorseHTML & "</td></tr></table><br>"
Print #2, strHorseHTML
'``````````````````````````````````````````
'Display the horse name in a table
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "<table><tr><td>"
strHorseHTML = strHorseHTML & FieldVal(45)
strHorseHTML = strHorseHTML & "</td></tr></table><br>
Print #2, strHorseHTML
'``````````````````````````````````````````
'Close out the body and html tags
'``````````````````````````````````````````
strHorseHTML = strHorseHTML & "</body></html>"
Close #2
End Sub
2. Then, just under the comment in the original code snippet where it says:
"'Done With Single Horse, now do something interesting."
Simply place a Call to the above Sub, passing the array named
FieldVal that contains all of the horse data as an argument:
Call WriteHTMLReport(FieldVal)
You may have to play with it a bit to get the output you want. I
just did a cut and paste of pieces here and there from the HTML
Report I use in JCapper, but it does illustrate at least one
strategy for reading a past performance file to create an HTML Report as output.
Good Luck,
Jeff