Open Excel specific Sheets 1

[VKZIMM]

I have the following script I use to open and excel spreadsheet from within Mas90. The client has now asked if I can only open specific tabs (Sheets) like 3 to 5.
Is this possible? Thank you in advance.
Dim fs
Dim ss

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FileExists("C:\\" & ML_UDF_SOH_SPREADSHEET & ".xls") Then
Set ss = CreateObject("WScript.Shell")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(SHEET3)
ss.run "C:\\" & ML_UDF_SOH_SPREADSHEET & ".xls"
Set ss = nothing

Else MsgBox"NO SPREADSHEET EXISTS FOR " & ML_UDF_SOH_SPREADSHEET & ".XLS",0, "MAS 90 ORDER SPREADSHEET"

End If

Set fs = nothing

 

[markdmac]

No, to the best of my knowledge it is not possible to open a specific sheet in a workbook and not open the entire workbook. You can only select the active sheet.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[HarleyQuinn]

If you only want it to appear that you're opening particular sheets then you could set the other sheets visible property to false, something like:

objExcel.ActiveWorkbook.Sheets("SheetToHide").Visible = False

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[markdmac]

Excellent suggestion HarleyQuinn. Have a star from me.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[HarleyQuinn]

Thanks Mark

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VKZIMM]

I am a scripting newbie.
I tried adding the following to run a test and I get an error message.
objExcel.ActiveWorkbook.Sheets("Sheet2").Visible = False

vbscript error number 424
description object required ObjExcel

Thank you in advance for your help.

 

[HarleyQuinn]

Where in your code did you add it?

Where is it that you set objExcel in your current code to an instance of Excel?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VKZIMM]

This is the last place I tried it. I also tried it before the ss.run and after the Set fs Thank you again

' language VBScript
'
' form variables
' ML_ORDER
'
'
' system variables
' MAS_SCR_CMP : company code [XXX]
' MAS_SCR_USR : user code [XXX]
' MAS_SCR_MOD : module code [X/X]
' MAS_SCR_DTE : current app date [MMDDYYYY]
' MAS_SCR_LIB : library [..\xx\xxxxxx.LIB]
' MAS_SCR_PNL : panel [PNL_xxxxxxx]
' MAS_SCR_OBJ : control [BT_LINK_x]
' MAS_SCR_CS : 1 if running MAS 200 on client
' MAS_SCR_DBG : 1 to show script before execution
'
MAS_SCR_DBG = 0

Dim fs
Dim ss

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FileExists("C:\\" & ML_UDF_SOH_SPREADSHEET & ".xls" ) Then
Set ss = CreateObject("WScript.Shell")
ss.run chr(34) & "C:\\" & ML_UDF_SOH_SPREADSHEET & ".xls" & chr(34)
Set ss = nothing
objExcel.ActiveWorkbook.Sheets("Sheet2").Visible = False
Else MsgBox"NO SPREADSHEET EXISTS FOR " & ML_UDF_SOH_SPREADSHEET & ".XLS",0, "MAS 90 ORDER SPREADSHEET"

End If

Set fs = nothing

 

[HarleyQuinn]

Though you had objExcel in your code before, you never actually set it to anything, try something along the lines of:

Dim objExcel

Set objExcel = CreateObject("Excel.Application")

objExcel.Workbooks.Open ("PathToYourExcelDocument.xls")

objExcel.Visible = True

objExcel.ActiveWorkbook.Sheets("Sheet2").Visible = False

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[VKZIMM]

Thank you so much, I got it to work.