?
Solved

How to output an html file using VB script

Posted on 2008-06-18
7
Medium Priority
?
727 Views
Last Modified: 2011-10-03
Greetings,

I have a routine that parses a record set and creates an xml file for each record - see the code below.  Now I need to create an html file that contains links to each of the files that were generated.  How would I include this html file as output so that xml file links appear in the html file?

Notice the line below that reads:

' ******* modify xml file name so that it is written as a link *********

I'm assuming this is the only thing that keeps me from doing what I need.  I can't figure out what is necessary in terms of a file path and/or the link syntax

Thanks!

Set Records = SqlCmd.Execute 
     
	    If Records.EOF Then 
	        MsgBox "No Records returned."
		    strResults = ""
	    Else
  	        Set objFSO1 = CreateObject("Scripting.FileSystemObject")
   	        Set objFSO2 = CreateObject("Scripting.FileSystemObject")
            Set objNewFile1 = objFSO1.CreateTextFile("C:\XmlIndex.html", ForAppending)  ' ********  html file *********
 
            Do Until Records.EOF
              strLine = ""
	            For DataFields = 0 To Records.Fields.Count -1.
		            If DataFields = 0 Then		              
			            
   			    	strNewFile2 = Records.Fields(DataFields).Value & ".xml"        ' ********  xml file name ********
			        strLine = Records.Fields(DataFields).Value
			            
                        	Set objNewFile2 = objFSO2.CreateTextFile("C:\" & strNewFile2, ForWriting)
 
                        	strNewFile2 =   ' ******* modify xml file name so that it is written as a link *********
   			        objNewFile1.WriteLine strNewFile2   ' ********  write to html file using xml file name *********
		            Else
			        strLine = strLine & "," & Records.Fields(DataFields).Value
	                    	objNewFile2.WriteLine strLine
	                    	objNewFile2.Close
		            End If
	            Next        	
	            Records.MoveNext
 
            Loop
            objNewFile1.Close
            Set objFSO1 = Nothing
            Set objFSO2 = Nothing
            Set objNewFile1 = Nothing
            Set objNewFile2 = Nothing

Open in new window

0
Comment
Question by:John500
  • 4
  • 3
7 Comments
 
LVL 10

Expert Comment

by:Dxpert
ID: 21815916
Are you running this on a intranet?
This code will only work on IE and on the same PC where the XML files are.


If DataFields = 0 Then                            
  strNewFile2 = Records.Fields(DataFields).Value & ".xml"        ' ********  xml file name ********
  strLine = Records.Fields(DataFields).Value
  strNewFileFullPath = "C:\" & strNewFile2  '** NEW LINE

  Set objNewFile2 = objFSO2.CreateTextFile(strNewFileFullPath, ForWriting) '** CHANGED LINE
  objNewFile1.WriteLine "<a href='" &  strNewFileFullPath & "'>" & strNewFile2 & "</a>"  '** CHANGED LINE
Else
...

0
 

Author Comment

by:John500
ID: 21816539
Yes - running on an Intranet

Can I throw one more element in there which includes a simple title for the page along with the date it was written?

Thanks!

0
 
LVL 10

Expert Comment

by:Dxpert
ID: 21816592
Not sure I know what you mean. Where do you want to add a Title and Date? To the body of HTML file, or to the LINK?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Accepted Solution

by:
Dxpert earned 2000 total points
ID: 21816663
Not sure which one you wanted, so I've added both:



Set objNewFile1 = objFSO1.CreateTextFile("C:\XmlIndex.html", ForAppending)  ' ********  html file *********
objNewFile2.WriteLine "<H1>XML File Title</H1>" '** ADDED LINE
 
Do Until Records.EOF
  strLine = ""
		  For DataFields = 0 To Records.Fields.Count -1.
					If DataFields = 0 Then                            
					  strNewFile2 = Records.Fields(DataFields).Value & ".xml"        ' ********  xml file name ********
					  strLine = Records.Fields(DataFields).Value
					  strNewFileFullPath = "C:\" & strNewFile2 '** ADDED LINE
					  strLinkTitle = "XML File Title - " & cstr(date()) '** ADDED LINE
 
					  Set objNewFile2 = objFSO2.CreateTextFile(strNewFileFullPath, ForWriting)
					  objNewFile1.WriteLine "<a href='" &  strNewFileFullPath & "'>" & strLinkTitle & "</a><br />" '** CHANGED LINE
					Else
						  strLine = strLine & "," & Records.Fields(DataFields).Value
						  objNewFile2.WriteLine strLine
						  objNewFile2.Close
					 End If
		  Next                
		  Records.MoveNext
 
Loop

Open in new window

0
 

Author Comment

by:John500
ID: 21816949
I needed the title for the html file.... but thanks for the extra!!
0
 

Author Closing Comment

by:John500
ID: 31468515
Thanks again
0
 
LVL 10

Expert Comment

by:Dxpert
ID: 21816980
Thanks! Glad I could help ;-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 17 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question