Macro stop if file in use by other user?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi im running a macro in workbook 1 that opens workbook 2, makes changes,
saves and exits.

trouble is workbook 2 is on a shred network drive and sometimes in use by
others, what i have found is that when the macro is run and wirkbook2 is
already open by another user then it apears to run fine but no changes are
saved as the macro has entered via read only.

Is there any way of me getting the macro to stop running if the file is
already in use? preferably displaying the name of the user thats using it but
this is less important.

ive attached the code of the macro below.

workbook1 = Query Logger.xls
workbook2 = QRYLOGMK2.xls

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 07/08/2006 by Bry
'

'
Sheets("Do not touch").Visible = True
Sheets("Do not touch2").Visible = True
Sheets("Do not touch").Select
Range("F15:F16").Select
Range("F16").Activate
Sheets("Query Logger").Select
ChDir "S:\bry\QRYLOGMK2"
Workbooks.Open Filename:="S:\bry\QRYLOGMK2\QRYLOGMK2.xls"
ActiveWindow.LargeScroll ToRight:=-8
Range("A2").Select
Selection.Copy
Windows("INPUT FORM.xls").Activate
Sheets("Do not touch").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("QRYLOGMK2.xls").Activate
Range("A2:BR2").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Range("B2:BR2").Select
Range("BR2").Activate
Selection.Interior.ColorIndex = 2
Range("BR3").Select
Selection.Copy
Range("BR2").Select
ActiveSheet.Paste
Range("BQ2").Select
Windows("INPUT FORM.xls").Activate
Range("A13:BQ13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("QRYLOGMK2.xls").Activate
Range("A2:BQ2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Notes").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Range("A2").Select
Selection.Copy
Sheets("Notes").Select
Range("A1").Select
ActiveSheet.Paste
Selection.Interior.ColorIndex = 2
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Do not touch2").Select
End Sub
 
U¿ytkownik "Bry said:
Hi im running a macro in workbook 1 that opens workbook 2, makes changes,
saves and exits.

trouble is workbook 2 is on a shred network drive and sometimes in use by
others, what i have found is that when the macro is run and wirkbook2 is
already open by another user then it apears to run fine but no changes are
saved as the macro has entered via read only.

Is there any way of me getting the macro to stop running if the file is
already in use? preferably displaying the name of the user thats using it but
this is less important.

ive attached the code of the macro below.

workbook1 = Query Logger.xls
workbook2 = QRYLOGMK2.xls

Sub transfer1()
'
' transfer1 Macro
' Macro recorded 07/08/2006 by Bry
'

'
Sheets("Do not touch").Visible = True
Sheets("Do not touch2").Visible = True
Sheets("Do not touch").Select
Range("F15:F16").Select
Range("F16").Activate
Sheets("Query Logger").Select
ChDir "S:\bry\QRYLOGMK2"
Workbooks.Open Filename:="S:\bry\QRYLOGMK2\QRYLOGMK2.xls"
Click to expand...

If ThisWorkbook.ReadOnly Then msgbox "Already opend by another user" And
exit sub

hth mcg
 
Many thanks for your prompt reply

i have copied the line of code (cut n paste) into the macro and am getting a
syntax error, sorry my vb knowledge is none existant so im not sure why it
doesnt work.

many thanks
Bry

ps if it help im using xl 2003
 
Thanks for your reply - i have cut n pasted the line into my code but am
getting an error :-
"compile error Syntax error"

using xl2003 if it helps.

thanks
Bry
 
Try

If ActiveWorkbook.ReadOnly Then
msgbox "Already opend by another user"
exit sub
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Thanks Bob - worked a treat

Bob Phillips said:
Try

If ActiveWorkbook.ReadOnly Then
msgbox "Already opend by another user"
exit sub
End If

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
Back
Top