Free Microsoft Excel 2013 Quick Reference

Workbook to open a new excel window Results

Hi all,

Is there a way to get a workbook to open in a new excel window rather then
one that is open?

Thanks in Advance

Jason Zischke

If I want to see a side by side view of two separate tabs in an Excel workbook, I generally select New Window and then tile the two views. However, whenever I open that second window, I find that even if I have grid lines turned off in the original window, the second window I open redisplays the grid lines and sets the zoom to 100%.

Is there a way to open a new window and have the view options mirror exactly those within the original window?

Thanks, folks!

I need to be able to have workbook A and workbook B open. I will open
workbook A first by double clicking the workbook A.xls which opens Excel.
Workbook A contains no macros. Then I need to be able to open workbook B
which contains macros that remove all toolbars, menus. When I do this now,
the toolbars are removed in both workbooks. Is there a way to open up
Workbook B in a new application where the Macros will not effect both or all
open workbooks. I realize I can open 2 instances of Excel & navigate to each
workbook A & B, but his method is apparently too cumbersome for some other
users. So, double click workbook A one App opens, double click workbook B a
second App opens, macros remove toolbars in workbook B window only. I did
change one of the settings so each workbook opens in a new window but
apparently they are still in one instance of Excel, I would like each double
click to open a new instance of Excel loading it's default settings. Thank
you for your help with this one.

I need to be able to have workbook A and workbook B open. I will open
workbook A first by double clicking the workbook A.xls which opens Excel.
Workbook A contains no macros. Then I need to be able to open workbook B
which contains macros that remove all toolbars, menus. When I do this now,
the toolbars are removed in both workbooks. Is there a way to open up
Workbook B in a new application where the Macros will not effect both or all
open workbooks. I realize I can open 2 instances of Excel & navigate to each
workbook A & B, but his method is apparently too cumbersome for some other
users. So, double click workbook A one App opens, double click workbook B a
second App opens, macros remove toolbars in workbook B window only. I did
change one of the settings so each workbook opens in a new window but
apparently they are still in one instance of Excel, I would like each double
click to open a new instance of Excel loading it's default settings. Thank
you for your help with this one.

Hi Gang!

I opened a spreadsheet from a friend and it changed my excel2000 settings.

Formerly, when I opened a 2nd spreadsheet, it opened a separate excel
window. Now all of my spreadsheets open in one window - very annoying!

I know how to force it, (start-run-excel) but I want to re-set excel2000 to
'automatically' open 2nd workbooks in a new window - like when I doubleclick
a workbook from my desktop, for example.

Help!
Thanks,
..James.

Hello all

With the help i find here in the forum i have made a simple user form.
I managed to find a code that allow me to minimize the user form and it works fine
I also found the way to hide the application/workbook and show just the user form

So far so good.
Here are my 3 questions if i may:

1- while the form is minimized and i go to open a new excel file, the form "jumps" up over the new opened file ( but the focus is not on the form ) and i have to minimize it again

2- when closing another file ( while the form is minimized ) the form closes as well , is there a way to prevent this from happening?

3- when i do close the user form , the excel stay open with a blank window , how can i close excel as well if the form is the only open excel file?

I thank you for your time and any help that can be given

Have a good day

Zohar

Is there any way to put a command in my Auto_Open macro that will open my workbook in a new Excel window? Even if I can include a VBA message box that prompts me to open in a new window, that would be acceptable. I’m not very optimistic as I presume the file will already be open in a current window before my macro has a chance to run.

The problem is that double-clicking a spreadsheet doesn't open a new instance
of Excel, it opens it in the current one. Then when you click the close "x"
in the upper right hand corner of the window, it closes ALL your workbooks,
not just the current one. This is not consistent with the way Word works
where opening a new Word document will open a whole new program window. Can I
change a setting to make Excel behave like Word?

I've tried this solution from

Is there a way to have Excel open in a new instance if click on a different
workbook while Excel already has one open? If I do this now, the second
workbook is opened in the same window as the first and I would like to have
it opened in another window.

Thank you very much. I hope I have made my question clear.

I use the following macro to open excel files. Can this be modified to open
a picture ppppp.jpg ?

. . . or, can someone help me with a new macro that can open pictures
. . . I would still like to have the file checking options to see if the picture is already open and if not, that it actually exists.

Please see bottom of this posting for additional question!

Thanks
Matt @ Launchnet

Sub NewExcelWithWorkbookFantastic()
Dim oXL As Object  'This is needed to open a new instance of Excel.
                  'Without it, the file is only opened as a new Window
Dim testFileFind
Dim oWB As Object

If FileAlreadyOpen("c:extrafilesfantastic.xls") = True Then
   MsgBox "File is alreaady open"
   
End
Else

'The following tests for the existance of the file
testFileFind = Dir("c:extrafilesfantastic.xls")

'If the file is not found there will be nothing
'in the variable and processing ends.
If Len(testFileFind) = 0 Then
  MsgBox "You do not have this file in C:extrafiles"
  End
End If

'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
Set oXL = CreateObject("Excel.Application")

'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
oXL.Visible = True
Set oWB = oXL.Workbooks.Open("c:extrafilesfantastic.xls")
     
End If
End Sub
P.S. - - - I am still looking for a way to activate the workbook we're looking for when the above test states that it is already open.

When I select and right-click a text document (.txt) in Windows, and choose "Open with" to open the document in Excel, a new instance of Excel starts.

When i do it again with another text document, again a new instance.

This presents me with a problem: I can't drag the newly opened worksheet to workbooks in other instances.

Is there a way of forcing Excel NOT to open a new instance?
Or maybe disable Excel to open new instances?

Thanks

I have the following macro that is linked to a button.

There is also a function that is called to check to see if the file is already open.

These macros & functions work properly.

Basically, the Case statements check the extension of the file to be opened and then calls the correct macro to open that particular file.

I have shown the macro that is called by the Case statement that has the extension .xls . . . Excel

I want to keep these Case statements as I always want Excel files opened in new instances of Excel.

What I need is when the existing case statements do not find an extension that is listed, I want code added to this macro that can open any type of file, such as files with other types of extensions or without extensions.

My son suggested that I should try to get a macro that could open the file using the RUN command found under the Start Button. Sounds good to me.

Is there anyone that can give me a hand. I'll be happy to answer any questions.

Thanks in Advance as everyone has always been a great help to me.

Sub ExcelOrWordFile()

Select Case ActiveCell.Column
Dim cl As Range
    Case 1, 3, 5, 7, 9, Is > 11
    MsgBox "Please select the name of a file you want to open."
    End
    End Select

       'THIS GETS THE PATH & FILE NAME & EXT OF THE FILE I WANT OPENED
Set cl = ActiveCell.Offset(0, -1)
   Select Case LCase(Right(cl.Value, 3))
   Case "xls", "xla"
      Call NewExcelWithWorkbook  'THIS CALLS THE OPEN EXCEL MACRO
   Case "doc", "wpd"
      Call NewWordWithDocument
   Case "exe"
      Call NewShortcut
   Case Else
      MsgBox "Please select the name of a file you want to open.", vbInformation
   End Select
End Sub


Sub NewExcelWithWorkbook()
    Dim oXL    As Object    'This is needed to open a new instance of Excel.
    'Without it, the file is only opened as a new Window
    Dim OpenFileName    '<-this isn't used
    Dim testFileFind As String
    Dim oWB    As Workbook
    Dim cl     As Range
    'This reads the cell 1 column to the Left so the path & file name can be read
    Set cl = ActiveCell.Offset(0, -1)


    'The following tests for a blank cell and ends processing
    'It is needed because dir() function will not work with a blank.
    If Len(Trim(cl)) = 0 Then
        MsgBox "You have not entered a Path and File name."
        End
    End If

    'The following tests for the existance of the file
    testFileFind = Dir(cl)
    
    'If the file is not found there will be nothing in the variable and processing ends.
    If Len(testFileFind) = 0 Then
        MsgBox "Invalid selection." & Chr(13) & _
               "Filename " & cl.Value & " not found"
        End
    End If

        'THIS TESTS TO SEE IF DOCUMENT ALREADY OPEN
    If FileAlreadyOpen(cl.Value) = True Then
        MsgBox "File is already open"
    End
    Else
    End If

    'THIS LINE OF CODE OPENS THE NEW INSTANCE OF EXCEL.
    Set oXL = CreateObject("Excel.Application")

    'THIS LINE OF CODE MAKES THE NEW INSTANCE OF EXCEL VISIBLE.
    oXL.Visible = True

    Set oWB = oXL.Workbooks.Open(cl)

End Sub


The problem is that double-clicking a spreadsheet doesn't open a new instance
of Excel, it opens it in the current one. Then when you click the close "x"
in the upper right hand corner of the window, it closes ALL your workbooks,
not just the current one. This is not consistent with the way Word works
where opening a new Word document will open a whole new program window. Can I
change a setting to make Excel behave like Word?

I've tried this solution from oatleyd@myway.com to no avail:

"The answer involves changing the file association stuff in windows. Go
to explorer, choose tools, then folder options. Click the File Types
tab. Scroll down to the XLS extension. Click the Advanced button.
Choose "open", then click the edit button.

At the end of the "Application used" entry, you'll probably see: /e
After this, add: "%1" (be sure to include the quotes.)

Then uncheck the "Use DDE" checkbox. Then click OK. (Windows re-checks
it at some point for some reason, but it still works)

OK your way out of the file types dialog.

Now when you double-click a spreadsheet, it will open it in a new
instance of Excel. "

Ok...I have used the following code to open a new workbook and go to a spacific cell..it works fine to get to it..but is it possible to make it open with that cell at the top? I have a frozen column headings so not sure how to go about it..

Sub Macro2002047()
ChDir "N:Storage list"
Workbooks.Open Filename:="N:Storage listWater and Sewer details.xls"
Range("A106").Select
Windows("Water and Sewer details.xls").Activate
End Sub

I'm new to the macro world so please help...Ohh..it is Excel 2003 on Windows XP if you need that...

Kris

I am running the following code in a module. I am trying to open a new workbook, copy a hidden sheet from my existing workbook to the new workbook, and leave it open for the user to save. When i run this code, the new workbook is left open, but i cant click on any of the cells. The excel menu works, but i cant close the sheet, and cant click back on my original workbook. Excel is locked up. If i have the VB editor open, I can run a 1 line procedure

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and then things are un-frozen, and i can work with the workbooks. If I dont have the VB window open, I have to end the task on Excel. I have not disabled screen updating anywhere in the application. Is there better code to use to accomplish this, or did I do something wrong?


	VB:
	
 'CurrentWorkbook and UsageData are Public Excel.Workbook variables declared in a module
 
 'set a pointer to my workbook
Set CurrentWorkbook = Application.ActiveWorkbook 
CurrentWorkbook.Unprotect ThePassword 
 
 'Create a new Workbook
Set UsageData = Application.Workbooks.Add 
 
 'copy a hidden sheet in my workbook to the new workbook
CurrentWorkbook.Worksheets("Usage Data").Copy after:=UsageData.Worksheets(1) 
 
 'protect my workbook again
CurrentWorkbook.Protect ThePassword 
 
 'activate my new workbook
UsageData.Sheets(1).Activate 
UsageData.Sheets(1).Cells(1, 1).Value = "Please save this file." 
 
 'protect my new workbook
UsageData.Protect ThePassword 
 
Set CurrentWorkbook = Nothing 
Set UsageData = Nothing 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried various things like NOT setting the workbook variables to nothing, and calling

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
at the end of the procedure, but nothing has worked so far.

My question is the following:

- I use VBA in Excel, and would like to use Excel itself as a data source
(Using ADO). I'd like to keep a table in excel and use SQL expression
power to manipulate the data in the table (selection, grouping, filtering.),
rather than using the usual Excel lookups.

- This does work, but I have an annoying secondary effect with the following
environment:

1) I have an (unrelated) excel session already open (Session 1) on my
Windows desktop.

2) I open a new, fresh, Excel session (Session2) and open my file, with the
table, and the VBA code.

3) I run the code in my file, Session2: it does work, but the unwanted
effect is that my file opens itself again, as Read-Only, in the other
Session1!

I do not know what went wrong. It could be great if you could put me on the
right tracks.

Thanks in advance for your help.

Regards


	VB:
	
 Simulation3() 
     
    strPathExcelFile_FILTER = ThisWorkbook.FullName 
     
    Set objConnection = CreateObject("ADODB.Connection") 
    Set objRecordSet = CreateObject("ADODB.Recordset") 
     
    objConnection.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
    "Data Source=" & strPathExcelFile_FILTER & ";" & _ 
    "Extended Properties=Excel 8.0;" 
     
     
    objRecordSet.Open "SELECT COUNT(*) AS resultat FROM [SHEET1$A1:IV20] 
    WHERE [PX_LAST] > 20", objConnection, adOpenForwardOnly, adLockReadOnly 
     
    Simulation.Label2.Caption = objRecordSet.fields("resultat") 
     
    objRecordSet.Close 
    objConnection.Close 
    Set objConnection = Nothing 
    Set objRecordSet = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


This is driving me batty, and I found several other threads that were ALMOST my problem, but not quite. So, here goes...

EDIT: Using Office 2007 under Windows Vista Ultimate

I regularly process CSV files that are downloaded from various websites. The problem is, for some reason many (but NOT all) downloaded CSV files do not open in "Microsoft Office Excel" and thus, my primary workspace. They instead open in a separate instance of "Excel.exe" which is actually kinda nice because I can treat each separately, move windows around, etc. The problem is, macros that are in my primary workbook, cannot be seen by the secondary instance(s). So while I have created a number of nifty macros to clean up all the data and format it exactly how I want it in the blink of an eye, I can't run them when a file opens in its own instance.

So, I suppose I need to know how to achieve one of several possible outcomes:

1) How can I force ALL .csv files to open in the primary "Microsoft Office Excel" application? NOTE: Using the normal Windows Explorer "Open With..." function and setting the default application only works for LOCAL files, and does NOT work to control how remotely downloaded .csv files are opened.

2) How can I make it so that macros can be seen between Excel instances - such that when I choose to see macros in "all open workbooks", it will actually see the macros in the open workbook in the other primary Excel instance?

Hopefully this makes sense and one of you clever folks has an answer.

Thanks all,

Jonathan

This should be simple enough ... the following code opens a new occurrence of Excel and creates & saves a new Workbook. I then want to switch to that workbook to manually edit it, that is all. However, the line ...

Code:
... opens the file again, as Read-Only in my current Excel Session.

I'm guessing that, as the window with the new, empty & renamed Workbook already exists, I don't actually need an "Open" statement here, just something to switch to that window ... but how do I do that ?!?

Code:
If MyFixFile = False Then
    Set MyxlApp = CreateObject("Excel.Application")
    MyxlApp.Visible = True
    Set MyxlWB = MyxlApp.Workbooks.Add ' create a new workbook
    With MyxlWB.Worksheets(1)
        .SaveAs ("E:" & TeamDetsLeague & "Fixtures.xls")
    End With
    Workbooks.Open("E:" & TeamDetsLeague & "Fixtures.xls").Activate
    MyxlWB.Close
    Set MyxlWB = Nothing
    Set MyxlApp = Nothing
    Exit Sub
End If


I am pretty new to Excel 2007, and this is the first time I've tried to write a macro for 2007. I am trying to open up a txt file and then xlDelimited the file by commas. I've got that part down except it keeps opening the txt file in another instance of excel, and what i really want is if it would open the file or at least the data into the existing excel file. Maybe import is the more appropriate term, but I want to delimit the data, separating it by commas too. Right now I have this.

Code:
Sub Auto_Open()
    NewFN = Application.GetOpenFilename(FileFilter:="Text Files (*.txt), *.txt", Title:="Please select a file")
    
    ' Cancel Pressed
    If NewFN = False Then
        MsgBox "Stopping because you did not select a file"
        Exit Sub
    Else
        MsgBox NewFN
        Workbooks.OpenText Filename:=NewFN, DataType:=xlDelimited, comma:=True
    End If
End Sub
Any thoughts on how this should be handled?

Hi there,

I wonder whether anybody could help with this problem. I would like to create a macro that enables the users of my workbook to create a new customer for a customer list.

Ideally, I would like my user to be able to click onto a button, after which a window opens (a user form), which allows the user to enter the name of a new customer.

The macro should then enter this name into colum c in the active worksheet without overwriting current entries and then activate another worksheet, called "Overview", which contains another list with customers. It should then :->

1) unlock the worksheet (e.g. by typing in the password)
2) drag down the last row of this list in overview
3) delete the content of some of the cells and then
4) copy the previously entered customer name into the first cell of the list.
5) lock the worksheet and stop.

I actually have recorded this macro (see below), but I am not very skilled with VBAs :o

Moreover, the macro didn't record the unlock/lock worksheet function and doesnt contain the userform (which I probably could build).

Code:
Sub Create_New_Customer_Macro()
'
' Create_New_Customer_Macro Macro
'

'
    Range("C5").Select
    ActiveCell.FormulaR1C1 = _
        "Here the user enters the name of a new customer, e.g. ""customer 1"""
    Sheets("Overview").Select
    ActiveSheet.Unprotect
    Range("B7:CS7").Select
    Selection.AutoFill Destination:=Range("B7:CS8"), Type:=xlFillDefault
    Range("B7:CS8").Select
    Range("BL8").Select
    Selection.ClearContents
    Range("BK8").Select
    Selection.ClearContents
    Range("BJ8").Select
    Selection.ClearContents
    Range("BG8").Select
    Selection.ClearContents
    Range("BF8").Select
    Selection.ClearContents
    Range("BE8").Select
    Selection.ClearContents
    Range("BB8").Select
    Selection.ClearContents
    Range("AZ8").Select
    Selection.ClearContents
    Range("AX8").Select
    Selection.ClearContents
    Range("AT8").Select
    Selection.ClearContents
    Range("AQ8").Select
    Selection.ClearContents
    Range("AN8").Select
    Selection.ClearContents
    Range("AK8").Select
    Selection.ClearContents
    Range("AH8").Select
    Selection.ClearContents
    Range("AE8").Select
    Selection.ClearContents
    Range("AB8").Select
    Selection.ClearContents
    Range("Y8").Select
    Selection.ClearContents
    Range("V8").Select
    Selection.ClearContents
    Range("S8").Select
    Selection.ClearContents
    Range("P8").Select
    Selection.ClearContents
    Range("M8").Select
    Selection.ClearContents
    Range("J8").Select
    Selection.ClearContents
    Range("G8").Select
    Selection.ClearContents
    Range("D8").Select
    Selection.ClearContents
    Range("C8").Select
    Selection.ClearContents
    Sheets("Setup").Select
    Selection.Copy
    Sheets("Overview").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D8").Select
    Application.CutCopyMode = False
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
I am working on this problem already a couple of days. I wanted to use the list funciton first, but the problem is that it doesn't work when the worksheet is locked (also when you unlock the list cells and allow the user to insert new rows ).

Any advice is highly!!! appreciated