Industry Online Support
Technical Forum
10/9/2012 10:26 AM | |
Joined: 10/3/2010 Last visit: 5/23/2024 Posts: 1552 Rating: (188) |
try changing the part of your script above like this: objExcelApp.Visible = True objExcelApp.ScreenUpdating = True objExcelApp.DisplayAlerts = True With objWorkbook.ActiveSheet .cells(4,2).value = SmartTags("iTemperature") .cells(4,3).value = SmartTags("iPressure") .cells(4,4).value = SmartTags("szMaterial") End With objExcelApp.DisplayAlerts = False objWorkbook.Save objWorkbook.Close "objExcelApp.DisplayAlerts = False" should stop those confirmation popups and take the default action (Yes) instead. Brgds. |
B r g d s , |
|
10/10/2012 8:53 AM | |
Joined: 10/3/2010 Last visit: 5/23/2024 Posts: 1552 Rating: (188) |
That behaviour is most likely related to creating your file as textfile before opening in Excel. I adjust your script like this: '****************************************************************************** Dim wsh, XLSrunning, TargetBookrunning, objExcel, objWorkbook, TheTargetBook, TheTargetBookName Dim TheCount, fso Dim d, m, y, dte, tme Dim h, mn, s Const xlExcel8 = 56 d=Day(Now) m=Month(Now) y=Year(Now) h=Hour(Now) mn=Minute(Now) s=Second(Now) dte=d & "." & m & "." & y tme=h & "." & mn & "." & s Set wsh = CreateObject("WScript.Shell") TheTargetBookName = "report "& dte &"_"& tme &".xls" TheTargetBook = "c:\test\" & TheTargetBookName 'Error Routine On Error Resume Next 'Create Object Set fso = CreateObject("Scripting.FileSystemObject") If Err.Number <> 0 Then ShowSystemAlarm "Error #" & CStr(Err.Number) & " " & Err.Description Err.Clear Exit Sub End If TheCount = GetObject("winmgmts:root\CIMV2").ExecQuery("SELECT * FROM Win32_Process WHERE Name='EXCEL.EXE'").Count If TheCount > 0 Then Set objExcel = GetObject(,"Excel.Application") ' Using GetObject(,"Excel.Application") to point to the running Excel Application. Else Set objExcel = CreateObject("Excel.Application") End If ' Create a new workbook. objExcel.Workbooks.Add objExcel.Visible = True objExcel.ScreenUpdating = True objExcel.DisplayAlerts = True ' Bind To worksheet. Set objWorkbook = objExcel.ActiveWorkbook.Worksheets(1) objWorkbook.cells(4,2).value = SmartTags("iTemperature") objWorkbook.cells(4,3).value = SmartTags("iPressure") objWorkbook.cells(4,4).value = SmartTags("szMaterial") objExcel.ActiveWorkbook.SaveAs TheTargetBook, xlExcel8 objExcel.ActiveWorkbook.Close Set objWorkbook = Nothing 'objExcel.Quit Set objExcel = Nothing Set wsh = Nothing '****************************************************************************** Main difference is that you don't create your report as text before opening in Excel. This way, you check if Excel is running or not. Afterwards you create a new Workbook with Worksheet and write your values in the respective cells. At the end, you do "SaveAs" with defining the format of the Excel-file. You'll see that this method works fine. Hope this helps. Brgds. |
B r g d s , |
|
10/10/2012 7:56 PM | |
Posts: 68 Rating: (0) |
Hi SteBo, Thank for helping. It's working finr now. Thanks a lot
|
Follow us on