Working with SUBSTR - KiXtart.org

You are not logged in. [Log In] KiXtart.org website » Forums » KiXtart » Basic Scripting » Working with SUBSTR

Page 1 of 1

Topic Options

#207937 - 2013-10-24 06:44 PM

Working with SUBSTR

Karon

Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas

I have a script that works fine with the older version of kix (windows 2003 server) but not the newest one.
I am using a text file to identify excel tabs and print to pdf995. My problem is that during the process, the information inside the pdf belongs to a different person than is labeled.
This is the entire script (I have tried to make it generic as to locations and filenames)'

Code:

$home = "K:\Doctor Reports\"
$setup = "\\<server>\mis\reports\PArvu.txt"

;open txt file with tab names and email addresses
IF Open(1, "$setup") = 0
$drfile = ReadLine(1)
ELSE
BEEP
? "Doc file not opened, error code: [" + @ERROR + "]"
sleep 5
EXIT
ENDIF

USE T: "\\<server2>\<dir>\123r4w"
$thismo = @monthno -1
IF $thismo = 0
  $month = "12"
ELSE
  IF $thismo > 9
    $month = CSTR($thismo)
  ELSE
    $month = "0" + CSTR($thismo)
  ENDIF
ENDIF
$year = IIf(@MONTHNO = 1, @YEAR -1, @YEAR)


$readerr = 0

;open Excel file
$app = CreateObject("Excel.Application")
;$app.visible = 1
;Create the Workbooks object
$wbs = $app.Workbooks
$rc = $wbs.Open("T:\Sherry\$drfile.xls",0)

?"drfile = "$drfile

;main program loop
WHILE $readerr = 0
$x = ReadLine(1)
$readerr = @ERROR
$doc = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$folder = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$drno = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$spec = SUBSTR($x, 1, LEN($x) - INSTR($x, ","))

?$doc

IF $doc <> ""


  WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File","$home$folder\Recap Reports\$doc-Recap-$year-$month.pdf")
  $rc.Worksheets("$doc").PrintOut(,,,,"PDF995")
  sleep 3
  WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File","$home$folder\(Current Month)\$doc-Recap-$year-$month.pdf")
  $rc.Worksheets("$doc").PrintOut(,,,,"PDF995")
  sleep 3
Else
?"doc is blank"
ENDIF

LOOP

WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","User File"," ")
WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Initial Dir"," ")
WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Launch"," ")
WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File"," ")
$ = Close (1)

;IMPORTANT--YOU MUST CLOSE THE WORKBOOKS OBJECT OR ELSE
;			THE EXCEL PROCESS WILL NOT TERMINATE!!!
$wbs.Close

;Destroy the Worksheet, Workbooks, and Application objects
$wbs = 0
$app = 0


USE LIST
USE T: /DELETE
USE LIST

A sample of the setup file:
mlrp0913
ADAMS.VELMA,ADAMS.VELMA,347,NS
ADAMS.VELMA,HUGHS,347,NS
BLAINE.CATHERINE,BLAINE.CATHERINE,254,FP
BLAINE.CATHERINE,BAKER,254,FP
CHAMBERS.EDDIE,CHAMBERS.EDDIE,490,EMER
DOBBS.ELIZABETH,DOBBS.ELIZABETH,7501,IM
DOBBS.ELIZABETH,KNIGHT,7501,IM

filename(less extension)
$doc,$folder,$drno,$spec

You will note that the output file is placed in 2 locations. The problem is the data in the first file - which has the correct name - contains information from a different $doc

Can anyone explain to me, why the $doc information is being corrupted? and how can I fix it?
We are using office 2010 on windows 7 pc's.

Top

#207938 - 2013-10-24 09:06 PM

Re: Working with SUBSTR [Re: Karon]

ShaneEP Offline

MM club member

Registered: 2002-11-29
Posts: 2127
Loc: Tulsa, OK

I looked through the script, didn't see anything too out of place. I stripped it down and simplified it some. Also removed all the variables from within strings, and added an extra IF check in the loop.

You'll see that I replaced the whole substr section with a much simpler split() which just splits the line into an array.

Again, I stepped through the script, and didn't seem to notice anything that would break it. But give my version a shot, see if you get anything different.

Code:

$home = "K:\Doctor Reports\"
$setup = "\\<server>\mis\reports\PArvu.txt"

$month = @monthno-1
IF $month = 0
   $month = "12"
ELSE
   If Len($month)<2
      $month = "0"+$month
   ENDIF
ENDIF
$year = IIf(@MONTHNO = 1, @YEAR-1, @YEAR)

;open txt file with tab names and email addresses

IF Open(1, $setup) = 0
   $drfile = ReadLine(1)
ELSE
   BEEP
   ? "Doc file not opened, error code: [" + @ERROR + "]"
   sleep 5
   EXIT
ENDIF

USE T: "\\<server2>\<dir>\123r4w"

;open Excel file
$app = CreateObject("Excel.Application")

;$app.visible = 1
;Create the Workbooks object
$wbs = $app.Workbooks
$rc = $wbs.Open("T:\Sherry\"+$drfile+".xls",0)

? "drfile = "+$drfile

$readerr = 0
;main program loop
WHILE $readerr = 0
   $x = ReadLine(1)
   $readerr = @ERROR

   IF InStr($x, ",")
      $x = Split($x, ",")
      $doc = $x[0]
      $folder = $x[1]
      $drno = $x[2]
      $spec = $x[3]

      IF Len($doc)
         ? $doc
         $dir = $home$folder
         $file = $doc+"-Recap-"+$year+"-"+$month
         $nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File",$dir+"\Recap Reports\"+$file+".pdf")
         $rc.Worksheets($doc).PrintOut(,,,,"PDF995")
         sleep 3
         $nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File",$dir+"\(Current Month)\"+$file+".pdf")
         $rc.Worksheets($doc).PrintOut(,,,,"PDF995")
         sleep 3
      Else
         ? "doc is blank"
      ENDIF
   ENDIF
LOOP
$nul = Close (1)

$nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","User File"," ")
$nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Initial Dir"," ")
$nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Launch"," ")
$nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File"," ")

;IMPORTANT--YOU MUST CLOSE THE WORKBOOKS OBJECT OR ELSE
;			THE EXCEL PROCESS WILL NOT TERMINATE!!!
$wbs.Close

;Destroy the Worksheet, Workbooks, and Application objects
$wbs = 0
$app = 0


USE LIST
USE T: /DELETE
USE LIST

Edited by ShaneEP (2013-10-24 09:07 PM)

Top

#207939 - 2013-10-25 12:01 AM Re: Working with SUBSTR [Re: ShaneEP]
Karon Karon Getting the hang of it Registered: 2009-07-16 Posts: 87 Loc: Texas	SWEET! I will test tomorrow!!
Top

#207942 - 2013-10-25 08:03 PM

Re: Working with SUBSTR [Re: ShaneEP]

Karon

Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas

Do I need the "+" in the following:

Code:

$rc = $wbs.Open("T:\Sherry\"+$drfile+".xls",0)

and

Code:

         $file = $doc+"-Recap-"+$year+"-"+$month
         $nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File",$dir+"\Recap Reports\"+$file+".pdf")
         $rc.Worksheets($doc).PrintOut(,,,,"PDF995")
         sleep 3
         $nul = WriteProfileString("C:\Program Files\pdf995\res\pdf995.ini","Parameters","Output File",$dir+"\(Current Month)\"+$file+".pdf")

Top

#207943 - 2013-10-25 09:02 PM Re: Working with SUBSTR [Re: Karon]
Karon Karon Getting the hang of it Registered: 2009-07-16 Posts: 87 Loc: Texas	2 issues so far It is printing to the output screen the $doc name twice - so it is reading the $doc and $folder as the $doc instead of as 2 seperate entries. So when it produces the file, it places it in $home\(Current Month) instead of $home$folder\(Current Month) (there is a folder named (Current Month) in $home and in $folder Sample output: ADAMSADAMS-1-1 Location should be: $home\$folder\(Current Month) and $home\$folder\SUBFOLDER1 Location it is: $home\(Current Month)
Top

#207944 - 2013-10-25 09:39 PM Re: Working with SUBSTR [Re: Karon]
Allen Allen KiX Supporter Registered: 2003-04-19 Posts: 4574 Loc: USA	Quote: Do I need the "+" in the following: If you leave the variables in your strings... Need... no. When you take the variables out of the string, you need to include the "+"'s. Having the variables outside the strings is good coding practice. _________________________ (... better days ahead)
Top

#207945 - 2013-10-25 11:42 PM Re: Working with SUBSTR [Re: Allen]
Karon Karon Getting the hang of it Registered: 2009-07-16 Posts: 87 Loc: Texas	Thanks! I will leave them in. Now if we can just figure out why the $doc and $folder variables are showing up as the $doc and no $folder variable available.. So weird...
Top

#207946 - 2013-10-26 01:29 AM Re: Working with SUBSTR [Re: Karon]
ShaneEP ShaneEP MM club member Registered: 2002-11-29 Posts: 2127 Loc: Tulsa, OK	It looks like I forgot a + sign in the below line. I don't think it's really a problem however other than sloppy coding. Let me look further. Code: $dir = $home+$folder
Top

#207947 - 2013-10-26 01:43 AM Re: Working with SUBSTR [Re: ShaneEP]
ShaneEP ShaneEP MM club member Registered: 2002-11-29 Posts: 2127 Loc: Tulsa, OK	Actually...turns out that plus I forgot may indeed be causing the problem. Try changing that one line and see if it helps. Just change this... Code: $dir = $home$folder to this Code: $dir = $home+$folder Edited by ShaneEP (2013-10-26 01:46 AM)
Top

#207982 - 2013-11-01 03:12 PM Re: Working with SUBSTR [Re: ShaneEP]
Karon Karon Getting the hang of it Registered: 2009-07-16 Posts: 87 Loc: Texas	Sweet!! I think you are right!! I will work this again. I have to wait until it is time again, but I will definitely let you know. Thanks!
Top

Page 1 of 1

Previous Topic View All Topics

Index Next Topic

Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart

Hop to:

Shout Box

Search the board with:
superb Board Search
or try with google:

	Web kixtart.org