We help IT Professionals succeed at work.
Troubleshooting Question

What is the excel visual basic code to open up a brand new excel application via the Start window?

50 Views
Last Modified: 2020-10-13
I have a spreadsheet that I open up each morning. It contains a macro such that when I run it a number of separate spreadsheets open up in sequence, each have their associated macros run and then the spreadsheet is shut down. Each spreadsheet is being opened in the same excel application as the initial spreadsheet. What I would like to do is have each of these sequenced spreadsheets opened up in its own excel application just like if I was going to the Start window, opening a brand new excel and then running the spreadsheet.
Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Create a new Excel instance for automation. As you don't have posted your actual code you need to figure it out where you need to place it and how it should look in the final version:

Option Explicit

Public Sub YourMacroX()

  Dim ExcelApplication As Excel.Application
  Dim ExcelWorkbook As Excel.Workbook

  Set ExcelApplication = New Excel.Application
  Set ExcelWorkbook = ExcelApplication.Workbooks.Open("yourExcelFile")
  'Run your old macro using ExcelWorkbook.
  ExcelWorkbook.Save
  ExcelWorkbook.Close
  Set ExcelWorkbook = Nothing
  ExcelApplication.Quit ' Lookup save constants.
  Set ExcelApplication = Nothing

End Sub

Open in new window

Author

Commented:
Let me provide a bit more information. Basically, the spreadsheets that I am interested in will have to be left open so that they can be updated with live information at different points throughout the day. Therefore the process will be that the initial template file will open up 3 separate spreadsheets that will need to be opened in their own excel application and then left open to update. I suppose your amended code that I would need would be as follows:

Option Explicit
Public Sub YourMacroX()  
Dim ExcelApplication As Excel.Application  
Dim ExcelWorkbook As Excel.Workbook  
Set ExcelApplication = New Excel.Application  
Set ExcelWorkbook = ExcelApplication.Workbooks.Open("yourExcelFile")  
'Run your old macro using ExcelWorkbook.    
Set ExcelWorkbook = Nothing  
Set ExcelApplication = Nothing
End Sub
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Well, without your actual code or a slightly better process description, yes you need that code.

I recommend that you start playing with it.

Author

Commented:
The code did not seem to work for me. I used variables to construct the full path name and spreadsheet name so I don't know if that was the cause?

Author

Commented:
The code ran through to the end and did not debug but it did not open the spreadsheet at all.
CERTIFIED EXPERT

Commented:
you should provide at least the results of your calculated path

Author

Commented:
Posted below is the code that I used. This code is found in what I call the Scheduler file. On the first sheet of the tab called CONTROL are a list of spreadsheet names and their associated folder path ways. The first cell of the list is given the reference name PNL.

What is supposed to happen is that I open the Scheduler file, run this macro and the first file of the list should open in a brand new excel application but when I tried nothing happened, no error or debugging messages.


Option Explicit

Public Sub testmacro()
Dim ExcelApplication As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim MainBook, FolderName, currbook, activebook As String
Dim X As Integer

MainBook = ActiveWorkbook.Name
FolderName = 'specified path way'
Dim wb As Workbook
Set ExcelApplication = New Excel.Application
Set ExcelWorkbook = ExcelApplication.Workbooks.Open(FolderName & Workbooks(MainBook).Sheets("CONTROL").Range("PNL").Offset(0, 0).Value)
 
End Sub

Author

Commented:
Tweaked the code. Now it opens the file.

Option Explicit

Public Sub testmacro()
Dim ExcelApplication As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim MainBook, FolderName, currbook, activebook As String
Dim X As Integer

MainBook = ActiveWorkbook.Name
FolderName = 'specified path way'
Dim wb As Workbook
'Set ExcelApplication = New Excel.Application - excluded the line
Set ExcelWorkbook = Excel.Application.Workbooks.Open(FolderName & Workbooks(MainBook).Sheets("CONTROL").Range("PNL").Offset(0, 0).Value)

Author

Commented:
I just tested the code and I have the same problem as before - the spreadsheet that is opened via the code does not open in a separate excel application. What I would expect to see when I open a brand new excel application is an 'Add-In Loader' window that lists features such as username, domain etc. I do not see this window when I use the code above.

Also, when I look in Visual Basic I see all the different VBA projects and modules all linked together, which should not be the case if files were opened in different applications. 

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
E.g.

Option Explicit

Public Sub Test1()

  Const SAMPLE_FILE_1 As String = "C:\Temp\Test1.xlsx"
  Const SAMPLE_FILE_2 As String = "C:\Temp\Test2.xlsx"

  Dim Workbook1 As Excel.Workbook
  Dim Workbook2 As Excel.Workbook
  
  Set Workbook1 = OpenWorkbook(SAMPLE_FILE_1)
  Set Workbook2 = OpenWorkbook(SAMPLE_FILE_2)

  Set Workbook1 = Nothing
  Set Workbook2 = Nothing
  
End Sub

Public Function OpenWorkbook(ByVal CFileName As String) As Excel.Workbook

  Dim ExcelApplication As Excel.Application
  Dim ExcelWorkbook As Excel.Workbook

  Set ExcelApplication = New Excel.Application
  Set OpenWorkbook = ExcelApplication.Workbooks.Open(CFileName)
  ExcelApplication.Visible = True
  Set ExcelApplication = Nothing

End Function

Open in new window


btw, please edit all your posts and embed the code into [code][/code] tags or use the </> button in the posts toolbar. This increases readability and copying code.

Author

Commented:
Alright, I just tried to run the code and I noticed that what the code is doing is opening up the standard excel application that I have on my computer but actually what I need it to do is to open up another installed excel application called GXL that has lots of add-ins built into it enabling me to run my spreadsheets. Is it possible for the VB code to specify which particular version of excel it needs to open?

Author

Commented:
The GXL application  is stored in the following location C:\ProgramData\Microsoft\Windows\Start Menu\Programs\GXL\GXL 64-bit as a shortcut on my desktop. Would it be possible to specify the application based on this information?
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Is it possible? Probably, but I don't know GXL. What kind of application is it?

Author

Commented:
GXL is basically an in-house built application based upon a standard excel application. The two are basically the same except that GXL has lots of developer created add-ins attached to it.

What your code seems to do is it defaults to the standard excel application whereas what I would like it to do is to use this other version instead. That is why I gave you the pathway and name of the other excel application.  
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
You could try something like this.  Just change out the path to the GXL executable in the code.  And also the full paths to the data files that need to be opened, which are "soft coded" into column A.

It passes the files in column A of Sheet1 to the EXE program, and updates the "last launched" column.

Sample workbook attached below.

sshot-389.png
Option Explicit

Sub LaunchAll()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim shell As Object
    Dim ExePath As String
    Dim i As Long
    
    ExePath = "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE"
    
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets(1)
   
    Set shell = CreateObject("WScript.Shell")
   
    With ws
        For i = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
            shell.Run Quote(ExePath) & " " & Quote(.Cells(i, 1)), 7, False
            .Cells(i, 2) = Now()
        Next i
    End With
    
    wb.Save
End Sub

Function Quote(s)
   Quote = Chr(34) & s & Chr(34)
End Function

Open in new window

Launcher.xlsm


»bp

Author

Commented:
This is great. This works in opening up the 'GXL' excel application. One question though, when it opens a window appears that requires me to click 'OK'. How would I do that using VB code?

Author

Commented:
Also, is it possible to produce some VB code so that when this GXL excel application and the specified workbook file appear the macros associated with it can be run?

For example, let's say that 'LaunchAll' code is found in my 'Scheduler' file such that running it opens up the first workbook file as highlighted above called 'DataWorkbook1'. What I would now like is for the Scheduler file to run or activate the VB code associated with 'DataWorkbook1' called 'Run_All'. Is it possible to do that? 
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Can you take a screen shot of the message box?

Does it come up before the GXL main screen comes up or after?

Can you just press enter once it is displayed to continue?


»bp
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
As far as automating the GXL application I have no knowledge of that program.  Is there documentation online someplace about it?  Since it's not Excel normal Excel automation isn't going to work.

You might also want to research command line parameters that the GXL exe supports, there might be ways to pass a starting macro.  And also prevent the message box confirmation - worth looking.


»bp

Author

Commented:
Due to privacy reasons I am not sure I would be allowed but what I can say is that when I open GXL the green excel box appears followed by this window that shows information such as my user name and environment status. It has two buttons 'Cancel' and 'OK'. The 'OK' button is highlighted by default so all I need to do is hit enter and the excel spreadsheet appears.

Author

Commented:
Alright, forget about GXL and just think of it like you would think of regular excel.

Regarding the OK button is there not some code that some how 'click's' a button so the user does not have to do it manually?

As for the code in the other spreadsheet, is there no way to activate or run it from the Scheduler file?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
So I'm a bit confused at this poiint in terms of what GXL actually is?  Is it an addon of some sort that is "hosted" within Excel?  If so then I'm not sure why it has it's own EXE?  Can you point to any web documentation / information on the product?


»bp
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Regarding the OK button is there not some code that some how 'click's' a button so the user does not have to do it manually?

In "normal VBA", the SENDKEYS method can be used to send keystrokes to the active window.  But not to click buttons.  So as long as sending an ENTER would work then that might be an option.  But the popup would need to have focus when the VBA code executes the SENDKEYS, which can sometimes be tricky.  You may need to add some delay after initiating the start of the opening of the workbook.

Application.SendKeys method (Excel) | Microsoft Docs

If the data file you are opening is indeed a real Excel workbook, then you could add VBA code in it to execute on load of the file.  That is another way you could cause code to execute every time that file is loaded.

As for the code in the other spreadsheet, is there no way to activate or run it from the Scheduler file?

With Excel, a command line switch /m can be used to run a macro when the specified workbook is opened.


»bp

Author

Commented:
GXL is just regular excel with lots of add-ons that have been developed by a tech team. For example it includes features such as complex mathematical functions and allows me to link to Reuters and Bloomberg market data. As a user you would see no difference in terms of use until you tried to perform those complex calculations or functions.

Author

Commented:
Could you provide an VB example of that command line switch?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Could you provide an VB example of that command line switch?
Sorry, getting my Office products mixed up.  /m exists for Word but not for Excel.  So it doesn't look like there is a way to execute a VBA procedure via the Excel command line.

Are your "data files" true Excel files, if so you could ad an OnLoad trigger to each of them to execute the desired VBA.


»bp

Author

Commented:
Yes, they are true excel files so how would I use the OnLoad trigger?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Yes, they are true excel files so how would I use the OnLoad trigger?

Automatically run a macro when opening a workbook - Office Support


»bp

Author

Commented:
Sub PNL_RUN()
 
Dim ws As Worksheet
Dim wb As Workbook
Dim shell As Object
Dim ExePath As String
Dim ExcelApplication As Excel.Application
Dim ExcelWorkbook As Excel.Workbook
Dim MainBook, FolderName(3), currbook, activebook, MacroName(3), SheetName(3) As String
Dim x As Integer
 
ExePath = "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\GXL\GXL 64-bit"
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
Set shell = CreateObject("WScript.Shell")
 
ActiveSheet.Calculate
x = 1
 
MacroName(1) = "Run_all"
SheetName(1) = "CONTROL"
FolderName(1) = "\\LondonShared.Wellsfargo.com\L_EMEA_Shared_Data\London Middle Office\RISK and P&L\Rates\Favourite\"
 
MacroName(2) = "Run"
SheetName(2) = "Control"
FolderName(2) = "\\LondonShared.Wellsfargo.com\L_EMEA_Shared_Data\London Middle Office\RISK and P&L\Rates\Favourite\"
 
MacroName(3) = "run_update"
SheetName(3) = "Save GXL"
FolderName(3) = "\\LondonShared.Wellsfargo.com\L_EMEA_Shared_Data\London Middle Office\RISK and P&L\Rates\Favourite\"
 
MainBook = ActiveWorkbook.Name
 
Do Until Workbooks(MainBook).Sheets("CONTROL").Range("PNL").Offset(x - 1, 0).Value = Empty
If Workbooks(MainBook).Sheets("CONTROL").Range("RUN").Offset(x - 1, 0).Value = "NO" Then
GoTo LINE1:
End If
Workbooks(MainBook).Activate
 
Workbooks.Open FileName:=(FolderName(x) & Workbooks(MainBook).Sheets("CONTROL").Range("PNL").Offset(x - 1, 0).Value)
currbook = ActiveWorkbook.Name
 
    Workbooks(currbook).Sheets(SheetName(x)).Range("A1").Value = "YES"
    ActiveWorkbook.Close savechanges:=1
    With ws
    shell.Run Quote(ExePath) & " " & Quote(FolderName(x) & Workbooks(MainBook).Sheets("CONTROL").Range("PNL").Offset(x - 1, 0).Value)
    Application.Wait (Now + TimeValue("0:01:00"))
    Application.Run "'" & currbook & "'!" & MacroName(x)
    End With
    GoTo LINE1
 
Workbooks(MainBook).Activate
LINE1:
x = x + 1
 
Workbooks(MainBook).Activate
Loop
 
MsgBox "Morning automated processes are now complete.", vbExclamation + vbOKOnly, "AUTORUN COMPLETE!"
 
End Sub

Author

Commented:
Alright, so above is a screen shot of the excel spreadsheet called Morning Task Scheduler and the code that is associated with it

To summarize the process, I open up a spreadsheet called Task Scheduler, I run the macro button called Morning Reports Production, which then calls the code PNL_RUN. Essentially what happens in the code is that it is meant to open the files called Reuters Market Data, Quote Set Emea and Quote Set Close in sequence in a brand new excel application called GXL, which is just like a regular excel but with lots of function add-ins. As a result it takes a few seconds to open but as each spreadsheet has its own VB code I would like that code to be run on opening and then on finishing it should be left open and the next line of the Task Scheduler code should then  be run.

Right now when I try to do it there is a debug message because I cannot get the VB code to do what I described above.

Could you please have al look at the code and suggest ways how I could improve it?

Thanks. 
Test your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.