ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Macro stop if file in use by other user? (https://www.excelbanter.com/excel-discussion-misc-queries/104227-macro-stop-if-file-use-other-user.html)

Bry
Macro stop if file in use by other user?
 
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


Gazeta
Macro stop if file in use by other user?
 

U¿ytkownik "Bry" napisa³ w wiadomo¶ci
...
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"

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

hth mcg



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

Bry
Macro stop if file in use by other user?
 
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

"Gazeta" wrote:


U¿ytkownik "Bry" napisa³ w wiadomo¶ci
...
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"

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

hth mcg



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

Bry
Macro stop if file in use by other user?
 
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


"Gazeta" wrote:


U¿ytkownik "Bry" napisa³ w wiadomo¶ci
...
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"

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

hth mcg



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

Bob Phillips
Macro stop if file in use by other user?
 
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)

"Bry" wrote in message
...
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


"Gazeta" wrote:


U¿ytkownik "Bry" napisa³ w wiadomo¶ci
...
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"

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

hth mcg



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

Bry
Macro stop if file in use by other user?
 
Thanks Bob - worked a treat

"Bob Phillips" wrote:

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)

"Bry" wrote in message
...
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


"Gazeta" wrote:


U¿ytkownik "Bry" napisa³ w wiadomo¶ci
...
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"

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

hth mcg



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


All times are GMT +1. The time now is 02:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com