+ Reply to Thread
Results 1 to 6 of 6

How to use Getopenfilename to switch between workbooks

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    How to use Getopenfilename to switch between workbooks

    Hi guys.

    Not sure if this is a good thing.. I frequent the forum a lot but dont' really post too much as I usually tend to find my solutions on here.
    There are quite a few threads on copy-pasting data between 2 workbooks but I unfortunately can't seem to extend that code to suit my needs.

    I am trying to write a macro to do the following:
    open 2 workbooks (path and filename changes each month). The user will select the files using getopenfilename.
    Then I want the macro to copy specific cells from each of these workbooks (am ok to write my own code for this), and then paste it back in the third workbook, which is the active workbook where I'll be keeping the macro button to execute the code.

    I'm not sure how to refer to the workbooks in the code.
    I've tried something along these lines but can't seem to get it to work

    I've tried using the Windows.Activate code also but I suspect my syntax is wrong.
    (I'm an accountant learning VBA from scratch, so please consider my skill level as beginner to VBA. Am very much above average user at non-VBA stuff though able to write complex SUMPRODUCTS etc.)

    Here is the code I compiled up to now.
    This should be enough to give an idea of what I'm trying to achieve.

    Sub codetest()
        
        
        Dim BillCalcs As String 'this is the activeworkbook where info will be pasted
        Dim MEWBC As String 'this is workbook 1 to copy from
        Dim MEWBP As String 'this is workbook 2 to copy from
        
        'specify which files to use as a source
        MEWBC = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is CURRENT Mth Wkbk")
        MEWBP = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is PRIOR Mth Wkbk")
        
        Workbooks.Open (MEWBC), UpdateLinks:=0
        Workbooks.Open (MEWBP), UpdateLinks:=0
        
        'this is where it fails :(
        Workbooks(MEWBC).Activate
        
        Sheets("CUSTOMSHEETNAME").Select
        range("B13:B100").Select
        Selection.Copy
        
        Workbooks(BillCalcs).Activate
        ActiveSheet.Select
        Selection.Paste
    
    End Sub
    Last edited by rasonline; 08-27-2013 at 04:12 AM. Reason: Grammar, change heading, SOLVED

  2. #2
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Getfilename for 2 wkbks, then copy-paste data to third workbook

    Hey Rasonline.

    It's all in those first few lines... Dim them as Workbook rather than string, and try again!
    Going for Guru! Click the Star to the bottom left of this post if I helped!

  3. #3
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: Getfilename for 2 wkbks, then copy-paste data to third workbook

    Unfortunately doesn't seem to work
    The moment I Dim them to Workbook, then the Getopenfilename line flags as an error.
    I think these lines need the Dim to be string

        MEWBC = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is CURRENT Mth Wkbk")
        MEWBP = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is PRIOR Mth Wkbk")

  4. #4
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: How to use Getopenfilename to switch between workbooks

    Ok, so Im trying to do something like this..

    Dim the workbook as a different name
    Open the workbook, then assign the workbook variable

    Still can't get it to work though I'm thinking that perhaps "ThisWorkbook" is not the correct command to use repeatedly?

        Dim BillCalcs As Workbook
        Set BillCalcs = ThisWorkbook
        
        Dim MEWBC As String
        Dim MEWBP As String
        
        MEWBC = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is CURRENT Mth Wkbk")
        MEWBP = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is PRIOR Mth Wkbk")
        
        Dim wbC As Workbook
        Dim wbP As Workbook
        
        Workbooks.Open (MEWBC), UpdateLinks:=0
        Set wbC = ThisWorkbook
        
        Workbooks.Open (MEWBP), UpdateLinks:=0
        Set wbP = ThisWorkbook
        
        Workbooks(wbC).Activate  'code executes up to this point, and then stops with the debugger highlighting this line
        Sheets("A.4 Engagement Summary").Select
        range("B13").Select
        range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        
        Workbooks(BillCalcs).Activate
        ActiveSheet.Select
        Selection.Paste
    I've tried substituting the "workbooks" command with "windows" but that also doesn't work. Eg. "Windows(BillCalcs).Activate"

  5. #5
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: How to use Getopenfilename to switch between workbooks

    Ok, managed to get it to work through a bit more research and trial and error.
    Here is the solution for anybody else's reference:

    Sub codetest()
       
        Dim BillCalcs As Workbook
        Set BillCalcs = ActiveWorkbook
        
        Dim MEWBC As String
        Dim MEWBP As String
        
        MEWBC = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is CURRENT Mth Wkbk")
        MEWBP = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*", Title:="Where is PRIOR Mth Wkbk")
        
        Dim wbC As Workbook
        Dim wbP As Workbook
        
        Set wbC = Workbooks.Open(MEWBC)
        Set wbP = Workbooks.Open(MEWBP)
        
        wbC.Activate
        Sheets("CUSTOMSHEETNAME").Select
        range("B13").Select
        range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        
        BillCalcs.Activate
        ActiveSheet.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
    End Sub

  6. #6
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    127

    Re: How to use Getopenfilename to switch between workbooks

    Still struggling with this And I'm so sure it is a small fix that's required.
    Anybody?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 01-29-2013, 07:01 AM
  2. Copy/Paste range of data excluding certain rows and copy to another workbook
    By HoerbigAdm in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-12-2012, 02:51 PM
  3. [SOLVED] Match Copy & Paste Between 2 Wkbks Revised
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-26-2011, 06:12 AM
  4. Copy data from workbook, create new workbook, paste data to new worbook?
    By mpkavanagh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-16-2011, 06:39 PM
  5. Copy paste WkBk/sheet 1 to multiple wkbks/sheets
    By wrpalmer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1