topic Re: Automating Excel from a Macro (using a call with optional params) in QlikView

Automating Excel from a Macro (using a call with optional params)

justin_morley — Mon, 22 Jul 2013 13:47:01 GMT

Hi,

I'm trying to automate Excel from within a macro in QlikView, but one particular command isn't working for me.

The following code works absolutely fine:

' Get the Object we wish to export

Set QVTable = ActiveDocument.GetSheetObject(shtObj)



    ' Define our temporary file

    dim strFileName

    strFileName = TempFile



    ' Export the data from our current QV object to the temporary file

    QVTable.Export strFileName, "     "



    ' Excel business. Create Application

      Set ExcelObj = CreateObject("Excel.Application")

      ' Open our exported sheet as a new object

      dim wb

      Set wb = ExcelObj.Workbooks.Open(strFileName)

So I know there's no problems with my late bound calls.

However I want to use OpenText rather than Open, as I want to tell it to expect a tab delimited file. Now OpenText has a whole bunch of optional params (according to the API guide at least). If I call it with the optionals removed as follows:

Set wb = ExcelObj.Workbooks.OpenText(strFileName)

this line causes the macro to fail. I've tried calling it with the required parameters supplied in the correct order(obviously substituting constants for real values):

Set wb = ExcelObj.Workbooks.OpenText(strFileName,1,1,1,-4142,False, True )

which also fails.

Anybody have any ideas how to get this working?

Re: Automating Excel from a Macro (using a call with optional params)

marcus_sommer — Mon, 22 Jul 2013 14:15:40 GMT

OpenText isn't QV it is a excel-vba method - search in vba help "OpenText-Methode" and for examples in google.

- Marcus

Re: Automating Excel from a Macro (using a call with optional params)

justin_morley — Mon, 22 Jul 2013 14:22:27 GMT

Thanks for the reply. I do know that - hence the link to the Excel VBA API documentation in my post. I really wanted to get to the bottom of if there is a special format for calling optional params using QlikView's flavour of VBScript, or whether it's just the same as doing it from any other language

A little further investigation reveals that OpenText doesn't have a return type which probably explains my problem. I'll carry on experimenting for now

Re: Automating Excel from a Macro (using a call with optional params)

marcus_sommer — Mon, 22 Jul 2013 14:52:43 GMT

What do you want to achieve with opentext instead only open, what are the advantages?

- Marcus

Re: Automating Excel from a Macro (using a call with optional params)

justin_morley — Fri, 26 Jul 2013 09:24:24 GMT

Hi Marcus,

The advantage of OpenText, is you control how Excel perceives the in-coming document.

If I use Open I occasionally see this:

Cell Header 1 | Cell Header 2 | Cell Header 3

Cell Contents 1Cell Contents 2 | Cell Contents 3 |

Where it randomly strips out the tabs and merges cells together, which is obviously very undesirable indeed.

OpenText seems not to do this in my testing so far. It also seems to be working without parameters so I think I've answered my own question!

Thanks,

Justin