Automation and object libraries

  • Thread starter Thread starter Cecilia Reyes
  • Start date Start date
C

Cecilia Reyes

... i guess.
well i have the following code in an access module:

To open a excell worksheet from access through Automation

dim HojaExcell as Excell.Worksheet
set HojaExcell = CreateObject("Excell.Sheet")

but it seems to be wrong i dont know why.
i tried also

Dim appXL As Excel.Application
Dim wbk As Workbook, wks As Worksheet

Set appXL = CreateObject("Excel.Application")
Set wbk = appXL.Workbooks.Add
Set wks = wbk.Worksheets(1)

and i know it works but maybe somebody could explain why
it doesnt work?
thanks in advance.
CEcilia.
 
Hi Cecilia,

Little things like "Excel" vs "Excell" and "Sheet" vs "Worksheet" make
a difference, of course. But as far as I know the main thing is that an
Excel worksheet object can only exist as part of a workbook and cannot
be created in isolation.
 
Hello Cecilia

You haven't explained what it is that doesn't work. Is there any error
message?

Your first example will fail because you have spelled "Excel" incorrectly,
with two "L"s.

The second example should work, but perhaps the problem is that you don't
see the open Excel window? This is because you need to make the application
window visible. It is probably also a good idea to place it under user
control:

appXL.Visible = True
appXL.UserControl = True
 
Hello and thank you for replying,
for this example:

dim HojaExcel as Excel.Worksheet
set HojaExcel = CreateObject("Excel.Sheet")

there´s an error: <<Type Mismatch>>

i dont know why but i think it should work since the
variable was already created?

and then how do i do to find out which are the objects
available to create from code?
 
Hi and thanks for the reply.
For this example:
dim HojaExcel as Excel.Worksheet
set HojaExcel = CreateObject("Excel.Sheet")

Do i have to set the variable like this?
set HojaExcel= CreateObject("Excel.Worksheet")
and then set another variable for a sheet?
 
Try something like this:

Dim oXL As Excel.Application
Dim oWbk As Excel.Workbook
Dim oSht As Excel.Worksheet

Set oXL = CreateObject("Excel.Application")
Set oWbk = oXL.Workbooks.Add
Set oSht = oWbk.Worksheets(1)

'Do things with the worksheet
...
...

'Save the workbook if you want to
oWbk.SaveAs "D:\Folder\File.xls"

'Close it
Set oSht = Nothing
oWbk.Close False 'False argument needed
' if you want to close without saving
Set oWbk = Nothing

'Make certain that there are no other workbooks
'open that might stop Excel closing
Do While oXL.Workbooks.Count > 0
oXL.Workbooks(1).Close False
Loop
oXL.Quit
Set oXL = Nothing
 
thanks!
-----Original Message-----
Try something like this:

Dim oXL As Excel.Application
Dim oWbk As Excel.Workbook
Dim oSht As Excel.Worksheet

Set oXL = CreateObject("Excel.Application")
Set oWbk = oXL.Workbooks.Add
Set oSht = oWbk.Worksheets(1)

'Do things with the worksheet
...
...

'Save the workbook if you want to
oWbk.SaveAs "D:\Folder\File.xls"

'Close it
Set oSht = Nothing
oWbk.Close False 'False argument needed
' if you want to close without saving
Set oWbk = Nothing

'Make certain that there are no other workbooks
'open that might stop Excel closing
Do While oXL.Workbooks.Count > 0
oXL.Workbooks(1).Close False
Loop
oXL.Quit
Set oXL = Nothing





Hi and thanks for the reply.
For this example:
dim HojaExcel as Excel.Worksheet
set HojaExcel = CreateObject("Excel.Sheet")

Do i have to set the variable like this?
set HojaExcel= CreateObject("Excel.Worksheet")
and then set another variable for a sheet?
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top