Solved

How to output an html file using VB script

Posted on 2008-06-18
7
716 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

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…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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…

831 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