PDA

View Full Version : Solved: Making Excel invisible on opening



d4vem
06-13-2006, 01:01 PM
I have created a auto open command that makes excel invisible but on opening the file you see the application briefly then it becomes invisible. Is there anyway of opening a file that prevents the excel application from being viewed in the initial instance? What I am trying to achieve is masking excel as the source application. Do I have to open a new session of excel each time I open the file (via shortcut) and how can I do that?

Ivan F Moala
06-13-2006, 08:42 PM
Open th file via a script VBS

eg



Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\ExcelFiles\Useful\Hide_Excel.xls")


Just paste above into Note Pad and rename with extension vbs
Point your shortcut to the script

MountainVogu
06-13-2006, 09:43 PM
Hey Ivan,

Maybe you can tell me, why when you use vb script or a vb executable created using visual studio to do the same thing that you have just demonstrated. The macro security message does not appear when the workbook being opened contains macros.

Not that I am complaining as I exploit this "bug" ? all the time.

Cheers

Mv

d4vem
06-14-2006, 02:35 AM
Ivan thanks for the tip it worked a treat.

Justinlabenne
06-14-2006, 04:39 AM
MountainVogu:

Don't quote me but I read on MSDN that it is because the calling code (vb6, vbs, etc...) doesn't handle the startup messages that excel sends when opened.

IE: Excel opened on it's own goes through certain procedures (kind of like an Auto_Open of it's own) that handle loading add-in's, security warnings, etc.. but when opened via automation, it doesn't get run.

Beyond that, I don't know.

circaa
06-14-2006, 10:35 AM
Ivan,

I'm sorry I'm not quite sure of what to do to make excel invisible...what do you mean by " Point the shortcut to the script " ??

Joey

d4vem
06-14-2006, 01:15 PM
As per Ivans post above create the text in note pad and save as a .vbs file. Then when saved select the file in explorer and create a shortcut. Copy this short cut where ever you want. When happy that it is in the right directory you can select propetries and change the icon (if you wish). Open the file and the route of the file will be opened without you knowing that excel has started

Cyberdude
06-14-2006, 01:22 PM
I haven't tried it yet, but can I invoke the script from an executing VBA macro that ends by opening another workbook before it exits? Normally I use a VBA "Open" statement for that purpose.
If the answer is yes, how does one invoke a VBS file from a VBA macro?

stanl
06-14-2006, 01:34 PM
how does one invoke a VBS file from a VBA macro?

Microsoft has a free script wizard that allows you to create .wsc files which can be a mixture of vbscript/jscript. To avoid having to register a .wsc, use GetObject() rather than CreateObject(). Should work within a macro.
.02
Stan

stanl
06-15-2006, 04:27 AM
To avoid having to register a .wsc, use GetObject() rather than CreateObject(). Should work within a macro.


Sorry, I meant to include an example, but Alberto dumped 6-9 inches of rain on Raleigh in about 2-3 hours. All the streets around our house were closed for flooding - bailed water in a 5-gallon pail for 4 hours - as we came within 1/8 inch of losing our downstairs. Anyway, bygones - sun is out now.

cut and paste this code in to a file named tn.wsc


<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
description="Tn"
progid="Tn.WSC"
version="1.00"
classid="{9a3ef45a-5375-4e14-833f-a8793ab9e519}"
>
</registration>
<public>
<method name="GetTn">
<PARAMETER name="var"/>
</method>
</public>
<implements type="Behavior" id="Behavior"/>
<script language="VBScript">
<![CDATA[
function GetTn(var)
GetTn = TypeName(var)
end function
]]>
</script>
</component>


The script wizard takes care of assigning the classid.

Now open a spreadsheet and enter this macro [ for simplicity sake I hard-coded the location of the .wsc, but it consists of "script:" & path & filename.wsc ] and adjust for your situation.



Sub testwsc()
cWsc = "script:c:\test\tn.wsc"
Set Var = ActiveWorkbook.ActiveSheet
Set oWsc = GetObject(cWsc)
MsgBox oWsc.GetTn(Var)
End Sub


When run, it should display the TypeName [WorkSheet]. Not particulary impressive, but since the .wsc is an ascii file, you can add functions on the fly with elementary file i/o. It is also a neat way to work with Jscript arrays, or other activex components.

Stan

circaa
06-26-2006, 12:12 PM
Hi !
I'm sorry, i just don't see how it works, I write any code (that works in VBA) in the note pad, I save it as a .vbs file. And when I open that .vbs file the code is executed. Is that it ?? I've tried it and nothing happens. I must forget something...anyone can help me out here ??


Joey

Justinlabenne
06-26-2006, 02:14 PM
If your looking to just verify that vbs scripts work, paste this line of code into notepad and save it as Test.vbs. Double-Click on it an the message box will show up.

msgbox "Hello World!",64,"New Script"

If you want to test just opening up an Excel file, paste this code in, but you must change the path of the excel file to the file you want to open:

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\YourFolderName\YourExcelFile.xls")
objExcel.visible=true

Set objWorkbook = Nothing
Set objExcel = Nothing

circaa
06-27-2006, 05:30 AM
Thanks a lot for your answer Justin.

When I double click my .vbs file, the code isn't executed. The msgbox doesn't show. But if I right click it and select "open with" and then "Microsoft windows Based Script Host" it works. So I set the file to always be opened with it.

My concern is, i'm creating an application that will be used on other computers in our network. Do you know if they'll have to change the "open with" property too ??

Thanks again

Joey