Excel automation: how to open a blank sheet?

[fred]

Hello,
I am using vbasic and Excel automation.
I do not know how to open the blank sheet.
I tried this:
On Error GoTo nmoExcelApp
Dim moExcelApp As Object
Dim moExcelWBk As Object
Dim moExcelWS As Object
Set moExcelApp = CreateObject("Excel.Application")
If moExcelWS Is Nothing Then
Set moExcelWS =
moExcelApp.Workbooks("blank.csv").Worksheets("blank")
End If
That does not work.
moExcelWS is Nothing
but when I do this:
If moExcelWS Is Nothing Then
Set moExcelWS = moExcelApp.Workbooks.Open(App.Path &
"\blank.csv")
End If
it appears that moExcelWS references to the sheet, but the sheet is not
displayed (visible) on screen.
What to do to have the blank spreadsheet opened and visible?
Thanks,
Fred

 

[fred]

I tried it.
There is not an error, but there is not any sheet displayed.
Thanks,
Fred

 

[JLGWhiz]

The VBA syntax is:

ActiveWorkbook.Worksheets.Add After:=Sheets(Sheets.Count)

Which would put it after the last existing sheet in the workbook.

 

[fred]

I know the sheet is out there, because when I try:
moExcelWS.UsedRange.Rows.Count
the return is 1.
In fact why 1 not 0? If the sheet is blank there should not be any used rows
I think.
In any case I cannot see any sheet displayed.
Fred

 

[JLGWhiz]

P.S.

The new sheet is automatically the active sheet and Excel gives it a name
based on the number of sheets that have been created in the workbook. So,
to identify the sheet you would probably want to give it a name that you
recognize.

ActiveSheet.Name = 'Give it a name

 

[fred]

But there is nothing active yet, or there is?
I need to start from scratch: no workbooks (sheets) are in existance.
Fred

 

[fred]

How to make it visible?

P.S.

The new sheet is automatically the active sheet and Excel gives it a name
based on the number of sheets that have been created in the workbook. So,
to identify the sheet you would probably want to give it a name that you
recognize.

ActiveSheet.Name = 'Give it a name

 

[Jim Cone]

A new Excel application created using Automation is not visible.
You need to use... moExcelApp.Visible = True
--
Jim Cone
Portland, Oregon USA




"fred" <[email protected]>
wrote in message
How to make it visible?

 

[Dave Peterson]

Jim Cone answered your question by adding:

moExcelApp.visible = true

But for lots of things (testing not included), you don't have to see excel to
work with it.

And It's one of the oddities of excel.

It will report 1 row, 1 column, or one cell in the .usedrange.rows.count,
..usedrange.columns.count, or .usedrange.cells.count for an empty worksheet.

 

[Dave Peterson]

ps. I bet you have lots of instances of excel running (invisible to you).

Close the visible instance (if there is one)
And use the taskmanager (alt-ctrl-delete) to delete the running excel.exe's in
the Process tab.

(or reboot your machine to kill all of them.)

 

[Bob Butler]

ps. I bet you have lots of instances of excel running (invisible to you).

Close the visible instance (if there is one)
And use the taskmanager (alt-ctrl-delete) to delete the running
excel.exe's in
the Process tab.

(or reboot your machine to kill all of them.)

In the immediate window:

set x=getobject(,"Excel.Application"):x.visible=true

repeat until it fails; the advantage is that it lets you see what is in each
instance and save as needed

 

[Dave Peterson]

I used to screw things up quite a bit.

I have a shortcut to a .vbs file on my desktop. It consists of this:

dim myXL
On Error Resume Next
Set myXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
msgbox "Excel is not running"
else
myxl.visible = true
end If
On Error GoTo 0
Set myxl = nothing

=====
I click on it, close any newly visible excel instances and reclick until I get
that "not running" message.

=============
I didn't try your version in the immediate window, but if that's the immediate
window of Excel's VBE, isn't there a chance that you'll get the same instance
each time.

I've never been able to control separate instances of excel by using getobject.

 

[Bob Butler]

I used to screw things up quite a bit.

I have a shortcut to a .vbs file on my desktop. It consists of this:

dim myXL
On Error Resume Next
Set myXL = GetObject(, "Excel.Application")
If Err.Number = 429 Then
msgbox "Excel is not running"
else
myxl.visible = true
end If
On Error GoTo 0
Set myxl = nothing

=====
I click on it, close any newly visible excel instances and reclick until I
get
that "not running" message.

=============
I didn't try your version in the immediate window, but if that's the
immediate
window of Excel's VBE, isn't there a chance that you'll get the same
instance
each time.

I've never been able to control separate instances of excel by using
getobject.

That's correct; I was reading this in m.p.vb.general.discussion and did not
notice the cross-post to excel.programming. My comment applies to the
immediate window within VB 6, not VBA inside Excel.

 

[Dave Peterson]

Ahh.

I didn't notice that it was cross-posted.