Excel opens worksheets HIDDEN 2

[IFRs]

When I use VB6 to open an Excel worksheet, and then go to Excel, the worksheet is hidden and I have to unhide it to see it. Next time I use Excel to open the worksheet it is hidden.

How do I open it with VB6 UnHidden so the user can see my VB6 program manipulate it?


How do I make the worksheet open normally with Excel next time?

My code for opening it is Set XLObj=GetObject("Drive\Path\Filename.xls&quot

 

[LPlates]

Try this..

Private Sub Command1_Click()
Dim Name As String
Set Excel = CreateObject("Excel.Application&quot
Excel.Visible = True
Excel.WindowState = 2
Excel.Workbooks.Open "Drive\Path\Filename.xls"
Name = Excel.ActiveWorkbook.Sheets(1).Name
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(Name)
End Sub

 

[IFRs]

LPlates -
Thanks for the suggestion. I will try it as soon as I can, probably this afternoon.

Is this for a VB6 application or VBA?

I am writing a VB applicaiton that will be compiled into a stand-alone exe file and run under Windows.

 

[LPlates]

VB6

 

[IFRs]

I must be extra dim this morning!!

Dim Name As String
Set Excel = CreateObject("Excel.Application&quot
Excel.Visible = True
Excel.WindowState = 2
Excel.Workbooks.Open "D:\In\Parts Fab.xls"
Name = Excel.ActiveWorkbook.Sheets(1).Name
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(Name)

The It gets to the Name line an errors with "Object variable or With block variable not set"

When I go to Excel, the Parts Fab.xls is hidden.

Can you explain what the WindowState line does and where I can get documentation for this and the rest?

 

[LPlates]

No I am, lol I left out two declares...

Option Explicit

Dim Excel As Object
Dim ExcelSheet As Object

try setting the windowstate to 0

 

[LPlates]

Actually leave it as it is and the following 3 lines of code will solve your issue (without the error of course)

 

[IFRs]

You mean the previous 3 lines? So it looks like this?


Sub OpenPartsFab()

Option Explicit
Dim Excel As Object
Dim ExcelSheet As Object
Dim Name As String
Set Excel = CreateObject("Excel.Application&quot
Excel.Visible = True
Excel.WindowState = 2
Excel.Workbooks.Open "D:\In\Parts Fab.xls"
Name = Excel.ActiveWorkbook.Sheets(1).Name
Set ExcelSheet = Excel.ActiveWorkbook.Sheets(Name)

End Sub

I don't need to replace "Name" with anything?

 

[johnwm]

Which 3 lines would they be then?

Also I would recommend not using Name as a variable.

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

 

[LPlates]

I was refering to the windowstate and the 3 lines of code that follow the line referencing the windowstate, but was not very clear

 

[MartinBell12]

Don't forget when you have finished with your Excel sheet to add

"Set [Excel Sheet] = nothing"
Otherwise your reference to Excel will still be open and each time you run the code another reference will be created

 

[combo]

Do not use ActiveWorkbook - it can be Nothing. Rather:

Excel.Workbooks.Open "D:\In\Parts Fab.xls"
Set ExcelSheet = Excel.Workbooks(1).Sheets(1)

Excel application whas set to be visible. But if you still can't see the workbook, it can have window hidden. In this case:

Excel.Workbooks(1).Windows(1).Visible=True

combo

 

[IFRs]

Will the folloing cause trouble?
Is it necessary to include all the Dim Statements and Option Explicit?

Private Sub OpenPartsList()
Set XLObj=GetObject("D:\In\Parts Fab.xls&quot
XLObj.Windows(1).Visible = True
' Manipulate the worksheet
Set XLObj = Nothing
End SUb

 

[combo]

If there are no opened excels, GetObject will create one, but invisible. You need to make it (excel) visible, so to have it always visible, XLObj.Visible=True is necessary.
Inside excel, you can hide workbook window (Window>Hide), it will stay open but invisible. VB/VBA equivalent for this is Windows(...).Visible = True/False. Normally, windows are visible, so, if you can from excel open workbook and see it, you can leave windows as they are.

As for the second question - no, it isn't, but dims speed up programmes, and together with Option Explicit help in tracing errors in variable names.

combo

 

[IFRs]

Correct me if I am wrong here:

If GetObject creates a worksheet and VB saves it, the worksheet will be invisible and Excel will always open it Hidden.

If GetObject opens an existing worksheet and VB saves it, the worksheet will retain it's original Hidden/UnHidden status.

If GetObject opens an existing worksheet, VB makes it invisible and then VB saves it, the worksheet will now open Hidden in Excel.

If GetObject opens an existing worksheet, VB makes it visible and then VB saves it, the worksheet will now open UnHidden in Excel.

 

[combo]

In the first and third case - no, other - yes.
Excel, as application, is not a workbook (but a parent). A hidden application (Visible=False) will be invisible with all the workbooks. But still the workbook (precisely - its window or some of windows) can have Visible=True.
GetObject("D:\In\Parts Fab.xls&quot returns excel application. If a new instance is created, it is invisible.
Excel, also invisible, creates and saves workbooks with visible window. Without separate command, a workbook created/opened and saved by invisible application will be opened visible by excel user.

combo