Adding To Already Made Excel Sheeet

[Guest Mark Connelly]

Can you please tell me exactly what I need to change in the following VB script so that information from each call automatically gets put into an Excel sheet (which has already been set up with column headings) without overwriting already existing data (i.e., new data gets appended to the next row each time a call comes in)? Here is the VB script based on your information about storing data to Excel. Thanks!:

 

Dim xlApp, xlBook, xlSht

Dim filename, value1, value2, value3, value4

 

on error resume next

 

filename = "c:\cancerstudy.xls"

 

Set xlApp = CreateObject("Excel.Application")

set xlBook = xlApp.WorkBooks.Open(filename)

set xlSht = xlApp.activesheet

 

xlApp.DisplayAlerts = False

 

'write data into the spreadsheet

xlSht.Cells(2, 2) = "New Data"

 

xlBook.Save

xlBook.Close SaveChanges=True

xlApp.Close

xlApp.Quit

 

'always deallocate after use...

set xlSht = Nothing

Set xlBook = Nothing

Set xlApp = Nothing

[SupportTeam]

Basically what you may want to have is a cell which stores in it the 'next row' value.

Then you'd first read that value and then use that read value to insert data.

You'd also need to update the value of the value stored in the 'next row' cell to point to the next row.

 

If you do not know how to implement this I'd recommend finding a local programmer to set all this up for you.

 

You should really use a database to store data, or at worst append a row of data to a text file.

Storing data like this in Excel is open to problems - eg: what happens when two or more lines want to update the Excel spreadsheet at the same time?