VBscript on Excel w/o opening?

[jmair]

I'm going to be logging student computer info (username, computername, location, time of day etc) to excel as part of the User Login script.

 

I've been hunting for an example of writing to an already created test.xls without actually opening it, however when I test it, it actually opens the xls to make the changes then saves/closes it. I've actually been looking for a few days for an answer before posting, perhaps my googlefoo is on the fritz. Can this even be done?

 

Here's the script I'm using now.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.DisplayAlerts = FALSE

Set objWorkbook = objExcel.Workbooks.Add
Set objWorksheet = objWorkbook.Worksheets(1)

objWorksheet.Cells(1, 1).Value = Now
objWorkbook.SaveAs("C:\Scripts\Test.xls")
objExcel.Quit

[jmair]

After further reading it looks like only one user can have access to the excel at a time anyhow. Though it's only open for a brief moment (.5 a second or so) I would rather find a diff way to record the info I need for accurate data than knowing it may not be correct.

[srochford]

You can use OLEDB to write the data to Excel but what you're doing really needs a database.@synack has just recommended SQL Express for just this purpose and I'd agree with that.

[mac_shinobi]

You can use OLEDB to write the data to Excel but what you're doing really needs a database.@synack has just recommended SQL Express for just this purpose and I'd agree with that.

 

sounds like a good idea - also with regards to the script you posted, the line here

 

objExcel.Visible = True

 

If you set this to False - is that any better ?

[srochford]

Setting the spreadsheet to not visible will speed things up a bit but that's not going to the problem if you use this for real - it's currently writing data to cell A1 which is fine for testing but no good if you use this for real because you'll only ever get one record. If you actually want to know more than the details of the last student to logon then you've got to find the next empty row and put stuff in there.

 

This web page Monitoring Computer Usage is something I wrote ages ago. It's a login and logoff script which records the username, computername, on time and off time for each logon.

 

You need to have a SQL database (SQL Express is fine) and the line:

sConn="provider=sqloledb;data source=SQLServerName;initial catalog=logoninfo;uid=username;pwd=password"

needs changing so that SQLServerName is the name of the SQL server (eg mysqlserver\sqlexpress), logoninfo is the name of the database and uid and pwd contain the username and password.

 

The logoninfo database needs a table called logonoff with the fields:

username - varchar[50]

compname - varchar[50]

ontime - datetime

offtime - datetime

and also an id field (just an integer set to be an identity)

 

You also want to set non-unique indices for the other fields - this will make it easier to find when details of a computer or user.

[apeo]

Like everyone else has said it does sound like you need a database instead of an excel file. You could also use MS Access as your database.

[LosOjos]

Excel seems an odd choice anyway; what do you need to do that can't be stored in a simple text/CSV file?

 

Having said that, as others said before me, a database would be much better

[srochford]

I'm guessing the OP wanted to use Excel because they know how to query it - for a single user, storing data in a spreadsheet is better than in a CSV or text file because you can store your filters, queries, charts etc

 

The problem is that it doesn't scale (nor does CSV or Access) and you really need a proper database to make it easy. Given that SQL Express (or MySQL) is free it makes sense to go straight to a proper database.

[jmair]

I'll be porting this data into excel to use a addon that will save the data in a a format that PivotViewer can read.

I really should have closed this post and labled it as Solved. I have some experiance with MySQL, so I will be switching to VBScript and SQL express to record the data. After gathering the info I need (should take a couple months) I want to post the results on the forums here in case anyone else finds it usefull.

Thanks for the reply folks.

[mac_shinobi]

I'll be porting this data into excel to use a addon that will save the data in a a format that PivotViewer can read.

I really should have closed this post and labled it as Solved. I have some experiance with MySQL, so I will be switching to VBScript and SQL express to record the data. After gathering the info I need (should take a couple months) I want to post the results on the forums here in case anyone else finds it usefull.

Thanks for the reply folks.

 

Yes that would be helpful if you get it working and a how to if you have time at any point

 

Ta