B
Bry
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
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