PDA

View Full Version : Switching workbooks in VBA



PatWForbes
03-16-2009, 07:57 PM
Hi,

I'm quite new to VBA and would appreciate any help you could give.

In the below macro, I am working with two separate spreadsheets. One spreadsheet (from which I am running the macro), is a summary sheet. The second spreadsheet is variable in that its the format is the same everyday but the data and filename are different. My goal is to aggregate information from the daily spreadsheet into the summary spreadsheet. Because the name changes everyday on the daily spreadsheet, I've used the GetOpenFilename command. Once I get the data I want to collect, I open the summary workbook inside the macro, create criteria about where I want it pasted (using the find method) and paste. I then try to switch back to the daily sheet (which I opened originally opened via GetOpenFilename) and am unable to figure out how to reference it. The purpose of switching back is to copy more material to then paste in the summary sheet. Any help in activating to the daily file would be greatly appreciated.

Best regards,
Pat


Sub MacroinWorkbook()
'

Application.ScreenUpdating = False
ChDrive "C"

FlName = Application.GetOpenFilename
Workbooks.Open FlName

Range("M11").Select
ActiveCell.Replace What:="cob ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="cob ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByColumns, MatchCase:=True, SearchFormat:=False, ReplaceFormat:= _
False
Range("M11").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False

End With
Range("M11:M149").Name = "Total"
Range("Total").Select
Selection.Copy

Workbooks("Summary sheet.xls").Activate
Sheets("Summary").Select

Cells.Find(What:="!!!!", SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase _
:=True, SearchFormat:=False).Activate

ActiveSheet.Paste

Application.CutCopyMode = False

Workbooks (FlName).Activate

Application.ScreenUpdating = True

End Sub

Ago
03-17-2009, 01:11 AM
i guess there is a system on naming the daily file.
<date>.xls?
<weekday>.xls?
how is it done?

im quite ssure all of what you are doing can be automated.
could you perhaps make dummyworkbooks that we can work on?

GTO
03-17-2009, 01:54 AM
Greetings Pat,

Firstly, let me say Welcome, as I see you just joined :-) I would also mention that you will 'meet' some knowledgeable and mighty nice folks here, who will go out of their way to assist. Now as to your stated issue of reactivating the source workbook, we can fix that with a couple of minor tweaks:
Sub MacroinWorkbook()

Application.ScreenUpdating = False
'ChDrive "C"

'// If you step thru the code, you will see that .GetOpenFilename returns the //
'// workbook's FullPath, such as "C:\Data\MyBook.xls". While handy for opening the //
'// wb, not so good for activating the wb later... //
FlName = Application.GetOpenFilename
Workbooks.Open FlName

'//...so, we could change the value of the variable to just the workbook's name //
FlName = ActiveWorkbook.Name

'....statements....

'// Now we can use the variable to reactivate the source wb. //
Workbooks(FlName).Activate

Application.ScreenUpdating = True

End Sub

Now I hope you don't mind, but I'd like to suggest a couple of things that once you are used to them, will save you countless headaches and make the code quicker and more reliable. In short - while selecting and activating, and using the selection, activecell, etc, will work - - -setting a few references will produce better results in the vast majority of the time.

So, the following is your code 'twisted' a bit, with what I hope is decent commenting to explain it.
'// I would suggest always using Option Explicit. This insists you dimension your
'// variables.
Option Explicit

Sub MacroinWorkbook_()
Dim FlName As String
Dim wbSource As Workbook
Dim wksSourceSheet As Worksheet
Dim wksDestinationSheet As Worksheet
Dim rngDestination As Range

Application.ScreenUpdating = False

'// just remmed for testing//
'ChDrive "C"

'// As mentioned, .GetOpenFilename will return the full path to the workbook - but - //
'// it will return "False" if cancelled, so... //
FlName = Application.GetOpenFilename(FileFilter:="Microsoft Workbooks (*.xls),*.xls", _
Title:="Select the File")

'// ...let's test and exit if no workbook was chosen to open. //
If FlName = "False" Then Exit Sub

'// Now, rather than just open the wb, we'll set a reference to it. This way, we //
'// can grab it later (refer to it) rather than worrying about what's active //
Set wbSource = Workbooks.Open(FlName)

'// Change "Sheet1" to the name of the sheet in the source workbooks. //
Set wksSourceSheet = wbSource.Worksheets("Sheet1")

'// Same as setting a reference to a wb, we can set a reference to a worksheet. //
Set wksDestinationSheet = ThisWorkbook.Worksheets("Summary")

'// Now lets find "!!!!" a bit earlier, and set a reference to the range (the cell) //
Set rngDestination = wksDestinationSheet.Cells.Find(What:="!!!!", _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=True)

'// In case we forgot to type "!!!!", we'd like to bail here. //
If rngDestination Is Nothing Then
MsgBox "OOPS! We didn't find ""!!!!"""
Exit Sub
End If

'// Okay - back to the wb we opened. You were Replacing twice. Just once: //
wksSourceSheet.Cells.Replace What:="cob ", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True

'// Rather than select M11, we can just refer to it and change the various properties//
With wksSourceSheet.Range("M11")
.NumberFormat = "mm/dd/yy;@"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

wksSourceSheet.Range("M11:M149").Name = "Total"


'// Rather than selecting the range, copying, activating the other workbook, etc, //
'// we'll use our set references to copy/paste in on action //
wksSourceSheet.Range("M11:M149").Copy rngDestination

Application.ScreenUpdating = True
End Sub

In closing, I would mention that the above is still a bit rough, but I wanted to show substitutions in a hopefully clear manner.

Hope this helps and again Welcome,

Mark

PS - OOPS! I forgot to mention: You'll note I dropped a couple of optional args in .Replace. This was just because I'm writning in xl2000 at the moment, and they didn't exist yet.

Bob Phillips
03-17-2009, 02:03 AM
My preferred way is to set an object variable, and the reference the object through that variable



Dim DailyWB as Workbook

FlName = Application.GetOpenFilename
Set DailyWB = Workbooks.Open(FlName)

'some more code that accesses ThisWorkbook

'now access via the WB object
DailyWB.Worksheets(1).Cells ....

PatWForbes
03-17-2009, 07:44 PM
GTO & XLD,

Thank you so much for your assistance. Your advice helped me make the macro work the way I hoped it would.

Thanks again,

Pat

GTO
03-17-2009, 08:55 PM
Glad we were able to help :friends: