open an excel file
 

Within powerpoint I have written a quiz for which I have a few lines of
vba code to write the results of the quiz to an excel file. As pupils
complete this, they each end up writing to the excel file, and I
therefore get an automatic analysis of which questions they found
difficult. The problem I have is that if two pupils finish at more or
less the same time, they can't both write to the excel file
simultaneously. Obviously one will start doing so, and the vba code for
the person who is second then ought to wait for the excel file to close
so it can then write to it. What I have found happening is a copy of the
excel results file gets opened up, which confuses the kids and me.
So what I need is a few lines of code in vba that if user B finds file X
is open by user A, it waits until the file is closed by user A and then
available to write to.

Can anyone help me with this??
Bernie Dawson

*** Sent via Developersdex http://www.developersdex.com ***

open an excel file
 

There are a couple of things that you need to provide:
1. Does each student have their own workstation?
2. What is the code you are using now?

On most networks, if one user is using a file and another tries to open the
file, it will send a message that the file is in use by another user and will
not let the second or subsequent user in until the file is closed. So your
system set-up is suspect.

"Bernard Dawson" wrote:


Within powerpoint I have written a quiz for which I have a few lines of
vba code to write the results of the quiz to an excel file. As pupils
complete this, they each end up writing to the excel file, and I
therefore get an automatic analysis of which questions they found
difficult. The problem I have is that if two pupils finish at more or
less the same time, they can't both write to the excel file
simultaneously. Obviously one will start doing so, and the vba code for
the person who is second then ought to wait for the excel file to close
so it can then write to it. What I have found happening is a copy of the
excel results file gets opened up, which confuses the kids and me.
So what I need is a few lines of code in vba that if user B finds file X
is open by user A, it waits until the file is closed by user A and then
available to write to.

Can anyone help me with this??
Bernie Dawson

*** Sent via Developersdex http://www.developersdex.com ***

open an excel file
 

Sub excelwrite()


Dim appExcel As Object
Dim wb As Object
Dim i As Integer
Dim j As Integer

i = 0
j = 1

Set appExcel = New Excel.Application

Set wb = appExcel.Workbooks.Open(ActivePresentation.Path +
"\y10chemistry1.xls")
'find the last empty row and add 1 to it
i = wb.Sheets("Sheet1").Range("A65536").End(xlUp).Row + 1

'first write the names to empty cell col A and B
wb.Sheets("Sheet1").Cells(i, 1) = userName1
wb.Sheets("Sheet1").Cells(i, 2) = userName2
'write answers from col C to end of array
For j = 1 To UBound(taggedAnswer)
wb.Sheets("Sheet1").Cells(i, j + 2) = taggedAnswer(j)
Next

wb.Close True

'release memory
Set wb = Nothing
Set appExcel = Nothing

End Sub




*** Sent via Developersdex http://www.developersdex.com ***