+ Reply to Thread
Results 1 to 2 of 2

opening an additional workbook from another workbook then resizing the newly opened book

  1. #1
    Registered User
    Join Date
    04-12-2011
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    5

    opening an additional workbook from another workbook then resizing the newly opened book

    I have a workbook that users need to open another "Calculator" workbook to enter data to calculate mortgate payouts
    what i would like to see happen is while they aere in the main workbook they click a button and the "CALCULATOR" workbook pops up and they do the calculation
    and the result is returned to a specific cell in the "MAIN" work book ...is this possible. here is what i have so far just opening the "CALCULATOR" workbook

    Sub Button1_Click()
    Dim strFilePath As String
    Dim xlApp As Object
    Set wb1 = ActiveWorkbook
    'MsgBox (ActiveWorkbook.Path)
    strFilePath = ActiveWorkbook.Path & "\Calculators\Closed Mortgage Penalty Calculator.xlsx"

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    xlApp.Workbooks.Open strFilePath
    xlApp.Left = 100
    xlApp.Top = 1
    xlApp.Width = 400
    xlApp.Height = 400
    xlApp.Worksheets("Sheet1").Range("A60").Activate
    xlApp.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
    ' xlApp.Worksheets("Sheet1").Range("C63").Copy
    End Sub



    what I would Like to have happen is fire off the
    xlApp.Worksheets("Sheet1").Range("C63").Copy

    code so that the result of the calculation is saved to the windows clipboard when the external "CALCULATOR"
    workbook is closed ...can that be done the "MAIN" workbook detect what is happening on the child "CALCULATOR" workbook?
    Last edited by merrittr; 06-22-2018 at 06:42 PM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: opening an additional workbook from another workbook then resizing the newly opened bo

    Have you considered a "calculator" worksheet in the main workbook that you show\hide as needed? Or a "calculator" userform? Those two methods are more common than opening\closing a seperate workbook.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

+ 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. [SOLVED] How Can I open two additional books when the first book is opened
    By TBM in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2018, 12:06 PM
  2. Newly opened xls file appeared as a workSHEET not workbook
    By terrypin in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-01-2016, 05:44 AM
  3. [SOLVED] auto macro for newly opened workbook
    By namluke in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-26-2014, 03:11 PM
  4. Need to Copy Columns from Previous Workbook to Newly Opened Workbook
    By BYizz in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-06-2012, 06:33 AM
  5. [SOLVED] Can a form be opened without opening up/displaying the workbook?
    By amhulli in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-01-2012, 01:44 PM
  6. Make newly opened workbook the active workbook
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2011, 10:12 AM
  7. Opening in new instance if there is opened workbook
    By owiec in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-22-2010, 02:41 PM

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