Results 1 to 4 of 4

VBA To change Active Workbook

This is a discussion on VBA To change Active Workbook within the Excel Questions forums, part of the Question Forums category; You would think this would be an easy one to find through a google search, but it is surprisingly difficult! ...

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Posts
    91

    Default VBA To change Active Workbook

    You would think this would be an easy one to find through a google search, but it is surprisingly difficult!

    Can someone please tell me how to change the active workbook using VBA code? I'm currently using:

    workbooks("potemplate.xls").activate

    But that is apparently wrong...

    POTemplate.xls is open, if it makes a difference.

    Thanks for any suggestions!

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    186

    Default Re: VBA To change Active Workbook

    Are you sure its not working? From the help file:

    This example activates Book4.xls. If Book4.xls has multiple windows, the example activates the first window, Book4.xls:1.

    Workbooks("BOOK4.XLS").Activate
    And yes, it'll die on you if you don't have the workbook open.

  3. #3
    Board Regular CWatts's Avatar
    Join Date
    Jan 2010
    Location
    Maine, USA
    Posts
    666

    Default Re: VBA To change Active Workbook

    If your VBA code is remaining lower case instead of converting things to upper case, then that is indicitive of a larger problem than syntax.

    Are you just pasting that into VBA or are you wrapping putting it in a sub like? Just making sure we're not assuming anything.

    Code:
     
    Sub Change()
        Workbooks("potemplate.xls").Activate
    End Sub

  4. #4
    Board Regular
    Join Date
    Jan 2011
    Posts
    91

    Default Re: VBA To change Active Workbook

    That part seems to be working now. I checked to see if uppercase/lowecase made a difference in this situation and it doesn't, so that seems like a good thing!

    The code I'm using looks through a folder, and for each file found, should repeat the ProcessData sub.

    So far it gets to the part where it copies data from "POTemplate.xls", changes back to the original workbook, but then the macro stops. No error, but also it doesn't paste or loop back to the next file in the folder...Any ideas?

    Thanks again for your help!


    Sub OpenAndProcess()
    Dim vaFileName As Variant
    Const MyDir As String = "[my file path]"
    'the location of the workbooks
    With Application.FileSearch
    .NewSearch
    .LookIn = MyDir
    'the directory to search in
    .SearchSubFolders = False
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    'workbooks found
    Application.ScreenUpdating = False
    For Each vaFileName In .FoundFiles
    'loop through each found workbook
    ProcessData vaFileName
    'pass workbook fullname to copy routine
    Next
    Else
    MsgBox "There were no Excel files found."
    End If
    Application.ScreenUpdating = True
    End With
    End Sub

    Sub ProcessData(ByVal Fname As String)
    Dim PasteTo As Workbook

    Workbooks.Open Fname
    'open the target workbook
    Set PasteTo = ThisWorkbook

    'Unhide, Unprotect, and Select the "Data Map" worksheet
    Worksheets("Data Map").Visible = True
    Sheets("Data Map").Select
    ActiveSheet.Unprotect Password:="[Password]"
    'Select IP5:IT55 from POTemplate.xls and paste to "ThisWorkbook"
    Workbooks("POtemplate.xls").Activate
    Sheets("Data Map").Select
    Range("ip5:it55").Select
    Selection.Copy
    PasteTo.Activate
    Range("ip5:it55").Select
    Paste = xlpaste


    'Close the workbook and save changes
    ActiveWorkbook.Close savechanges:=True

    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com