Hello Gurus, this is what I think is a simple script.
I am a student in charge (as a student aide) of helping one of our departments sort of modernize the way they post new campus jobs on the campus website. I have been converting the 97 databases to the 2007 databases, ran through a couple of problems but I have fixed them all. One of the backend modules allows the reports to be turned into html files, but they copy the files to a floppy drive so one of the employees can upload the floppy drive files to a FTP. The script is about 10 years old and instead of going to a floppy drive, I want to be able to have the files on the hard drive in my documents or something of that nature, maybe even (if possible) upload them automatically.
I am going to paste the code right here, and if any bright minds can streer me in the right direction I would appreciate it.
I can basically see where it says a:/ that will need to be changed, I just don't want to mess it up. Thank you!!
I am a student in charge (as a student aide) of helping one of our departments sort of modernize the way they post new campus jobs on the campus website. I have been converting the 97 databases to the 2007 databases, ran through a couple of problems but I have fixed them all. One of the backend modules allows the reports to be turned into html files, but they copy the files to a floppy drive so one of the employees can upload the floppy drive files to a FTP. The script is about 10 years old and instead of going to a floppy drive, I want to be able to have the files on the hard drive in my documents or something of that nature, maybe even (if possible) upload them automatically.
I am going to paste the code right here, and if any bright minds can streer me in the right direction I would appreciate it.
I can basically see where it says a:/ that will need to be changed, I just don't want to mess it up. Thank you!!
Code:
'*************
'Created by:
'Date Created: 12/16/99
'Function Name: InternetPost
'Purpose: InternetPost is a function written to create an HTML file of all the open positions in the HR Job Positing database
'************
'************
'Variable Dictionary
'dBase: A variable of type Database, used as a central part of DAO (see help for info) to store the database information
'recSet: A variable of type Recordset, used as a central part of DAO (see help for info) to store information about a specific set of records in the database
'strTitle: String used to store the job title
'strDept: String used to store the department
'strRate: String used to store the pay rate
'strSupervisor: String used to store the supervisor
'counter: An integer value used to compare the count of the records processed to the total number of records
'recCount: An integer value that stores the number of records in the recordset
'************
Function InternetPost(ByVal postType As String) 'Function Declaration
On Error GoTo Error_Msg
Dim dbase As Database 'Variable Declarations
Dim recSet As Recordset
Dim strTitle As String, strDept As String
Dim counter As Integer, recCount As Integer
MsgBox ("Please insert a disk into Floppy Drive (A).") 'Prompts for Disk
Set dbase = CurrentDb() 'Sets the dBase variable to the Currently opened database
'Opens the correct HTML files depending on postings being updated
If postType = "PT" Then
Open "a:\hr_open_pt.html" For Output As #1
Open "a:\hr_lastmod_pt.html" For Output As #2
Set recSet = dbase.OpenRecordset("SELECT * FROM [PT Support Postings] ORDER BY [Open Date] DESC;") 'Sets the recSet variable to an already defined recordset (table) found within the database (dBase)
ElseIf postType = "FT" Then
Open "a:\hr_open_ft.html" For Output As #1
Open "a:\hr_lastmod_ft.html" For Output As #2
Set recSet = dbase.OpenRecordset("SELECT * FROM [FT Support Postings] ORDER BY [Open Date] DESC;") 'Sets the recSet variable to an already defined recordset (table) found within the database (dBase)
Else
Open "a:\hr_open_admin.html" For Output As #1
Open "a:\hr_lastmod_admin.html" For Output As #2
Set recSet = dbase.OpenRecordset("SELECT * FROM [Admin Postings] ORDER BY [Open Date] DESC;") 'Sets the recSet variable to an already defined recordset (table) found within the database (dBase)
End If
counter = 0 'Initializes the counter to 0
recSet.MoveLast 'Moves the last record in the set
recCount = recSet.RecordCount 'Counts the total records passed in the MoveLast operation
recSet.MoveFirst 'Moves to the first record
Print #1, "<!--Begin Open Positions-->" 'Outputs the text enclosed in quotes to file #1 (openpos.html)
While Not recSet.EOF 'Repeats the enclosed code until the EOF (End of File) of the recordset
If recSet![Active Position] = True Then
counter = counter + 1 'Increments the counter
'The following section outputs all the HTML to the file, in each line a part of the record is also output (strTitle, UIN, etc.) - this output occurs for each posting
'The double quotes are used to put quotes around the numbers in HTML
'The AdjCase surrounding some variables is a function defined below that corrects the capitalization of the values stored in the database (since they're all caps)
Print #1, "<table width=""578"" border=""0"" cellspacing=""2"" cellpadding=""1"">"
'Outputs Heading of Posting
Print #1, "<tr><td align=""center""><font size=""4""><b>POSITION: </b></font><font size=""4"" color=""blue""><b>"; recSet![Position Title]; "</b></font></td></tr>"
Print #1, "<tr><td width=""100%"" bgcolor=""#000000""><img src=""images/spacer.gif"" width=""1"" height=""1"" alt="" "" border=""0""></td></tr>"
Print #1, "<tr><td><table width=""100%"" cellspacing=""1"" cellpadding=""1"">"
Print #1, "<tr><td><b>Salary:</b></td><td>"; recSet![Salary]; "</td><td><b>Location:</b></td><td>"; recSet![Location]; "</td></tr>"
Print #1, "<tr><td><b>Position Status:</b></td><td>"; recSet![Position Status]; "</td><td><b>Hours:</b></td><td>"; recSet![Hours]; "</td></tr>"
Print #1, "<tr><td><b>Position Number:</b></td><td>"; recSet![Position Number]; "</td><td><b>Open Date:</b></td><td>"; recSet![Open Date]; "</td></tr>"
Print #1, "<tr><td><b>FLSA Status:</b></td><td>"; recSet![FLSA Status]; "</td><td><b>Notes:</b></td><td>"; recSet![Notes]; "</td></tr>"
Print #1, "</table></td></tr>"
Print #1, "<tr><td width=""100%"" bgcolor=""#000000""><img src=""images/spacer.gif"" width=""1"" height=""1"" alt="" "" border=""0""></td></tr>"
'End Heading
'Department Web Site
If (recSet![Department Web Site] <> "") Then
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""3"" alt="" "" border=""0""></td></tr>"
Print #1, "<tr><td><b>Department Web Site: </b><a href="; recSet![Department Web Site]; ">"; recSet![Department Web Site]; "</td></tr>"
End If
'End Department Web Site
'Nature of Work
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""5"" alt="" "" border=""0""></td></tr>"
Print #1, "<tr><td><font size=""3"" color=""red""><b>NATURE OF WORK:</b></font><br>"; recSet![Nature of Work]; "</td>"
'End Nature of Work
'Illustrative Examples of Work
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""5"" alt="" "" border=""0""></td></tr>"
Print #1, "<tr><td><font size=""3"" color=""red""><b>ILLUSTRATIVE EXAMPLES OF WORK:</b></font><br>"; BreakOut(recSet![Illustrative Examples of Work], recSet![Number of Examples]); "</td>"
'End Illustrative Examples of Work
'Requirements of Work
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""5"" alt="" "" border=""0""></td></tr>"
If (recSet![Number of Work Reqs] = 0) Then
Print #1, "<tr><td><font size=""3"" color=""red""><b>REQUIREMENTS OF WORK:</b></font><br>"; recSet![Requirements of Work]; "</td>"
Else
Print #1, "<tr><td><font size=""3"" color=""red""><b>REQUIREMENTS OF WORK:</b></font><br>"; BreakOut(recSet![Requirements of Work], recSet![Number of Work Reqs]); "</td>"
End If
'End Requirements of Work
'Space
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""5"" alt="" "" border=""0""></td></tr>"
'End Space
'If the counter is equal to the number of records, it denotes the end of the postings and exits the loop, otherwise it inserts the goto top button
If counter = recCount Then
Print #1, "<!--End Open Positions-->"
Else
Print #1, "<tr><td align=""right""><a href=""#TOP""><img src=""images/gotop.gif"" width=""45"" height=""20"" border=""0""></a></td></tr>"
End If
'Space Between Postings
Print #1, "<tr><td width=""100%"" bgcolor=""#FFFFFF""><img src=""images/spacer.gif"" width=""1"" height=""5"" alt="" "" border=""0""></td></tr>"
'End Space
'Outputs the </table> tag which indicates the end of the posting
Print #1, "</table>"
End If
recSet.MoveNext 'Moves to the next record in the set
Wend 'Marks end of code in While loop
'Outputs to file #2 (lastmod.html) the last date modified, etc.
Print #2, "Last Modified: "; Format(Now, "dd-mmmm-yy hh:nn:ss"); " EST<br>"
'Closes the recordset and files
recSet.Close
Close #1
Close #2
'Message box that notifies the user of a succesful completion along with instructions for uploading the file.
MsgBox ("The job postings HTML files have been created. Next, please open WS_FTP so you can transfer the files to the School server. Make sure you upload the appropriate HTML files to the server.")
Exit Function
Error_Msg:
If Err.Number = "3021" Then
MsgBox ("Table has no postings to process, please add a posting and try again.")
End If
recSet.Close
Close #1
Close #2
Exit Function
End Function
