Solved

How to output an html file using VB script

Posted on 2008-06-18
7
717 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 10

Accepted Solution

by:
Dxpert earned 500 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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