Hi,
I've wrote a simple VBscript that will write to a shared Excel spreadsheet. Im sending username, asset number, computer name and model type. I can run this on multiple machines, it will check if there is a duplicate and it will also find the next empty line to write to. This is working fine.
My problem is that I need to make sure that only one machine writes to the spreadsheet at a time. If not, I get an Excel pop up box on one of the machines telling me that it can not write, or words to that effect. I want to catch any type of error like this, pause the write for that machine and then try again until it is completed ok. You will see I am using a msgbox to control the writing of data.
If you can point me in the right direction please post. TIA.
Im just showing the code that connects to the spreadsheet, checks and then writes the data. I've left out the first part which gathers the data. If anyone wants to see it, let me know.
Im using a shared XLS saved as Office 97/2003.
I've wrote a simple VBscript that will write to a shared Excel spreadsheet. Im sending username, asset number, computer name and model type. I can run this on multiple machines, it will check if there is a duplicate and it will also find the next empty line to write to. This is working fine.
My problem is that I need to make sure that only one machine writes to the spreadsheet at a time. If not, I get an Excel pop up box on one of the machines telling me that it can not write, or words to that effect. I want to catch any type of error like this, pause the write for that machine and then try again until it is completed ok. You will see I am using a msgbox to control the writing of data.
If you can point me in the right direction please post. TIA.
Im just showing the code that connects to the spreadsheet, checks and then writes the data. I've left out the first part which gathers the data. If anyone wants to see it, let me know.
Im using a shared XLS saved as Office 97/2003.
Code:
Dim objUser, strExcelPath, objExcel, objSheet, objGroup
' Bind to Excel object and worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.open("\\<server>\<share>\Asset_Register.xls")
' Connect to the worksheet
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Populate spreadsheet cells with user attributes.
' Loop to find the next empty row, then write to it
' Exit if duplicate Asset value found
On Error Resume Next
loopCount = 1
Do until IsEmpty(objSheet.Cells(loopCount, 1).Value)
If (objSheet.Cells(loopcount, 3).Value) = strAsset Then
objExcel.Application.Quit
WScript.Quit
Else
loopCount = loopCount + 1
End If
Loop
' This is a simple pause so we can control
' when data is written to the spreadsheet
' TODO: Add an error catching routine to
' replace this pause.
Wscript.Echo "Write data to the XLS"
' Start writing the data
objSheet.Cells(loopcount, 1).Value = strUsername
objSheet.Cells(loopcount, 2).Value = strComputername
objSheet.Cells(loopcount, 3).Value = strAsset
objSheet.Cells(loopcount, 4).Value = strModel
' Save and close workbook then quit
objExcel.ActiveWorkbook.Save
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
WScript.Quit