software

Some macros I use require another workbook to open so I include a line
like Workbooks.Open Filename:= C:\My\ Documents\Orders\POTemp.xls
The problem occurs when that workbook is already open – the macro
stalls. Is there a way to bypass that instruction if the other
workbook is already open?--
dkipping
------------------------------------------------------------------------
dkipping's Profile: www.excelforum.com/member.php...oamp;userid=34683
View this thread: www.excelforum.com/showthread...hreadid=544484Here is one I use from a printed name in a cell. Modify to suit

Sub GetWorkbook()
If ActiveCell.Value = quot;quot; Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname amp; quot;.xlsquot;).Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname amp; quot;.xlsquot;).RunAutoMacros xlAutoOpen
End Sub

--
Don Guillett
SalesAid Software

quot;dkippingquot; gt; wrote in
message ...
gt;
gt; Some macros I use require another workbook to open so I include a line
gt; like Workbooks.Open Filename:= C:\My\ Documents\Orders\POTemp.xls
gt; The problem occurs when that workbook is already open – the macro
gt; stalls. Is there a way to bypass that instruction if the other
gt; workbook is already open?
gt;
gt;
gt; --
gt; dkipping
gt; ------------------------------------------------------------------------
gt; dkipping's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34683
gt; View this thread: www.excelforum.com/showthread...hreadid=544484
gt;
You could use a function like

Option Compare Text

Function IsWorkbookOpen(FileName As String) As Boolean
Dim WB As Workbook
For Each WB In Workbooks
If WB.Name = FileName Or _
WB.FullName = FileName Then
IsWorkbookOpen = True
Exit Function
End If
Next WB
IsWorkbookOpen = False
End FunctionThen call this in your code

If IsWorkbookOpen(quot;Book2.xlsquot;) = False Then--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;dkippingquot;
gt; wrote in
message
...
gt;
gt; Some macros I use require another workbook to open so I include
gt; a line
gt; like Workbooks.Open Filename:= C:\My\
gt; Documents\Orders\POTemp.xls
gt; The problem occurs when that workbook is already open - the
gt; macro
gt; stalls. Is there a way to bypass that instruction if the other
gt; workbook is already open?
gt;
gt;
gt; --
gt; dkipping
gt; ------------------------------------------------------------------------
gt; dkipping's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34683
gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=544484
gt;

Sorry but I can't seem to make that work. The actual sub I'm using is as
below. Could you please alter to check if quot;Orders.xlsquot; is open using
your routine.
Many thanks for your interest
David

Sub SaveOrder()
'
' SaveOrder Macro
' Macro recorded 19/05/2006 by David
'

'
Range(quot;A1:M36quot;).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:=quot;C:\My Documents\Orders\POTemp.xlsquot;
ActiveSheet.Paste
Windows(quot;Purchase Order.xlsquot;).Activate
Sheets(quot;3quot;).Select
Range(quot;A2:M21quot;).Select
Selection.Copy
Workbooks.Open Filename:=quot;C:\My Documents\Orders\Orders.xlsquot;
Sheets(quot;Ordersquot;).Select
Range(quot;A1quot;).Select
Range(quot;A1quot;).End(xlDown).Select
ActiveCell.Offset(1, 0).Range(quot;A1quot;).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range(quot;A1quot;).Select
ActiveWorkbook.Save
ActiveWindow.Close
Windows(quot;Purchase Order.XLSquot;).Activate
Sheets(quot;1quot;).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(quot;2quot;).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Windows(quot;POTemp.XLSquot;).Activate
ActiveSheet.Shapes(quot;Button 2quot;).Select
Selection.Cut
ActiveSheet.Shapes(quot;Button 1quot;).Select
Selection.Cut
ChDir quot;C:\My Documents\Ordersquot;
ActiveWorkbook.SaveAs Filename:= _
Range(quot;h11quot;), FileFormat:=xlNormal, _
Password:=quot;quot;, WriteResPassword:=quot;quot;, ReadOnlyRecommended:=False,
_
CreateBackup:=False
ActiveWorkbook.Close
Windows(quot;Purchase Order.XLSquot;).Activate
ActiveWorkbook.Close

End Sub--
dkipping
------------------------------------------------------------------------
dkipping's Profile: www.excelforum.com/member.php...oamp;userid=34683
View this thread: www.excelforum.com/showthread...hreadid=544484
I would execute the macro I sent FIRST and then modify yours to remove the
selections.
This should get you going.

Sub copyfrom()
dwb = quot;workbooktocopyto.xlsquot;
dws = quot;sheettocopytoquot;
swb = quot;worbooktocopyfrom.xlsquot;
sws = quot;sheetctocopyfromquot;

Windows(swb).Activate

'Sheets(sws).Range(quot;b4:d4quot;).Copy _
'Workbooks(dwb).Sheets(dws).Range(quot;a15quot;)

'Get Values ONLY without changing to values first
Workbooks(dwb).Sheets(dws).Range(quot;b15:d15quot;).Value = _
Sheets(sws).Range(quot;b4:d4quot;).Value

End Sub--
Don Guillett
SalesAid Software

quot;dkippingquot; gt; wrote in
message ...
gt;
gt; Sorry but I can't seem to make that work. The actual sub I'm using is as
gt; below. Could you please alter to check if quot;Orders.xlsquot; is open using
gt; your routine.
gt; Many thanks for your interest
gt; David
gt;
gt; Sub SaveOrder()
gt; '
gt; ' SaveOrder Macro
gt; ' Macro recorded 19/05/2006 by David
gt; '
gt;
gt; '
gt; Range(quot;A1:M36quot;).Select
gt; Selection.Copy
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Application.CutCopyMode = False
gt; Selection.Copy
gt; Workbooks.Open Filename:=quot;C:\My Documents\Orders\POTemp.xlsquot;
gt; ActiveSheet.Paste
gt; Windows(quot;Purchase Order.xlsquot;).Activate
gt; Sheets(quot;3quot;).Select
gt; Range(quot;A2:M21quot;).Select
gt; Selection.Copy
gt; Workbooks.Open Filename:=quot;C:\My Documents\Orders\Orders.xlsquot;
gt; Sheets(quot;Ordersquot;).Select
gt; Range(quot;A1quot;).Select
gt; Range(quot;A1quot;).End(xlDown).Select
gt; ActiveCell.Offset(1, 0).Range(quot;A1quot;).Select
gt; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
gt; SkipBlanks _
gt; :=False, Transpose:=False
gt; Application.CutCopyMode = False
gt; Range(quot;A1quot;).Select
gt; ActiveWorkbook.Save
gt; ActiveWindow.Close
gt; Windows(quot;Purchase Order.XLSquot;).Activate
gt; Sheets(quot;1quot;).Select
gt; ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
gt; Sheets(quot;2quot;).Select
gt; ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
gt; Windows(quot;POTemp.XLSquot;).Activate
gt; ActiveSheet.Shapes(quot;Button 2quot;).Select
gt; Selection.Cut
gt; ActiveSheet.Shapes(quot;Button 1quot;).Select
gt; Selection.Cut
gt; ChDir quot;C:\My Documents\Ordersquot;
gt; ActiveWorkbook.SaveAs Filename:= _
gt; Range(quot;h11quot;), FileFormat:=xlNormal, _
gt; Password:=quot;quot;, WriteResPassword:=quot;quot;, ReadOnlyRecommended:=False,
gt; _
gt; CreateBackup:=False
gt; ActiveWorkbook.Close
gt; Windows(quot;Purchase Order.XLSquot;).Activate
gt; ActiveWorkbook.Close
gt;
gt; End Sub
gt;
gt;
gt; --
gt; dkipping
gt; ------------------------------------------------------------------------
gt; dkipping's Profile:
gt; www.excelforum.com/member.php...oamp;userid=34683
gt; View this thread: www.excelforum.com/showthread...hreadid=544484
gt;