| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Check if workbook open
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: http://www.excelforum.com/member.php...o&userid=34683 View this thread: http://www.excelforum.com/showthread...hreadid=544484 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check if workbook open
Here is one I use from a printed name in a cell. Modify to suit
Sub GetWorkbook() If ActiveCell.Value = "" Then Exit Sub workbookname = ActiveCell.Value On Error GoTo OpenWorkbook Windows(workbookname & ".xls").Activate Exit Sub OpenWorkbook: Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen End Sub -- Don Guillett SalesAid Software "dkipping" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=34683 View this thread: http://www.excelforum.com/showthread...hreadid=544484 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check if workbook open
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 "Orders.xls" is open using your routine. Many thanks for your interest David Sub SaveOrder() ' ' SaveOrder Macro ' Macro recorded 19/05/2006 by David ' ' Range("A1:M36").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Selection.Copy Workbooks.Open Filename:="C:\My Documents\Orders\POTemp.xls" ActiveSheet.Paste Windows("Purchase Order.xls").Activate Sheets("3").Select Range("A2:M21").Select Selection.Copy Workbooks.Open Filename:="C:\My Documents\Orders\Orders.xls" Sheets("Orders").Select Range("A1").Select Range("A1").End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select ActiveWorkbook.Save ActiveWindow.Close Windows("Purchase Order.XLS").Activate Sheets("1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("2").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Windows("POTemp.XLS").Activate ActiveSheet.Shapes("Button 2").Select Selection.Cut ActiveSheet.Shapes("Button 1").Select Selection.Cut ChDir "C:\My Documents\Orders" ActiveWorkbook.SaveAs Filename:= _ Range("h11"), FileFormat:=xlNormal, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False ActiveWorkbook.Close Windows("Purchase Order.XLS").Activate ActiveWorkbook.Close End Sub -- dkipping ------------------------------------------------------------------------ dkipping's Profile: http://www.excelforum.com/member.php...o&userid=34683 View this thread: http://www.excelforum.com/showthread...hreadid=544484 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check if workbook open
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 Function Then call this in your code If IsWorkbookOpen("Book2.xls") = False Then -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dkipping" wrote in message ... 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: http://www.excelforum.com/member.php...o&userid=34683 View this thread: http://www.excelforum.com/showthread...hreadid=544484 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calc status of open excel workbook should not overwrite another | Setting up and Configuration of Excel | |||
| Excel 2003 hangs when trying to locate a workbook to open | Excel Discussion (Misc queries) | |||
| Excel workbook does not open in open window on desktop | Excel Discussion (Misc queries) | |||
| Sumif Linking to Another Workbook error #VALUE! | Excel Discussion (Misc queries) | |||
| Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
Hybrid Mode
