Solved

How to output an html file using VB script

Posted on 2008-06-18
7
714 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

864 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now