topic Open an Excel object in QlikView

Open an Excel object

Mon, 19 Jul 2010 16:50:43 GMT

Hi all,

Do you know why when I execute this only macro line :

set ExcelApp = createobject("Excel.Application")

Why an excel application open and close directly ? (I see it in task manager) ?

thanks

Open an Excel object

Mon, 19 Jul 2010 21:27:42 GMT

Is that the end of your macro? When your macro ends, it could release the Excel app object.

You are able to open an Excel object and manipulate it via macro. Here's a sample:

Sub Excel_OLE_Automation
 Set oXL=CreateObject("Excel.Application")
 f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
 If f_name="False" then
 'user cancelled out of dialog box
 Set oXL=nothing
 Exit sub
 End If
 ActiveDocument.Variables("vFileName").SetContent f_name, true
 Set oWB=oXL.Workbooks.Open(f_name)
 Set oSH=oWB.Worksheets.Item(1)
 oSH.Range("A2") = "12345"
 oWB.Save
 oWB.Close
 Set oSH=nothing
 Set oWB=nothing
 Set oXL=nothing
End Sub

Open an Excel object

Wed, 21 Jul 2010 08:46:09 GMT

"When your macro ends, it could release the Excel app object."

Really It's not so common !

Open an Excel object

Wed, 21 Jul 2010 17:14:03 GMT

No, not really, I was able to open an Excel object and leave it open after the macro ended.

What are you trying to accomplish with your macro? I think you need to do a little more to fully open the Excel and keep it open. Are you trying to start Excel and create a new file? Do you just want to open Excel with no file?

Here is the minimum I was able to use to get Excel to open and stay open after the macro ended:

Sub SimpleAuto
 Set oXL=CreateObject("Excel.Application")
 oXL.Visible = True

 f_name=oXL.GetOpenFilename("All Files (*.*),*.*",,"Select file",False)
 If f_name="False" then 'user cancelled out of dialog box
 Set oXL=nothing
 Exit sub
 End If

 Set oWB=oXL.Workbooks.Open(f_name)
 Set oSH=oWB.Worksheets.Item(1)
End Sub

Open an Excel object

Thu, 22 Jul 2010 10:35:24 GMT

Finally, I have no problem!

Usually when I use a COM object for Excel (in a vbs or other), the Excel application remains active if I do not close it.

So when I check only the line CreateObject ("Excel.Application") and I see this Excel application creating and immediately quit ... I am surprised and check if there is not a bug on my computer! I can not imagine that the VB engine in QV would close it automatically!

So there is no problem (finally)