|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
You're also opening the access object only once, but then trying to close it multiple times in the loop. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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. |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
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 |
||||||||
|
|
|||||||
Thanks Glenn!! |