Opening and Closing workbooks in code
 

Hello.

I Have a workbook - "Data" - that will be accessed on a network drive. Say
set up as "T" for every user within a folder "Customer".

I need the code the open the workbook. testing for read/write access. only
proceding if full access granted.

Take some action on the data. For example use end(xlup) to get the bottom
row and then add a no. of entries from a userform. ( I'm reasonably confident
to do this bit.)

Save and Close the file with update.

Could someone give me some input on the opening of the file part say in loop
that only procedes when access is possible.

Regards Francis.

Opening and Closing workbooks in code
 

One way would be something like this. Open the file test if it is
readonly, if so close without saving changes, if not readonly make
changes and then save and close:

Sub test()
Dim theBk As Workbook
Set theBk = Workbooks.Open("C:\Temp\File5.xls")
If theBk.ReadOnly Then
theBk.Close False
Else
'make your changes
theBk.Close True
End If
End Sub


Hope this helps
Rowan

Francis Brown wrote:
Hello.

I Have a workbook - "Data" - that will be accessed on a network drive. Say
set up as "T" for every user within a folder "Customer".

I need the code the open the workbook. testing for read/write access. only
proceding if full access granted.

Take some action on the data. For example use end(xlup) to get the bottom
row and then add a no. of entries from a userform. ( I'm reasonably confident
to do this bit.)

Save and Close the file with update.

Could someone give me some input on the opening of the file part say in loop
that only procedes when access is possible.

Regards Francis.

Opening and Closing workbooks in code
 

Hello.

Thanks for answer. It worked with slight alteration. Thanks for getting me
on right track.

heres my code :

Private Sub CommandButton1_Click()

Do Until issave = True
Dim theBk As Workbook
Set theBk = Workbooks.Open("Z:\Systemdown\Declaration.xls")
If theBk.ReadOnly Then
theBk.Close False
issave = False
Else
newrow =
Workbooks("Declaration.xls").Sheets("data").Range( "A65536").End(xlUp).Row + 1
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 1).Value
= TextBox1.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 2).Value
= TextBox2.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 3).Value
= TextBox3.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 4).Value
= TextBox4.Value

theBk.Close True
MsgBox "Records Saved"
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
issave = True

End If
Loop

End Sub
--
Regards
Francis Brown.


"Rowan Drummond" wrote:

One way would be something like this. Open the file test if it is
readonly, if so close without saving changes, if not readonly make
changes and then save and close:

Sub test()
Dim theBk As Workbook
Set theBk = Workbooks.Open("C:\Temp\File5.xls")
If theBk.ReadOnly Then
theBk.Close False
Else
'make your changes
theBk.Close True
End If
End Sub


Hope this helps
Rowan

Francis Brown wrote:
Hello.

I Have a workbook - "Data" - that will be accessed on a network drive. Say
set up as "T" for every user within a folder "Customer".

I need the code the open the workbook. testing for read/write access. only
proceding if full access granted.

Take some action on the data. For example use end(xlup) to get the bottom
row and then add a no. of entries from a userform. ( I'm reasonably confident
to do this bit.)

Save and Close the file with update.

Could someone give me some input on the opening of the file part say in loop
that only procedes when access is possible.

Regards Francis.

Opening and Closing workbooks in code
 

Hi Francis

Happy to help.

Without actually testing your code it looks like you could get into a
very long loop eg if somebody else has the file open for an hour (or all
day!!) this looks like it will continue to open and close the file as
read only until the other user closes it?

Regards
Rowan

Francis Brown wrote:
Hello.

Thanks for answer. It worked with slight alteration. Thanks for getting me
on right track.

heres my code :

Private Sub CommandButton1_Click()

Do Until issave = True
Dim theBk As Workbook
Set theBk = Workbooks.Open("Z:\Systemdown\Declaration.xls")
If theBk.ReadOnly Then
theBk.Close False
issave = False
Else
newrow =
Workbooks("Declaration.xls").Sheets("data").Range( "A65536").End(xlUp).Row + 1
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 1).Value
= TextBox1.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 2).Value
= TextBox2.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 3).Value
= TextBox3.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 4).Value
= TextBox4.Value

theBk.Close True
MsgBox "Records Saved"
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
issave = True

End If
Loop

End Sub

Opening and Closing workbooks in code
 

The only people who will get the password to the workbooks for the data will
be admin. All other people will use the excel userforms. I will lock the code
and add passwords for the databook.

I know I need to add the password part to the code.

That would only leave if the code breaks to cause the problem you described.
--
Regards and Thanks for any assistance.

Francis Brown.


"Rowan Drummond" wrote:

Hi Francis

Happy to help.

Without actually testing your code it looks like you could get into a
very long loop eg if somebody else has the file open for an hour (or all
day!!) this looks like it will continue to open and close the file as
read only until the other user closes it?

Regards
Rowan

Francis Brown wrote:
Hello.

Thanks for answer. It worked with slight alteration. Thanks for getting me
on right track.

heres my code :

Private Sub CommandButton1_Click()

Do Until issave = True
Dim theBk As Workbook
Set theBk = Workbooks.Open("Z:\Systemdown\Declaration.xls")
If theBk.ReadOnly Then
theBk.Close False
issave = False
Else
newrow =
Workbooks("Declaration.xls").Sheets("data").Range( "A65536").End(xlUp).Row + 1
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 1).Value
= TextBox1.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 2).Value
= TextBox2.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 3).Value
= TextBox3.Value
Workbooks("Declaration.xls").Sheets("data").Cells( newrow, 4).Value
= TextBox4.Value

theBk.Close True
MsgBox "Records Saved"
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
issave = True

End If
Loop

End Sub