Page 1 of 1 1
Topic Options
#211751 - 2016-07-05 10:12 PM If/Else statement
Karon Offline
Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas
I have an inherited script that currently will not close excel and if a temp file is missing it throws an error.
I need an If/Else and then send the script back to the beginning of the loop.
I am going to add the lines for closing excel inside the loop.
This is the script - I didn't include the full sqlcmd lines because the lines are huge.
 Code:
$setup = "\\server\share\folder\names.txt"

$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)

;open txt file with doc names 
IF Open(1, "$setup") <> 0
BEEP
? "spec file not opened, error code: [" + @ERROR + "]"
sleep 5
EXIT
ENDIF
Dim $accessApp

$accessApp = CreateObject("Access.Application")
;$accessApp.visible = 1
$accessApp.OpenCurrentDatabase("\\server\share\folder\file.mdb")

$accessApp.DoCmd.RunMacro("exportdocs")

sleep 10

;main program loop

WHILE $readerr = 0
$x = ReadLine(1)
$readerr = @ERROR
IF $readerr <> 0
EXIT
ENDIF
$key = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$drname = SUBSTR($x, 1, LEN($x) - INSTR($x, ","))



$accessApp.DoCmd.RunSQL("SELECT [prod2]![drno] AS [Key], prod2.class, Sum(prod2.Oct_amt) AS Oct_amt, Sum(prod2.Sep_amt) AS Sep_amt, Sum(prod2.Tot_amt) AS Tot_amt INTO tempsum
FROM prod2
GROUP BY [prod2]![no], prod2.class
HAVING ((([prod2]![no])=$key));")
Sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, Sum(tempsum.Oct_amt) AS Oct_amt, Sum(tempsum.Sep_amt) AS Sep_amt, Sum(tempsum.Tot_amt) AS Tot_amt INTO temptot
FROM tempsum
GROUP BY tempsum.Key;")

sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, classdesc.description, tempsum.Oct_amt, [tempsum]![Oct_amt]/[temptot]![Oct_amt] AS [Oct_pct],  tempsum.Sep_amt,  [tempsum]![Sep_amt]/[temptot]![Sep_amt] AS [Sep_pct], tempsum.Tot_amt, [tempsum]![Tot_amt]/[temptot]![Tot_amt] AS [Tot_pct] INTO docpct
FROM temptot, tempsum INNER JOIN classdesc ON tempsum.class = classdesc.class;")
sleep 1

$accessApp.DoCmd.RunMacro("exportnamepct")


sleep 1

;added below to loop
$accessApp.DisplayAlerts = 0
$accessApp.Quit
$accessApp = 0

; open excel file apptutil created by Access macro above
;  format and save with correct name
$app = CreateObject("Excel.Application")
;$app.visible = 1

;Create the Workbooks object
$wbs = $app.Workbooks
;this is what I need the If Else statement for
$rc = $wbs.Open("\\server\share\folder\temp.xls")


$app.Range("d1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("f1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("h1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("j1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("l1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("n1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("p1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("r1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("t1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("v1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("x1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("z1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("ab1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("c1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("e1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("g1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("i1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("k1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("m1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("o1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("q1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("s1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("u1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("w1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("y1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("aa1").Entirecolumn.NumberFormat = "#,##0"


$app.Range("c24").Formula = "=SUM(c1:c23)"
$app.Range("e24").Formula = "=SUM(e1:e23)"
$app.Range("g24").Formula = "=SUM(g1:g23)"
$app.Range("i24").Formula = "=SUM(i1:i23)"
$app.Range("k24").Formula = "=SUM(k1:k23)"
$app.Range("m24").Formula = "=SUM(m1:m23)"
$app.Range("o24").Formula = "=SUM(o1:o23)"
$app.Range("q24").Formula = "=SUM(q1:q23)"
$app.Range("s24").Formula = "=SUM(s1:s23)"
$app.Range("u24").Formula = "=SUM(u1:u23)"
$app.Range("w24").Formula = "=SUM(w1:w23)"
$app.Range("y24").Formula = "=SUM(y1:y23)"
$app.Range("aa24").Formula = "=SUM(aa1:aa23)"

$app.Range("A1:AB1").insert
$app.Range("A1:AB1").merge
$array = "Fiscal Year 2011"
$app.Range("A1:AB2").HorizontalAlignment = 3 
$app.Range("A1:AB1").Value = $array
$app.Range("A1:AB2").Font.Bold = 1
$app.Range("A1:AB2").Borders.LineStyle = 1
$app.Range("A1:AB2").Borders.Weight = 1
$app.Range("A1:AB1").EntireColumn.AutoFit
;$app.Range("A1:AB2").Interior.ColorIndex = 15

$filename = "\\server\shares\folder\$name-$key-$year-$month.xls"
$app.ActiveWorkbook.SaveAs("$filename",-4143) 


Del "\\server\share\folder\temp.xls"
$wbs = 0
;added below to loop
$app.DisplayAlerts = 0
$app.Quit
$app = 0

LOOP

;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.DisplayAlerts = 0
$app.Quit
$app = 0

$accessApp.DisplayAlerts = 0
$accessApp.Quit
$accessApp = 0



sleep 1



So I need to check if the temp.xls is there, if not, return to the top of the loop.
I think I can remove the following at the end of the script:
 Code:
;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.DisplayAlerts = 0
$app.Quit
$app = 0

Top
#211752 - 2016-07-06 10:30 AM Re: If/Else statement [Re: Karon]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
I think the problem is, you open the workbook, yet you don't do anything with it and that's where the temp file comes from.
 Code:
$app = CreateObject("Excel.Application")
$wbs = $app.Workbooks
$rc = $wbs.Open("\\server\share\folder\temp.xls")
;here you start with the $app instead of the workbook.
$app.Range("d1").Entirecolumn.NumberFormat = "0.0%"
$filename = "\\server\shares\folder\$name-$key-$year-$month.xls"
$app.ActiveWorkbook.SaveAs("$filename",-4143)

I think it should be
 Code:
$app = CreateObject("Excel.Application")
$objWorkbook = $app.Workbooks.Open("\\server\share\folder\temp.xls")
$objWorkbook.Range("d1").Entirecolumn.NumberFormat = "0.0%"
$filename = "\\server\shares\folder\"+$name+"-"+$key+"-"+$year+"-"+$month+".xls"
$objWorkbook.SaveAs($filename,-4143)
$objWorkbook = ""
$app.Quit


Then again, I could be wrong.

Top
#211753 - 2016-07-06 03:21 PM Re: If/Else statement [Re: Arend_]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
I would recommend taking the creation of the Excel application out of the loop all together. You can create the object once, and then open and manipulate multiple files using the same object. Then close it after the loop. You are wasting a lot of resources the way you're doing it. I think I even posted an example with the same suggestion in one of your other posts.
Top
#211754 - 2016-07-06 03:27 PM Re: If/Else statement [Re: ShaneEP]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
You're also opening the access object only once, but then trying to close it multiple times in the loop.
Top
#211755 - 2016-07-06 03:52 PM Re: If/Else statement [Re: ShaneEP]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
I haven't even seen the loop, indeed a bit inefficient but still should not pose a problem. I've converted 500+ Excel 2003 templates into OpenXML templates this way.

But what I find strange is that you open temp.xls and edit it, but save as another:
 Code:
$rc = $wbs.Open("\\server\share\folder\temp.xls")
  $filename = "\\server\shares\folder\$name-$key-$year-$month.xls"
  $app.ActiveWorkbook.SaveAs("$filename",-4143) 

Unless it is a template, but then it should be converted to temp.xlt.
Also, using
 Code:
$app.DisplayAlerts = 0
only works if you set it just after creating the object, not before you close the object.

Top
#211756 - 2016-07-06 03:57 PM Re: If/Else statement [Re: Arend_]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
You really should post the whole script so we can investigate.
Like Shane said, you create the Access Object before the loop, but in the loop you close it.
Seems like the loop should end before you close it because I can't find anything in the Excel code that references anything Access related.

Top
#211757 - 2016-07-06 05:43 PM Re: If/Else statement [Re: Arend_]
Karon Offline
Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas
Ok - these are the changes I have made -
 Code:
$setup = "\\server\share\folder\names.txt"

$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)

;open txt file with names 
IF Open(1, "$setup") <> 0
BEEP
? "spec file not opened, error code: [" + @ERROR + "]"
sleep 5
EXIT
ENDIF
Dim $accessApp

$accessApp = CreateObject("Access.Application")
;$accessApp.visible = 1
$accessApp.OpenCurrentDatabase("\\server\share\folder\file.mdb")

$accessApp.DoCmd.RunMacro("exportnames")


$app = CreateObject("Excel.Application")
;$app.visible = 1

;Create the Workbooks object
$wbs = $app.Workbooks


sleep 10

;main program loop

WHILE $readerr = 0
$x = ReadLine(1)
$readerr = @ERROR
IF $readerr <> 0
EXIT
ENDIF
$key = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$drname = SUBSTR($x, 1, LEN($x) - INSTR($x, ","))



$accessApp.DoCmd.RunSQL("SELECT [prod2]![no] AS [Key], prod2.class, Sum(prod2.Oct_amt) AS Oct_amt, Sum(prod2.Nov_amt) AS Nov_amt, Sum(prod2.Dec_amt) AS Dec_amt, Sum(prod2.Jan_amt) AS Jan_amt, Sum(prod2.Feb_amt) AS Feb_amt, Sum(prod2.Mar_amt) AS Mar_amt, Sum(prod2.Apr_amt) AS Apr_amt, Sum(prod2.May_amt) AS May_amt, Sum(prod2.Jun_amt) AS Jun_amt, Sum(prod2.Jul_amt) AS Jul_amt, Sum(prod2.Aug_amt) AS Aug_amt, Sum(prod2.Sep_amt) AS Sep_amt, Sum(prod2.Tot_amt) AS Tot_amt INTO tempsum
FROM prod2
GROUP BY [prod2]![no], prod2.class
HAVING ((([prod2]![no])=$key));")

Sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, Sum(tempsum.Oct_amt) AS Oct_amt, Sum(tempsum.Nov_amt) AS Nov_amt, Sum(tempsum.Dec_amt) AS Dec_amt, Sum(tempsum.Jan_amt) AS Jan_amt, Sum(tempsum.Feb_amt) AS Feb_amt, Sum(tempsum.Mar_amt) AS Mar_amt, Sum(tempsum.Apr_amt) AS Apr_amt, Sum(tempsum.May_amt) AS May_amt, Sum(tempsum.Jun_amt) AS Jun_amt, Sum(tempsum.Jul_amt) AS Jul_amt, Sum(tempsum.Aug_amt) AS Aug_amt, Sum(tempsum.Sep_amt) AS Sep_amt, Sum(tempsum.Tot_amt) AS Tot_amt INTO temptot
FROM tempsum
GROUP BY tempsum.Key;")

sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, classdesc.description, tempsum.Oct_amt, [tempsum]![Oct_amt]/[temptot]![Oct_amt] AS [Oct_pct], tempsum.Nov_amt,  [tempsum]![Nov_amt]/[temptot]![Nov_amt] AS [Nov_pct], tempsum.Dec_amt, [tempsum]![Dec_amt]/[temptot]![Dec_amt] AS [Dec_pct], tempsum.Jan_amt, [tempsum]![Jan_amt]/[temptot]![Jan_amt] AS  [Jan_pct], tempsum.Feb_amt, [tempsum]![Feb_amt]/[temptot]![Feb_amt] AS [Feb_pct], tempsum.Mar_amt, [tempsum]![Mar_amt]/[temptot]![Mar_amt] AS [Mar_pct], tempsum.Apr_amt,  [tempsum]![Apr_amt]/[temptot]![Apr_amt] AS [Apr_pct], tempsum.May_amt, [tempsum]![May_amt]/[temptot]![May_amt] AS [May_pct], tempsum.Jun_amt, [tempsum]![Jun_amt]/[temptot]![Jun_amt] AS  [Jun_pct], tempsum.Jul_amt, [tempsum]![Jul_amt]/[temptot]![Jul_amt] AS [Jul_pct], tempsum.Aug_amt, [tempsum]![Aug_amt]/[temptot]![Aug_amt] AS [Aug_pct], tempsum.Sep_amt,  [tempsum]![Sep_amt]/[temptot]![Sep_amt] AS [Sep_pct], tempsum.Tot_amt, [tempsum]![Tot_amt]/[temptot]![Tot_amt] AS [Tot_pct] INTO namepct
FROM temptot, tempsum INNER JOIN classdesc ON tempsum.class = classdesc.class;")


sleep 1

$accessApp.DoCmd.RunMacro("exportnamepct")




sleep 1



;=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-


; open excel file created by Access macro above
;  format and save with correct name


$rc = $wbs.Open("\\server\share\folder\temp.xls")


$app.Range("d1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("f1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("h1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("j1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("l1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("n1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("p1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("r1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("t1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("v1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("x1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("z1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("ab1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("c1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("e1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("g1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("i1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("k1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("m1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("o1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("q1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("s1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("u1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("w1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("y1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("aa1").Entirecolumn.NumberFormat = "#,##0"


$app.Range("c24").Formula = "=SUM(c1:c23)"
$app.Range("e24").Formula = "=SUM(e1:e23)"
$app.Range("g24").Formula = "=SUM(g1:g23)"
$app.Range("i24").Formula = "=SUM(i1:i23)"
$app.Range("k24").Formula = "=SUM(k1:k23)"
$app.Range("m24").Formula = "=SUM(m1:m23)"
$app.Range("o24").Formula = "=SUM(o1:o23)"
$app.Range("q24").Formula = "=SUM(q1:q23)"
$app.Range("s24").Formula = "=SUM(s1:s23)"
$app.Range("u24").Formula = "=SUM(u1:u23)"
$app.Range("w24").Formula = "=SUM(w1:w23)"
$app.Range("y24").Formula = "=SUM(y1:y23)"
$app.Range("aa24").Formula = "=SUM(aa1:aa23)"

$app.Range("A1:AB1").insert
$app.Range("A1:AB1").merge
$array = "Fiscal Year 2011"
$app.Range("A1:AB2").HorizontalAlignment = 3 
$app.Range("A1:AB1").Value = $array
$app.Range("A1:AB2").Font.Bold = 1
$app.Range("A1:AB2").Borders.LineStyle = 1
$app.Range("A1:AB2").Borders.Weight = 1
$app.Range("A1:AB1").EntireColumn.AutoFit
;$app.Range("A1:AB2").Interior.ColorIndex = 15

$filename = "\\server\share\folder\folder2\$name-$key-$year-$month.xls"

$app.ActiveWorkbook.SaveAs("$filename",-4143) 


Del "\\server\share\folder\temp.xls"
$wbs = 0
;added below to loop
$app.DisplayAlerts = 0
$app.Quit
$app = 0

LOOP

;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.DisplayAlerts = 0
$app.Quit
$app = 0

$accessApp.DisplayAlerts = 0
$accessApp.Quit
$accessApp = 0



sleep 1


Top
#211758 - 2016-07-06 05:53 PM Re: If/Else statement [Re: Karon]
Arend_ Moderator Offline
MM club member
*****

Registered: 2005-01-17
Posts: 1894
Loc: Hilversum, The Netherlands
I can't see any changes you made that we've proposed to you, so try this:
 Code:
$setup = "\\server\share\folder\names.txt"

$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)

;open txt file with names 
If Open(1, "$setup") <> 0
  BEEP
  ? "spec file not opened, error code: [" + @ERROR + "]"
  sleep 5
  EXIT
EndIf

Dim $objAccess
$objAccess = CreateObject("Access.Application")
$objAccess.DisplayAlerts = 0
;$objAccess.visible = 1
$objAccess.OpenCurrentDatabase("\\server\share\folder\file.mdb")
$objAccess.DoCmd.RunMacro("exportnames")

$objExcel = CreateObject("Excel.Application")
$objExcel.DisplayAlerts = 0
;$objExcel.visible = 1

;Create the Workbooks object
$wbs = $objExcel.Workbooks
sleep 10

;main program loop
WHILE $readerr = 0
  $x = ReadLine(1)
  $readerr = @ERROR
  If $readerr <> 0
    Exit
  EndIf
  $key = SUBSTR($x, 1, INSTR($x, ",")-1)
  $x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
  $drname = SUBSTR($x, 1, LEN($x) - INSTR($x, ","))
  $objAccess.DoCmd.RunSQL("SELECT [prod2]![no] AS [Key], prod2.class, Sum(prod2.Oct_amt) AS Oct_amt, Sum(prod2.Nov_amt) AS Nov_amt, Sum(prod2.Dec_amt) AS Dec_amt, Sum(prod2.Jan_amt) AS Jan_amt, Sum(prod2.Feb_amt) AS Feb_amt, Sum(prod2.Mar_amt) AS Mar_amt, Sum(prod2.Apr_amt) AS Apr_amt, Sum(prod2.May_amt) AS May_amt, Sum(prod2.Jun_amt) AS Jun_amt, Sum(prod2.Jul_amt) AS Jul_amt, Sum(prod2.Aug_amt) AS Aug_amt, Sum(prod2.Sep_amt) AS Sep_amt, Sum(prod2.Tot_amt) AS Tot_amt INTO tempsum
    FROM prod2
    GROUP BY [prod2]![no], prod2.class
    HAVING ((([prod2]![no])=$key));")
  Sleep 1
  $objAccess.DoCmd.RunSQL("SELECT tempsum.Key, Sum(tempsum.Oct_amt) AS Oct_amt, Sum(tempsum.Nov_amt) AS Nov_amt, Sum(tempsum.Dec_amt) AS Dec_amt, Sum(tempsum.Jan_amt) AS Jan_amt, Sum(tempsum.Feb_amt) AS Feb_amt, Sum(tempsum.Mar_amt) AS Mar_amt, Sum(tempsum.Apr_amt) AS Apr_amt, Sum(tempsum.May_amt) AS May_amt, Sum(tempsum.Jun_amt) AS Jun_amt, Sum(tempsum.Jul_amt) AS Jul_amt, Sum(tempsum.Aug_amt) AS Aug_amt, Sum(tempsum.Sep_amt) AS Sep_amt, Sum(tempsum.Tot_amt) AS Tot_amt INTO temptot
    FROM tempsum
    GROUP BY tempsum.Key;")
  sleep 1
  $objAccess.DoCmd.RunSQL("SELECT tempsum.Key, classdesc.description, tempsum.Oct_amt, [tempsum]![Oct_amt]/[temptot]![Oct_amt] AS [Oct_pct], tempsum.Nov_amt,  [tempsum]![Nov_amt]/[temptot]![Nov_amt] AS [Nov_pct], tempsum.Dec_amt, [tempsum]![Dec_amt]/[temptot]![Dec_amt] AS [Dec_pct], tempsum.Jan_amt, [tempsum]![Jan_amt]/[temptot]![Jan_amt] AS  [Jan_pct], tempsum.Feb_amt, [tempsum]![Feb_amt]/[temptot]![Feb_amt] AS [Feb_pct], tempsum.Mar_amt, [tempsum]![Mar_amt]/[temptot]![Mar_amt] AS [Mar_pct], tempsum.Apr_amt,  [tempsum]![Apr_amt]/[temptot]![Apr_amt] AS [Apr_pct], tempsum.May_amt, [tempsum]![May_amt]/[temptot]![May_amt] AS [May_pct], tempsum.Jun_amt, [tempsum]![Jun_amt]/[temptot]![Jun_amt] AS  [Jun_pct], tempsum.Jul_amt, [tempsum]![Jul_amt]/[temptot]![Jul_amt] AS [Jul_pct], tempsum.Aug_amt, [tempsum]![Aug_amt]/[temptot]![Aug_amt] AS [Aug_pct], tempsum.Sep_amt,  [tempsum]![Sep_amt]/[temptot]![Sep_amt] AS [Sep_pct], tempsum.Tot_amt, [tempsum]![Tot_amt]/[temptot]![Tot_amt] AS [Tot_pct] INTO namepct
    FROM temptot, tempsum INNER JOIN classdesc ON tempsum.class = classdesc.class;")
  sleep 1
  $objAccess.DoCmd.RunMacro("exportnamepct")
  sleep 1

  ;=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-
  ; open excel file created by Access macro above
  ;  format and save with correct name

  $rc = $wbs.Open("\\server\share\folder\temp.xls")

  $objExcel.Range("d1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("f1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("h1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("j1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("l1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("n1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("p1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("r1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("t1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("v1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("x1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("z1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("ab1").Entirecolumn.NumberFormat = "0.0%"
  $objExcel.Range("c1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("e1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("g1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("i1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("k1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("m1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("o1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("q1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("s1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("u1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("w1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("y1").Entirecolumn.NumberFormat = "#,##0"
  $objExcel.Range("aa1").Entirecolumn.NumberFormat = "#,##0"

  $objExcel.Range("c24").Formula = "=SUM(c1:c23)"
  $objExcel.Range("e24").Formula = "=SUM(e1:e23)"
  $objExcel.Range("g24").Formula = "=SUM(g1:g23)"
  $objExcel.Range("i24").Formula = "=SUM(i1:i23)"
  $objExcel.Range("k24").Formula = "=SUM(k1:k23)"
  $objExcel.Range("m24").Formula = "=SUM(m1:m23)"
  $objExcel.Range("o24").Formula = "=SUM(o1:o23)"
  $objExcel.Range("q24").Formula = "=SUM(q1:q23)"
  $objExcel.Range("s24").Formula = "=SUM(s1:s23)"
  $objExcel.Range("u24").Formula = "=SUM(u1:u23)"
  $objExcel.Range("w24").Formula = "=SUM(w1:w23)"
  $objExcel.Range("y24").Formula = "=SUM(y1:y23)"
  $objExcel.Range("aa24").Formula = "=SUM(aa1:aa23)"

  $objExcel.Range("A1:AB1").insert
  $objExcel.Range("A1:AB1").merge
  $array = "Fiscal Year 2011"
  $objExcel.Range("A1:AB2").HorizontalAlignment = 3 
  $objExcel.Range("A1:AB1").Value = $array
  $objExcel.Range("A1:AB2").Font.Bold = 1
  $objExcel.Range("A1:AB2").Borders.LineStyle = 1
  $objExcel.Range("A1:AB2").Borders.Weight = 1
  $objExcel.Range("A1:AB1").EntireColumn.AutoFit
  ;$objExcel.Range("A1:AB2").Interior.ColorIndex = 15

  $filename = "\\server\share\folder\folder2\$name-$key-$year-$month.xls"
  $objExcel.ActiveWorkbook.SaveAs("$filename",-4143) 
LOOP

Del "\\server\share\folder\temp.xls"

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

;Destroy the Worksheet, Workbooks, and Application objects

$wbs.Close
$wbs = 0
$objExcel.Quit
$objExcel = ""

$objAccess.Quit
$objAccess = ""

sleep 1

Top
#211759 - 2016-07-06 06:11 PM Re: If/Else statement [Re: Arend_]
Karon Offline
Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas
I think I have it now. The only difference is I put the delete command inside the loop, so the script doesn't have to overwrite the temp file. I also left the variable names that I have.
 Code:
$setup = "\\server\share\folder\names.txt"

$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)

;open txt file with names 
IF Open(1, "$setup") <> 0
BEEP
? "spec file not opened, error code: [" + @ERROR + "]"
sleep 5
EXIT
ENDIF

Dim $accessApp
$accessApp = CreateObject("Access.Application")
$accessApp.DisplayAlerts=0
;$accessApp.visible = 1
$accessApp.OpenCurrentDatabase("\\server\share\folder\file.mdb")
$accessApp.DoCmd.RunMacro("exportnames")


$app = CreateObject("Excel.Application")
$app.DisplayAlerts = 0 
;$app.visible = 1

;Create the Workbooks object
$wbs = $app.Workbooks


sleep 10

;main program loop

WHILE $readerr = 0
$x = ReadLine(1)
$readerr = @ERROR
IF $readerr <> 0
EXIT
ENDIF
$key = SUBSTR($x, 1, INSTR($x, ",")-1)
$x = SUBSTR($x, INSTR($x, ",")+1, LEN($x) - INSTR($x, ","))
$name = SUBSTR($x, 1, LEN($x) - INSTR($x, ","))

$accessApp.DoCmd.RunSQL("SELECT [prod2]![no] AS [Key], prod2.class, Sum(prod2.Oct_amt) AS Oct_amt, Sum(prod2.Nov_amt) AS Nov_amt, Sum(prod2.Dec_amt) AS Dec_amt, Sum(prod2.Jan_amt) AS Jan_amt, Sum(prod2.Feb_amt) AS Feb_amt, Sum(prod2.Mar_amt) AS Mar_amt, Sum(prod2.Apr_amt) AS Apr_amt, Sum(prod2.May_amt) AS May_amt, Sum(prod2.Jun_amt) AS Jun_amt, Sum(prod2.Jul_amt) AS Jul_amt, Sum(prod2.Aug_amt) AS Aug_amt, Sum(prod2.Sep_amt) AS Sep_amt, Sum(prod2.Tot_amt) AS Tot_amt INTO tempsum
FROM prod2
GROUP BY [prod2]![no], prod2.class
HAVING ((([prod2]![no])=$key));")

Sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, Sum(tempsum.Oct_amt) AS Oct_amt, Sum(tempsum.Nov_amt) AS Nov_amt, Sum(tempsum.Dec_amt) AS Dec_amt, Sum(tempsum.Jan_amt) AS Jan_amt, Sum(tempsum.Feb_amt) AS Feb_amt, Sum(tempsum.Mar_amt) AS Mar_amt, Sum(tempsum.Apr_amt) AS Apr_amt, Sum(tempsum.May_amt) AS May_amt, Sum(tempsum.Jun_amt) AS Jun_amt, Sum(tempsum.Jul_amt) AS Jul_amt, Sum(tempsum.Aug_amt) AS Aug_amt, Sum(tempsum.Sep_amt) AS Sep_amt, Sum(tempsum.Tot_amt) AS Tot_amt INTO temptot
FROM tempsum
GROUP BY tempsum.Key;")

sleep 1

$accessApp.DoCmd.RunSQL("SELECT tempsum.Key, classdesc.description, tempsum.Oct_amt, [tempsum]![Oct_amt]/[temptot]![Oct_amt] AS [Oct_pct], tempsum.Nov_amt,  [tempsum]![Nov_amt]/[temptot]![Nov_amt] AS [Nov_pct], tempsum.Dec_amt, [tempsum]![Dec_amt]/[temptot]![Dec_amt] AS [Dec_pct], tempsum.Jan_amt, [tempsum]![Jan_amt]/[temptot]![Jan_amt] AS  [Jan_pct], tempsum.Feb_amt, [tempsum]![Feb_amt]/[temptot]![Feb_amt] AS [Feb_pct], tempsum.Mar_amt, [tempsum]![Mar_amt]/[temptot]![Mar_amt] AS [Mar_pct], tempsum.Apr_amt,  [tempsum]![Apr_amt]/[temptot]![Apr_amt] AS [Apr_pct], tempsum.May_amt, [tempsum]![May_amt]/[temptot]![May_amt] AS [May_pct], tempsum.Jun_amt, [tempsum]![Jun_amt]/[temptot]![Jun_amt] AS  [Jun_pct], tempsum.Jul_amt, [tempsum]![Jul_amt]/[temptot]![Jul_amt] AS [Jul_pct], tempsum.Aug_amt, [tempsum]![Aug_amt]/[temptot]![Aug_amt] AS [Aug_pct], tempsum.Sep_amt,  [tempsum]![Sep_amt]/[temptot]![Sep_amt] AS [Sep_pct], tempsum.Tot_amt, [tempsum]![Tot_amt]/[temptot]![Tot_amt] AS [Tot_pct] INTO namepct
FROM temptot, tempsum INNER JOIN classdesc ON tempsum.class = classdesc.class;")

sleep 1

$accessApp.DoCmd.RunMacro("exportnamepct")

sleep 1

;=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-=-=-=-
; open excel file created by Access macro above
;  format and save with correct name

$rc = $wbs.Open("\\server\share\folder\temp.xls")

$app.Range("d1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("f1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("h1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("j1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("l1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("n1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("p1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("r1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("t1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("v1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("x1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("z1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("ab1").Entirecolumn.NumberFormat = "0.0%"
$app.Range("c1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("e1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("g1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("i1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("k1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("m1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("o1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("q1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("s1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("u1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("w1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("y1").Entirecolumn.NumberFormat = "#,##0"
$app.Range("aa1").Entirecolumn.NumberFormat = "#,##0"

$app.Range("c24").Formula = "=SUM(c1:c23)"
$app.Range("e24").Formula = "=SUM(e1:e23)"
$app.Range("g24").Formula = "=SUM(g1:g23)"
$app.Range("i24").Formula = "=SUM(i1:i23)"
$app.Range("k24").Formula = "=SUM(k1:k23)"
$app.Range("m24").Formula = "=SUM(m1:m23)"
$app.Range("o24").Formula = "=SUM(o1:o23)"
$app.Range("q24").Formula = "=SUM(q1:q23)"
$app.Range("s24").Formula = "=SUM(s1:s23)"
$app.Range("u24").Formula = "=SUM(u1:u23)"
$app.Range("w24").Formula = "=SUM(w1:w23)"
$app.Range("y24").Formula = "=SUM(y1:y23)"
$app.Range("aa24").Formula = "=SUM(aa1:aa23)"

$app.Range("A1:AB1").insert
$app.Range("A1:AB1").merge
$array = "Fiscal Year 2011"
$app.Range("A1:AB2").HorizontalAlignment = 3 
$app.Range("A1:AB1").Value = $array
$app.Range("A1:AB2").Font.Bold = 1
$app.Range("A1:AB2").Borders.LineStyle = 1
$app.Range("A1:AB2").Borders.Weight = 1
$app.Range("A1:AB1").EntireColumn.AutoFit
;$app.Range("A1:AB2").Interior.ColorIndex = 15

$filename = "\\server\share\folder\folder2\$name-$key-$year-$month.xls"
$app.ActiveWorkbook.SaveAs("$filename",-4143) 


Del "\\server\share\folder\temp.xls"
LOOP

$wbs = 0
$app.DisplayAlerts = 0
$app.Quit
$app = 0

;IMPORTANT--YOU MUST CLOSE THE WORKBOOKS OBJECT OR ELSE
;			THE EXCEL PROCESS WILL NOT TERMINATE!!!
;Destroy the Worksheet, Workbooks, and Application objects

$wbs.Close
$wbs = 0
$app.DisplayAlerts = 0
$app.Quit
$app = 0

$accessApp.Quit
$accessApp = 0

sleep 1


Top
#211760 - 2016-07-06 10:57 PM Re: If/Else statement [Re: Karon]
ShaneEP Moderator Offline
MM club member
*****

Registered: 2002-11-29
Posts: 2125
Loc: Tulsa, OK
I know it probably doesn't affect the success of the script, but you're doing some of the app closing twice after the loop.
Top
#211761 - 2016-07-06 11:00 PM Re: If/Else statement [Re: ShaneEP]
Karon Offline
Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas
I fixed it already \:\)
 Code:

Del "\\server\share\folder\temp.xls"
LOOP

;IMPORTANT--YOU MUST CLOSE THE WORKBOOKS OBJECT OR ELSE
;			THE EXCEL PROCESS WILL NOT TERMINATE!!!
;Destroy the Worksheet, Workbooks, and Application objects

$wbs.Close
$wbs = 0
$app.Quit
$app = 0

$accessApp.Quit
$accessApp = 0



Top
#211762 - 2016-07-07 12:30 AM Re: If/Else statement [Re: Karon]
Glenn Barnas Administrator Offline
KiX Supporter
*****

Registered: 2003-01-28
Posts: 4396
Loc: New Jersey
Just FYI - we've collectively created quite a few UDFs to manipulate Excel. I've collected all of the individual functions into a single library that we've updated with consistent naming, variable use, and return methods.

You might want to take a look at the XLLib UDF library in my Kix library to see if any of that standardization would be helpful to you.

Glenn
_________________________
Actually I am a Rocket Scientist! \:D

Top
#211763 - 2016-07-07 03:14 PM Re: If/Else statement [Re: Glenn Barnas]
Karon Offline
Getting the hang of it

Registered: 2009-07-16
Posts: 87
Loc: Texas
Thanks Glenn!!
Top
Page 1 of 1 1


Moderator:  Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart 
Hop to:
Shout Box

Who's Online
1 registered (Allen) and 382 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

Generated in 0.064 seconds in which 0.024 seconds were spent on a total of 13 queries. Zlib compression enabled.

Search the board with:
superb Board Search
or try with google:
Google
Web kixtart.org