+ Post New Thread
Results 1 to 10 of 10
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 ...
  1. #1
    jmair's Avatar
    Join Date
    Aug 2007
    Posts
    268
    Thank Post
    57
    Thanked 8 Times in 8 Posts
    Rep Power
    15

    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.
    Code:
    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

  2. #2
    jmair's Avatar
    Join Date
    Aug 2007
    Posts
    268
    Thank Post
    57
    Thanked 8 Times in 8 Posts
    Rep Power
    15
    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.

  3. #3

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,144
    Thank Post
    113
    Thanked 518 Times in 447 Posts
    Blog Entries
    2
    Rep Power
    121
    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.

  4. #4

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,214
    Thank Post
    2,766
    Thanked 935 Times in 875 Posts
    Rep Power
    343
    Quote Originally Posted by srochford View Post
    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 ?

  5. #5

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,144
    Thank Post
    113
    Thanked 518 Times in 447 Posts
    Blog Entries
    2
    Rep Power
    121
    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:
    Code:
    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.

  6. #6
    apeo's Avatar
    Join Date
    Sep 2005
    Location
    Lost
    Posts
    1,612
    Thank Post
    95
    Thanked 115 Times in 111 Posts
    Rep Power
    41
    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.

  7. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,182
    Thank Post
    1,285
    Thanked 1,029 Times in 729 Posts
    Rep Power
    658
    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

  8. #8

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,144
    Thank Post
    113
    Thanked 518 Times in 447 Posts
    Blog Entries
    2
    Rep Power
    121
    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.

  9. #9
    jmair's Avatar
    Join Date
    Aug 2007
    Posts
    268
    Thank Post
    57
    Thanked 8 Times in 8 Posts
    Rep Power
    15
    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.

  10. Thanks to jmair from:

    mac_shinobi (2nd December 2010)

  11. #10

    mac_shinobi's Avatar
    Join Date
    Aug 2005
    Posts
    9,214
    Thank Post
    2,766
    Thanked 935 Times in 875 Posts
    Rep Power
    343
    Quote Originally Posted by jmair View Post
    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

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 8
    Last Post: 5th August 2010, 10:04 AM
  2. Replies: 1
    Last Post: 22nd December 2009, 05:05 PM
  3. Opening Excel 2007 in Sharepoint
    By imiddleton25 in forum Virtual Learning Platforms
    Replies: 10
    Last Post: 27th October 2009, 01:19 AM
  4. [MS Office - 2007] Strange behavior opening excel 2007
    By imiddleton25 in forum Office Software
    Replies: 3
    Last Post: 22nd September 2008, 10:59 PM
  5. problems opening excel document
    By PEO in forum How do you do....it?
    Replies: 6
    Last Post: 7th March 2008, 09:53 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •