Solved: Recalling A Workbook

[drums4monty]

Hi All

I run some code on a workbook and on one of the sheets it checks to see if cell E53 is >=1, if it is a message box appears and lets me know. I have changes this so when I click ok it opens a closed workbook or me to check something later (I know how to do this), once this book is open it can just stay open until I close it down. I then need the macro to carry on and execute the remainder of the tasks on the original open workbook. I think I know how to call the original open workbook back but the only problem is that this macro gets run on numerous workbooks that are sent to me (these are all the same) and each one gets called something different. Is there a way to make an open workbook active again without specifying a name?

[Bob Phillips]

When you open the workbook, set a variabel to the bpook and reference through this object later



Set oWB = Workbooks.Open(...

'....

With oWB

'etc.

[drums4monty]

Thanks xld but I am affraid that I don't understand that.

[Bob Phillips]

It's a way of keeping a tab on a specific object (workbook in this case) without having to remember which one is active, or what its name is.

[drums4monty]

I just dont know how to use it. I have this code that runs on a sheet in a workbook


Sub FormatAll()
' FormatAll Macro
' Macro recorded 18/11/2006 by Alan
' Moves onto Retail Sheet
'
Sheets("Stationery").Select
If ActiveSheet.Range("E53") >= 1 Then
MsgBox "Purchase Order Pads Required"
Workbooks.Open Filename:="L:\Alan\All Purchase Order Pads.xls"
Sheets("Detailed Movement").Select
End If
Sheets("Retail").Select
Application.Run "PERSONAL.XLS!FormatSheetRetail"

End Sub


It works fine up to 'Sheets("Detailed Movement").Select', once the 'All Purchase Order Pads.xls' workbook is open I want the code to go back to the original open workbook and then carry on executing the code i.e 'Sheets("Retail").Select' I will then work on the All Purchase Order Pads later workbook later.

[Bob Phillips]

I am sure it can be done without selecting the sheet, but without knowing what 'FormatSheetRetail' does, I'll leave it



Sub FormatAll()
' FormatAll Macro
' Macro recorded 18/11/2006 by Alan
' Moves onto Retail Sheet
'
Dim ActiveWB as Workbook

Set ActiveWB = Activeworkbook
If ActiveWB.Sheets("Stationery").Range("E53") >= 1 Then
MsgBox "Purchase Order Pads Required"
Workbooks.Open Filename:="L:\Alan\All Purchase Order Pads.xls"
Sheets("Detailed Movement").Select
End If
ActiveWB.Activate
Sheets("Retail").Select
Application.Run "PERSONAL.XLS!FormatSheetRetail"

End Sub

[Bob Phillips]

It would also be better to set a reference to the other wb, for later in the code maybe, as a general principle



Sub FormatAll()
' FormatAll Macro
' Macro recorded 18/11/2006 by Alan
' Moves onto Retail Sheet
'
Dim ActiveWB as Workbook
Dim POWb As Workbook

Set ActiveWB = Activeworkbook
If ActiveWB.Sheets("Stationery").Range("E53") >= 1 Then
MsgBox "Purchase Order Pads Required"
Set POWb = Workbooks.Open(Filename:="L:\Alan\All Purchase Order Pads.xls")
Sheets("Detailed Movement").Select
End If
ActiveWB.Activate
Sheets("Retail").Select
Application.Run "PERSONAL.XLS!FormatSheetRetail"

End Sub

[drums4monty]

Thanks xld that is great. All 'FormatSheetRetail' does is add some text to certain cells and remove blank lines before moving onto another sheet.

[Bob Phillips]

Thanks xld that is great. All 'FormatSheetRetail' does is add some text to certain cells and remove blank lines before moving onto another sheet.

Yeah I figured that, the name you gave it gave the game away :whistle:

What I really meant was what it does if terms of how the code is written. Does it assume the active sheet, could it be passed a sheet as a parameter, and so on?