#43014 - 2003-07-17 03:57 PM
Problems writing to Excel
|
Sverre
Getting the hang of it
Registered: 2002-09-29
Posts: 52
|
I have the following code that writes to a hardware log from the login script. The problem is that IF the Excel file is open the user is asked if he wants to save the spreadsheet.
Anyone got any idea why?
code:
$product = @producttype + ", " + @csd + ", " + @build $Serial = WMIQuery("SerialNumber","Win32_BIOS")[0] if trim($Serial) = "" $file = "@LServer\Users\" + @wksta + ".cfg" shell "sm_info > $file" $ = open(1,$file) $line ="" while left($line ,12) <> "Service tag:" $line = readline(1) loop $Serial = right($line,len($line)-13) endif $Manufacturer = WMIQuery("Manufacturer","Win32_ComputerSystem")[0] if $Manufacturer = "Dell Computer Corporation" $Manufacturer = "Dell" endif $Model = WMIQuery("Model","Win32_ComputerSystem")[0] $BiosVer = WMIQuery("SMBIOSBIOSVersion","Win32_BIOS")[0] $Memory = WMIQuery("TotalPhysicalMemory","Win32_LogicalMemoryConfiguration")[0] $xlFile = "@LServer\Users\machines.xls" $xl = createobject("excel.application") if not @error $ = $xl.Workbooks.Open($xlFile) $Row = 1 $CellValue = $Xl.Cells($Row,1).Value While $CellValue <> @wksta And $CellValue <> '' $Row = $Row + 1 $CellValue = $Xl.Cells($Row,1).Value Loop If $CellValue = @wksta $SearcStartRow = $Row + 1 EndIf $xl.Range("A"+$row).Value = @wksta $xl.Range("B"+$row).Value = $product $xl.Range("C"+$row).Value = trim(@cpu) $xl.Range("D"+$row).Value = $Serial $xl.Range("E"+$row).Value = $Manufacturer $xl.Range("F"+$row).Value = $Model $xl.Range("G"+$row).Value = $BiosVer $xl.Range("H"+$row).Value = $Memory $xl.Range("I"+$row).Value = @fullname $xl.Range("J"+$row).Value = @date $xl.Range("K"+$row).Value = @time $ = $xl.ActiveWorkbook.Save $xl.UserControl = 1 $bye = $xl.quit else exit 0 endif Color w+/n exit
FUNCTION WMIQuery($sWhat, $sFrom, Optional $sComputer, Optional $sWhere, Optional $x) Dim $sQuery, $objEnum, $sValue, $sItem, $lUbound Dim $aTMP[0] $sQuery = "Select " + $sWhat + " From "+ $sFrom If Not $sComputer $sComputer=@WKSTA EndIf If $sWhere AND $x $sQuery = $sQuery + " Where " + $sWhere + " = '"+$x+"'" EndIf $SystemSet = GetObject("winmgmts:{impersonationLevel=impersonate}!//"+$sComputer) If @ERROR Exit VAL("&"+Right(DecToHex(@ERROR),4)) Return EndIf $objEnum = $SystemSet.ExecQuery($sQuery) If @ERROR Exit VAL("&"+Right(DecToHex(@ERROR),4)) Return EndIf For Each $objInstance in $objEnum If $objInstance $=Execute("$$sValue = $$objInstance.$sWhat") Select Case VarType($sValue) & 8192 For Each $sItem in $sValue $lUbound = Ubound($aTMP) If $aTMP[$lUbound] >' ' $lUbound = $lUbound +1 Redim Preserve $aTMP[$lUbound] EndIf $aTMP[$lUbound] = Trim($sItem) Next Case 1 $lUbound = Ubound($aTMP) If $aTMP[$lUbound] >' ' $lUbound = $lUbound +1 Redim Preserve $aTMP[$lUbound] EndIf $aTMP[$lUbound] = Trim($sValue) EndSelect EndIf Next $WMIQuery = $aTMP Exit VAL("&"+Right(DecToHex(@ERROR),4)) ENDFUNCTION
|
Top
|
|
|
|
#43016 - 2003-07-17 04:11 PM
Re: Problems writing to Excel
|
Sverre
Getting the hang of it
Registered: 2002-09-29
Posts: 52
|
With the exception mentioned above, the script works wery well and I have no need to get the result from the user every time they log on, to get an update from each user every tenth time they log in is more than good enough.
So, how can I prevent the users from being asked if they want to save the file if the file is already open?
|
Top
|
|
|
|
#43019 - 2003-07-17 04:22 PM
Re: Problems writing to Excel
|
Sverre
Getting the hang of it
Registered: 2002-09-29
Posts: 52
|
But isn't it possible to check if the file is open and if so just skip the registring?
|
Top
|
|
|
|
#43020 - 2003-07-17 04:26 PM
Re: Problems writing to Excel
|
Sverre
Getting the hang of it
Registered: 2002-09-29
Posts: 52
|
Thks Shawn, that did the trick.
|
Top
|
|
|
|
#43028 - 2003-07-17 07:43 PM
Re: Problems writing to Excel
|
Jochen
KiX Supporter
Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
|
Yep,
there are loads of parameters to pass :
quote: expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)
All except Filename are optional
Hmmm :
quote: Notify Optional Variant. If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.
_________________________
|
Top
|
|
|
|
#43029 - 2003-07-18 11:50 AM
Re: Problems writing to Excel
|
Sverre
Getting the hang of it
Registered: 2002-09-29
Posts: 52
|
Ok a few clarifications.
The Excel file is meant as a hardware log and nothing more.
I don't have a code that only opens it every 10th login, what I meant is: I don't really care if the user is able to log the data or not as long as they are able to do it every 10th login (in fact I only need it every time the PC is updated in some serious way).
I have 100 users (+/-) and normally they log in over a periode of 2 to 3 hours (as well as about 10 users in Asia who logs in way earlier). The time it takes for the script to open the file, write the data, save it and close it is so short that there really shouldn't be a problem.
I will at a later time rewrite the script to write directly to a MS SQL database, but I don't have the time to dig up all the details right now. If anyone could tell me exactly what to do to acomplish this I would be very greatful, since it's years since I worked with databases last time and I have never been any good with SQL anyway.
|
Top
|
|
|
|
Moderator: Jochen, Allen, Radimus, Glenn Barnas, ShaneEP, Ruud van Velsen, Arend_, Mart
|
1 registered
(Allen)
and 466 anonymous users online.
|
|
|