String Constructed from Recordset Values Misbehaving 1

[tradle]

Hi Everyone!

Long time, no post. Here's today's quandry...

I'm trying to tell my script to only do a function on those files that are identified by a couple of queries. I actually use a couple of entries from recordsets to construct the string filenames, but I'm having something of an issue with the syntax in creating that string value and then manipulating the value correctly to pass through the rest of the function code. The error I currently get references the line "for each f in fc = "(rs3("Acct")&"_"&(rs2("Date")&".ps"" with the error "Expected end of statement". I'm missing something - what the heck is it?

As always, thanks for taking the time to take a look.

Tim

Function Test()
   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   Dim fso, ts, source, fc, dbII, dbIII
   dbII = "Advent_Reports"
   dbIII = "NCC_DW"
   set con2 = CreateObject("ADODB.Connection")
   set con3 = CreateObject("ADODB.Connection")
   strCon2 = "driver={SQL SERVER};server=SERVER;uid=user;pwd=pwd;database=" & dbII & ""
   strCon3 = "driver={SQL SERVER};server=SERVER;uid=user;pwd=pwd;database=" & dbIII & ""
   con2.Open strCon2
   con3.Open strCon3
   
   sql2 = "select FileAsOfDate as Date, Period as Period" & _
        	" from Advent_Reports.dbo.BowneTags "      
	set rs2 = CreateObject("ADODB.Recordset")
		rs2.Open sql2, con2

   sql3 = "select Acct_Nbr as Acct" & _
		" from NCC_DW.dbo.Account_Status" & _
		" where Reason is not null and ReasonIO is not null and Period = "&(rs2("Period"))
	set rs3 = CreateObject("ADODB.Recordset")
		rs3.Open sql3, con3

	   Set fso = CreateObject("Scripting.FileSystemObject")
	   set source = fso.GetFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress")
	   set fc = source.Files

		for each f in fc = "(rs3("Acct")&"_"&(rs2("Date")&".ps"
		(DO STUFF)
		
		rs2.Close
		rs3.Close
	set rs2 = Nothing
	set rs3 = Nothing

End Function

 

[PHV]

Something like this ?
for each f in fc
If UCase(f.Name)=UCase(rs3("Acct")&"_"&rs2("Date")&".ps") Then
(DO STUFF)
End If
next 'f

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tradle]

PHV -

Thanks for your reply. The script runs successfully, but only for one file instead of looping through the rest of the directory. As best as I can tell, all of the loops are complete - can you tell what might be going on? The full code is posted below. I was able to consolidate my connections and only read from one source; that's the only treal change to what I've sent previously.

Regards,
Tim

Function TextStreamTest()
   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
   Dim fso, ts, source, fc, dbII 
   dbII = "Advent_Reports"
   set con2 = CreateObject("ADODB.Connection")
   strCon2 = "driver={SQL SERVER};server=SERVER;uid=user;pwd=pwd;database=" & dbII & ""
   con2.Open strCon2
   
   sql2 = "select FileAsOfDate as Date, Period as Period, ACCOUNT_ID as Acct" & _
        	" from Advent_Reports.dbo.BowneTags "      
	set rs2 = CreateObject("ADODB.Recordset")
		rs2.Open sql2, con2

	   Set fso = CreateObject("Scripting.FileSystemObject")
	   set source = fso.GetFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress")
		wscript.echo source
	   set fc = source.Files

		for each f in fc
		  If UCase(f.Name)=UCase(rs2("Acct")&"_"&rs2("Date")&".ps") Then
			bPageSetup = False
			bPage1 = False
			Set tsIni = FSO.OpenTextFile(f)
			file2create ="f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress\Step1\" & f.Name
			Set newInifile = fso.CreateTextFile(file2Create, True)

			Do While Not tsIni.AtEndOfStream
			    sLine = tsIni.ReadLine
			    newIniFile.WriteLine sLine
		    	Select Case sLine
			       	Case "%%LanguageLevel: 2"
				 newInifile.WriteLine "%%ADOContainsXMP:MainFirst"
	       			Case "% %%EndPageSetup"
			         If bPageSetup = False Then
			         newInifile.WriteLine "" &VBNewLine	
			         newInifile.WriteLine "%%ControlNumber" &VBNewLine
			         newInifile.WriteLine "gsave"
			         newInifile.WriteLine "/Times-Roman findfont 50 scalefont setfont"
		        	 newInifile.WriteLine "180 rotate"
			         newInifile.WriteLine "-1 1 scale"
			         newInifile.WriteLine "907 -1021 moveto"
			         newInifile.WriteLine "(000000.0000.00.0000.000.000000000.000000) show"
		        	 newInifile.WriteLine "grestore" & VBNewLine
				 bPageSetup = True
				 End If
			       Case "(%%[Page: 1]%%) = "
				 If bPage1 = False Then
				 newInifile.WriteLine "" & VBNewLine		
			         newInifile.WriteLine "/ncbdict 1 dict def" & VBNewLine		
	  			 newInifile.WriteLine "ncbdict begin" & VBNewLine
	  			 newInifile.WriteLine "/currentdistillerparams where"
		  		 newInifile.WriteLine "{pop currentdistillerparams /CoreDistVersion get 5000 lt} {true} ifelse" & VBNewLine
		  		 newInifile.WriteLine "{ncbdict /pdfmark /cleartomark load put"
	  			 newInifile.WriteLine "	ncbdict /metafile_pdfmark {flushfile cleartomark} bind put}"
	  			 newInifile.WriteLine "{ncbdict /metafile_pdfmark {/PUT pdfmark} bind put} ifelse" & VBNewLine		
		  		 newInifile.WriteLine "%Define the stream object and associate it as metadata"
		  		 newInifile.WriteLine "[/_objdef {ncb_metadata_stream@stream1@} /type /stream /OBJ pdfmark"
	  			 newInifile.WriteLine "[{ncb_metadata_stream@stream1@} 2 dict begin"
	  			 newInifile.WriteLine "	/Type /Metadata def /Subtype /XML def currentdict end /PUT pdfmark" & VBNewLine				
		  		 newInifile.WriteLine "%Fill the stream with the XMP Packet..."
		  		 newInifile.WriteLine "[{ncb_metadata_stream@stream1@}"
 	  			 newInifile.WriteLine "	currentfile 0 (% &&end XMP packet marker&&)"
	  			 newInifile.WriteLine "	/SubFileDecode filter metafile_pdfmark"
	 	 		 newInifile.WriteLine "<?xpacket begin='?' id='W5M0MpCehiHzreSzNTczkc9d'?>"
 				 newInifile.WriteLine "<x:xmpmeta xmlns:x='adobe:ns:meta/'>"
	  			 newInifile.WriteLine "<rdf:RDF xmlns:iX='[URL unfurl="true"]http://ns.adobe.com/iX/1.0/'>"[/URL]
	  			 newInifile.WriteLine "<rdf:Description rdf:about='' xmlns:ncb='//ohclesql1026/ncb/metadata/' ncb:CompTime='COMP_TIME|' ncb:PkgSeq='PKG_SEQUENCE|@stream2@' ncb:StmtSeq='STMT_SEQUENCE|@stream3@' ncb:Mail1='MAIL_TO1|@stream4@' ncb:Mail2='MAIL_TO2|@stream5@' ncb:Mail3='MAIL_TO3|@stream6@' ncb:Mail4='MAIL_TO4|@stream7@' ncb:Mail5='MAIL_TO5|@stream8@' ncb:Mail6='MAIL_TO6|@stream9@' ncb:ZipCode='ZIP_CODE|' ncb:InsMap='INSERT_MAP|YNNNNNNN' ncb:InsId='INSERT_ID|NCIM1Q04                                                                         ' ncb:AcctId='ACCOUNT_ID|@stream10@' ncb:PaperCde='PAPER_CODE|10001453' ncb:EnvCde='ENV_CODE|10001538' ncb:SpecHand='SPECIAL_HANDLING|N'/>"
		  		 newInifile.WriteLine "</rdf:RDF>"
		  		 newInifile.WriteLine "</x:xmpmeta>"
	  			 newInifile.WriteLine "<?xpacket end='w'?>" & VBNewLine				
		  		 newInifile.WriteLine "% &&end XMP packet marker&&" & VBNewLine				
		  		 newInifile.WriteLine "%Attach the stream as the main XMP metadata stream"
		  		 newInifile.WriteLine "[{Catalog} {ncb_metadata_stream@stream1@} /Metadata pdfmark" & VBNewLine
				 bPage1 = True
				 End If
			    End Select        

			Loop
			
			newInifile.Close
			tsIni.Close
		
		  End If
		Next 'f 		

		rs2.Close 
	Set rs2 = Nothing
End Function

WScript.echo TextStreamTest()

 

[PHV]

but only for one file
Yes only for the first occurence of;
rs2("Acct")&"_"&rs2("Date")&".ps"
Maybe something like this ?
Set fso = CreateObject("Scripting.FileSystemObject")
[highlight]While Not rs2.EOF[/highlight]
set source = fso.GetFolder("f:\advent\wrc\ps\"&(rs2("Date"))&"\In_Progress")
.../...
Next 'f
[highlight] rs2.MoveNext
WEnd[/highlight]
rs2.Close

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[tradle]

That works prefectly - thanks for filling in the holes, PHV.

Tim