PDA

View Full Version : Solved: Dimension help when closing workbook.



LutonBarry
05-18-2013, 05:27 AM
I think this a simple question but one I cannot get my head round to get the right answer.

I have written a simple macro to import a couple of sheets from another workbook into another open workbook. I want to then close the original workbook, in the script below it is referred to as 'Dashboard.xlsx'. This file is opened using a variable I've called myFile as the file to be opened each day will not be the same name, following day it could be called "Dashboard1.xlsx" for example. So the macro currently fails at this line in the script:

Windows("Dashboard.xlsx").Activate

How can I write the macro so it closes whatever the daily workbook is open.

Here's the current script:

myFile = Application.GetOpenFilename()
Workbooks.Open Filename:=myFile
Sheets (Array("Raw OpenWIP Data")).Select
Sheets (Array("Raw OpenWIP Data")).Copy Before:=Workbooks_("Import").Sheets(1)
Windows("Dashboard.xlsx").Activate
ActiveWorkbook.Close SaveChanges:=False


This is probably a very simple question but one that is stumping me I've not used VBA for 10 years or so.

Thanks.

Paul_Hossler
05-18-2013, 05:47 AM
I've always had a hard time keeping multiple open workooks strieght, so for me what works to to explicitly Set a WB variable and refer to the variable instead of relying on Activeworkbook or ThisWorkbook or Workbooks ("...")

Not tested


Sub test()
Dim MyWorkbook As Workbook
myFile = Application.GetOpenFilename()
Workbooks.Open Filename:=myFile
Set MyWorkbook = ActiveWorkbook
Sheets(Array("Raw OpenWIP Data")).Select
Sheets(Array("Raw OpenWIP Data")).Copy Before:=Workbooks_("Import").Sheets(1)
MyWorkbook.Close SaveChanges:=False
End Sub


You could / should probably Dim a MyDataWorkbook and then Set that to ThisWorkbook in the beginning. That way you could explicitly refer to MyDataWorkbook.Worksheets ("Summary") for example


Paul

SamT
05-18-2013, 01:45 PM
How to Use:
Sub MySub
Dim MyBook As WorkBook
Dim MyOtherBook As Workbook

Set MyBook = NewOpenedWorkbook

If Not MyBook is Nothing Then Set MyOtherBook = NewOpenedWorkbook
If Not MyOtherBook is Nothing Then MyBook.Sheets(1).Copy Before:=MyOtherBook.Sheets(1)

End Sub


Function NewOpenedWorkbook() As Workbook
'Returns a workbook object or Nothing
'Uses GetOpenFileName to let User select one Workbook for opening
'Returns Nothing if user Cancels Dialog.
'If User Selects an already open Book, lets User Choose
'A. use already open Book: Returns said book
'B. use book selected to Open: Opens book and returns said book.
'C. cancel Operation: Returns Nothing

Dim MyFile As String
Dim BookName As String

'Variables for MsgBox
Dim msgTitle As String
msgTitle = "Selected Workbook Is Already Open!"
Dim msgButtons As Long
'vbYesNoCancel, vbQuestion, and Default is "Yes" button. (3+32+0)
msgButtons = 35
Dim msgText As String
msgText = "Click ""Yes"" or press ""Enter"" to Use the Current Book" & Chr(13) _
& "Click ""No"" to open the Selected Workbook." & Chr(13) _
& "Click ""Cancel"" to Stop operation."
Dim Answer As Integer

''''Get File To Open
MyFile = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks, *.xls, *.xlsx, *.xlsm", _
Title:="Select One Workbook To Open", _
MultiSelect:=False)
''''If the User Cancels the FileOpen DialogBox, Set the function to Nothing
'by exiting, so the Calling Sub can deal with it.
If MyFile = "False" Then Exit Function

''''Get the Workbook.Name part of FileToOpen
BookName = Right(MyFile, Len(MyFile) - InStrRev(MyFile, "\"))

''''See if Workbook already open and what User wants to do
'Set msg Text using BookName
msgText = "WorkBook " & BookName & " is Already Open." & Chr(13) & msgText

'If Book not already open, WorkBooks(NoSuchBook) causes an Error.
On Error GoTo NoSuchBookIsOpenATT
If Not Workbooks(BookName) Is Nothing Then 'Book is already open
Answer = MsgBox(Prompt:=msgText, Buttons:=msgButtons, Title:=msgTitle)
'Cancel = 2, Yes = 6, No = 7
''''Book Was open, so see what the user wants to do
If Answer = 2 Then Exit Function
If Answer = 6 Then
Set NewOpenedWorkbook = Workbooks(BookName)
Exit Function
ElseIf Answer = 7 Then
Workbooks.Open Filename:=MyFile
NewOpenedWorkbook = Workbooks(BookName)
Exit Function
End If
End If

NoSuchBookIsOpenATT:
Workbooks.Open Filename:=MyFile
Set NewOpenedWorkbook = Workbooks(BookName)
End Function

Function Short_Version_NewOpenedWorkbook() As Workbook
'Needs rename without "Short_Version_"
'Returns a workbook object or Nothing
'Uses GetOpenFileName to let User select one Workbook for opening
'Returns Nothing if user Cancels Dialog.

Dim MyFile As String
Dim BookName As String

''''Get File To Open
MyFile = Application.GetOpenFilename( _
FileFilter:="Excel Workbooks, *.xls, *.xlsx, *.xlsm", _
Title:="Select One Workbook To Open", _
MultiSelect:=False)
''''If the User Cancels the FileOpen DialogBox, Set the function to Nothing
'by exiting, so the Calling Sub can deal with it.
If MyFile = "False" Then Exit Function

''''Get the Workbook.Name part of FileToOpen
BookName = Right(MyFile, Len(MyFile) - InStrRev(MyFile, "\"))
''''Open selected book
Workbooks.Open Filename:=MyFile
''''Return new book
Set NewOpenedWorkbook = Workbooks(BookName)
End Function

LutonBarry
05-20-2013, 04:51 AM
Folks I'm so grateful for you help here. Looks like it isn't as simple as I thought but I have got to the bottom of it thanks to you both.:beerchug: