Activating new workbooks with VBA

[lawrencemiller]

I have two separate workbooks opened on excel. I have right-clicked the excel button and opened the second workbook that way. I want to access information from cell A1 in one workbook and paste the information into the second workbook into position B2. The problem is I don't know how to activate the second workbook. I can write code that determines if that second workbook is opened, and it says it is. But when I open a new workbook, it opens as a new excel workbook and as read-only and I do not want that. Is there anyway to activate the second workbook without using the option in View --> New Window feature? Thank you for any advice.

 

[pbornemeier]

Welcome to the Board!

What do you mean by "I have right-clicked the excel button"

"access information from cell A1 in one workbook " is not possible. Workbooks have one or more worksheets. Each worksheet has a cell A1

This code will create a new workbook in the copy of Excel that is already running (which I have never seen to be ReadOnly) and it becomes the active workbook

Workbooks.Add

Please state exactly what you want to do.

 

[lawrencemiller]

I should have been more specific. I am using excel 2010. My goal is to:
1) Open a new instance of Excel
2) Copy information from Workbook 1 and paste it into the new instance of Workbook 2
3) Have both workbooks open and each set to one monitor (I am running this code off of a dual monitor system)

The problem with using

Workbooks.Add

or

Workbooks.Open("Blah.xls")

does not open a new instance of excel. It simply puts the new workbook in the same window. Similar to being on internet explorer or chrome and having multiple tabs when you want a new instance of internet explorer or google chrome without any tabs.

- Lawrence

 

[pbornemeier]

the following will open the same copy.

Sub MakeACopyOfExcel()

    Dim appXL As Excel.Application
    
    Set appXL = CreateObject("Excel.Application")
    appXL.Visible = True
    appXL.Workbooks.Open Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name
    appXL.Top = 200
    appXL.Left = 200
    
    'appXL.Quit
    'Set appXL = Nothing
    
End Sub

If you want other than that add a line like:

    ThisWorkbook.Worksheets(1).Range("A1:C25").Copy Destination:=appXL.Worksheets(1).Range("G18")

I believe you can increase the .Top or.Left values so they "push" into the second monitor address space.

Or (after adjusting your path)

X=shell("E:\Program Files (x86)\Microsoft Office\Office14\Excel.exe",vbNormalFocus)

 

[lawrencemiller]

Phil,

Thank you. Is there a way to activate the newly opened workbook?

 

[pbornemeier]

Option Explicit

Sub MakeACopyOfExcelGiveItFocus()

    Dim appXL As Excel.Application
    
    Application.WindowState = xlNormal
    
    With Application
        .Top = 100
        .Left = 100
        .Height = 400
        .Width = 600
    End With
    
    Set appXL = CreateObject("Excel.Application")
    With appXL
        .Visible = True
        .Application.WindowState = xlNormal     'normal state

        .Workbooks.Open Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name
        .Top = 200
        .Left = 200
        .Height = 400
        .Width = 600
        .Application.WindowState = xlMaximized  'Give it focus
        .Application.WindowState = xlNormal     'Return to normal state
        '.Quit
    End With
    Set appXL = Nothing
    
End Sub

 

[lawrencemiller]

Phil,

I agree with you that this will maximize the newly opened excel workbook. At the end of the code, I added

Thisworkbook.activate 
Msgbox Thisworkbook.name

The active workbook is still the original excel workbook but I would prefer to have the newly opened workbook be the active workbook. Is that possible?

Thank you for all your help so far.

Lawrence

 

[pbornemeier]

When I run the code in post #6 without the additional lines from post #7 the new workbook (a read-only copy of the first) is the activeworkbook. Is that not what you want?

The ThisWorkbook.Activate line returns control to the window/workbook that is running the code, the original one.

Option Explicit

Sub OpenANewInstanceOfExcel_CopySomeData_GiveItFocus()

    Dim appXL As Excel.Application
    
'    Application.WindowState = xlNormal          'Ensure this file is windowed, not needed for actual
'    With Application                            'resize/reposition for demo, not needed for actual
'        .Top = 100
'        .Left = 100
'        .Height = 400
'        .Width = 600
'    End With
    
    Set appXL = CreateObject("Excel.Application")
    With appXL
        .Visible = True                         'make new Excel instnce visible
'        .Application.WindowState = xlNormal     'Ensure this file is windowed, not needed for actual
    
        appXL.Workbooks.Add                     'Add a new workbook to the new window
        
        'Copy required data from the original workbook (ThisWorkbook) to the new one (appXL)
        ThisWorkbook.Worksheets("Sheet1").Range("A1").Copy
        .Worksheets(1).Range("A1").Select       'select destination in appXL
        .ActiveSheet.Paste                      'paste to appXL
        
'        .Top = 200                              'resize/reposition for demo, not needed for actual
'        .Left = 200
'        .Height = 400
'        .Width = 600
        
        .Application.WindowState = xlMaximized  'Give it focus
        .Application.WindowState = xlNormal     'Return to normal state

    End With
    Set appXL = Nothing

End Sub