Coding Thread, VBscript on Excel w/o opening? in Coding and Web Development; I'm going to be logging student computer info (username, computername, location, time of day etc) to excel as part of ...
1st December 2010, 02:48 AM #1
- Rep Power
VBscript on Excel w/o opening?
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
1st December 2010, 05:08 AM #2
- Rep Power
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.
1st December 2010, 09:06 AM #3
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.
1st December 2010, 09:20 AM #4
sounds like a good idea - also with regards to the script you posted, the line here
Originally Posted by srochford
If you set this to False - is that any better ?
1st December 2010, 10:28 AM #5
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:
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.
sConn="provider=sqloledb;data source=SQLServerName;initial catalog=logoninfo;uid=username;pwd=password"
The logoninfo database needs a table called logonoff with the fields:
username - varchar
compname - varchar
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.
1st December 2010, 10:49 AM #6
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.
1st December 2010, 11:47 AM #7
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
1st December 2010, 01:17 PM #8
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.
2nd December 2010, 04:00 PM #9
- Rep Power
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.
Thanks to jmair from:
mac_shinobi (2nd December 2010)
2nd December 2010, 04:33 PM #10
Yes that would be helpful if you get it working and a how to if you have time at any point
Originally Posted by jmair
By Teaser in forum Scripts
Last Post: 5th August 2010, 11:04 AM
By DaveP in forum Downloads
Last Post: 22nd December 2009, 06:05 PM
By imiddleton25 in forum Virtual Learning Platforms
Last Post: 27th October 2009, 02:19 AM
By imiddleton25 in forum Office Software
Last Post: 22nd September 2008, 11:59 PM
By PEO in forum How do you do....it?
Last Post: 7th March 2008, 10:53 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)