#211751 - 2016-07-05 10:12 PM
If/Else statement
|
Karon
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.
$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:
;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_
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.
$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
$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
|
|
|
|
#211755 - 2016-07-06 03:52 PM
Re: If/Else statement
[Re: ShaneEP]
|
Arend_
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:
$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 only works if you set it just after creating the object, not before you close the object.
|
Top
|
|
|
|
#211757 - 2016-07-06 05:43 PM
Re: If/Else statement
[Re: Arend_]
|
Karon
Getting the hang of it
Registered: 2009-07-16
Posts: 87
Loc: Texas
|
Ok - these are the changes I have made -
$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_
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:
$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
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.
$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
|
|
|
|
#211761 - 2016-07-06 11:00 PM
Re: If/Else statement
[Re: ShaneEP]
|
Karon
Getting the hang of it
Registered: 2009-07-16
Posts: 87
Loc: Texas
|
I fixed it already
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
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
0 registered
and 515 anonymous users online.
|
|
|