Question about VBS

User avatar
Nick Vittum
4StarLounger
Posts: 475
Joined: 21 Feb 2020, 21:27
Location: Vermont (USA)

Question about VBS

Post by Nick Vittum »

. . . but first: is this even the correct place to ask about VBS?
—Nick

I’m only an egg (but hard-boiled)

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about VBS

Post by HansV »

Yes, go ahead.
Best wishes,
Hans

User avatar
Nick Vittum
4StarLounger
Posts: 475
Joined: 21 Feb 2020, 21:27
Location: Vermont (USA)

Re: Question about VBS

Post by Nick Vittum »

Oh, good.

I tried to set up a VBS script that would open an Excel workbook. Maybe no need to go into "why?" here, but the code was this:

Code: Select all

Option Explicit

Dim xlApp, xlBook
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\Nick\Desktop\2020\2020", 0, False)
It does work, but when the file opens this way, the ribbon is hidden and apparently disabled (that is to say, if I stick a line of code in a module to show the ribbon, and try to run it, nothing happens). The ribbon behaves normally if I open the file without using the script.
Is there something in that script that's disabling the ribbon? Or something I need to add to enable it?
—Nick

I’m only an egg (but hard-boiled)

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about VBS

Post by HansV »

How about

Code: Select all

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Users\Nick\Desktop\2020\2020", False, False)
xlApp.Visible = True
When I run this (with a workbook on my PC of course), Excel opens normally.
Best wishes,
Hans

User avatar
Nick Vittum
4StarLounger
Posts: 475
Joined: 21 Feb 2020, 21:27
Location: Vermont (USA)

Re: Question about VBS

Post by Nick Vittum »

That does indeed work. Thank you. :~)

So apparently the .Visible line is necessary— even though the app was visible before, only the ribbon was not. Can you tell me why?

And what does the first "False" where there was a zero before mean? I tried changing the zero to other numbers, but got no results that way.
—Nick

I’m only an egg (but hard-boiled)

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Question about VBS

Post by HansV »

CreateObject creates an invisible instance of the application.
If you want to get the already running instance, use

Code: Select all

Set xlApp = GetObject(, "Excel.Application")
The second argument of Workbooks.Open is UpdateLinks and it is of type Boolean, i.e. its value can be either True or False. 0 is equivalent to False.
Best wishes,
Hans

User avatar
Nick Vittum
4StarLounger
Posts: 475
Joined: 21 Feb 2020, 21:27
Location: Vermont (USA)

Re: Question about VBS

Post by Nick Vittum »

Thank you !!
—Nick

I’m only an egg (but hard-boiled)