Page 1 of 1 1
Topic Options
#43014 - 2003-07-17 03:57 PM Problems writing to Excel
Sverre Offline
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
#43015 - 2003-07-17 04:01 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
As I understand you accomplish this task at login ?

Hmmm ... you should either consider using a database or go this way
_________________________



Top
#43016 - 2003-07-17 04:11 PM Re: Problems writing to Excel
Sverre Offline
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
#43017 - 2003-07-17 04:13 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
There is no way to prevent this !

Excel is not capable of multiple opens of the same file ... therefore :

Database or INI is the way
_________________________



Top
#43018 - 2003-07-17 04:21 PM Re: Problems writing to Excel
Shawn Administrator Offline
Administrator
*****

Registered: 1999-08-13
Posts: 8611
Can't seem to really repro this, but might try setting this flag before your save:

$xl.DisplayAlerts = 0
$ = $xl.ActiveWorkbook.Save

-Shawn

Top
#43019 - 2003-07-17 04:22 PM Re: Problems writing to Excel
Sverre Offline
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 Offline
Getting the hang of it

Registered: 2002-09-29
Posts: 52
Thks Shawn, that did the trick.
Top
#43021 - 2003-07-17 04:36 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
hmmm ... ok. This prevents the message, true,
but still I would not go the excel file way at logon in fear of unforeseen consequences [Smile]

Call it personal preference, decision is up to you.

Two more questions if you don't mind.

How many users are around in your environment ? and
How do you accomplish the log every 10th login as I cannot find any code to do so ?
_________________________



Top
#43022 - 2003-07-17 04:41 PM Re: Problems writing to Excel
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Also, how do you assure that only one person at a time has that Excel file open?
_________________________
There are two types of vessels, submarines and targets.

Top
#43023 - 2003-07-17 04:54 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
If i am not on the completely wrong track the way the file is opened, it does force it anyway for write access !

Therefore the other users got these messages to save !

As said, I have my concerns doing this [Roll Eyes]
_________________________



Top
#43024 - 2003-07-17 04:57 PM Re: Problems writing to Excel
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Actually, a second user will get a message that the file can only be opened for read-only as the file is already write-locked at that point.
_________________________
There are two types of vessels, submarines and targets.

Top
#43025 - 2003-07-17 04:59 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
not sure doing only

$ = $xl.Workbooks.Open($xlFile)

have you verified that ?
_________________________



Top
#43026 - 2003-07-17 05:01 PM Re: Problems writing to Excel
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
It definitley happens if I open an in-use Excel file interactively.
_________________________
There are two types of vessels, submarines and targets.

Top
#43027 - 2003-07-17 07:33 PM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Sure, same happens for me too ...

But I never saw that by open it this way by Excel.Application [Smile]

Hmmm ... needs a dig me thinx, one sec. please.
_________________________



Top
#43028 - 2003-07-17 07:43 PM Re: Problems writing to Excel
Jochen Administrator Offline
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 [Smile]

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 Offline
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. [Smile]

Top
#43030 - 2003-07-18 11:54 AM Re: Problems writing to Excel
Jochen Administrator Offline
KiX Supporter
*****

Registered: 2000-03-17
Posts: 6380
Loc: Stuttgart, Germany
Do that as soon as possible [Wink]

Oh yeah, there are some excellent and reliable database udf's available in UDF library forum here
_________________________



Top
#43031 - 2003-07-18 02:56 PM Re: Problems writing to Excel
Sealeopard Offline
KiX Master
*****

Registered: 2001-04-25
Posts: 11164
Loc: Boston, MA, USA
Writing to a SQL/Access DB is very straight-forward, actually it's plug-and-play. Plug the DBCommand() UDF into your script and play DB Guru. Flowchart:
code:
Collect hardware data
SELECT computer FROM database
IF computer
UPDATE database SET computer hardware data
ELSE
INSERT INTO database VALUES(computer hardware data)
ENDIF



[ 18. July 2003, 15:14: Message edited by: sealeopard ]
_________________________
There are two types of vessels, submarines and targets.

Top
#43032 - 2003-07-19 06:36 PM Re: Problems writing to Excel
ChristopheM Offline
Hey THIS is FUN
*****

Registered: 2002-05-13
Posts: 309
Loc: STRASBOURG, France
I do something very similar in my login script but as I have more than 2800 computers,
to be sure that 2 users are not connecting at the same time, I write the results in a
.csv file named with computer name on a centralized share.

When I want to see the information, i have made a .kix script that concatenate all the .csv files in a single .xls file
thus, no conflict possible...
_________________________
Christophe

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 466 anonymous users online.
Newest Members
gespanntleuchten, DaveatAdvanced, Paulo_Alves, UsTaaa, xxJJxx
17864 Registered Users

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

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