Automating Excel from a Macro (using a call with optional params)
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
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
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 )
Re: Automating Excel from a Macro (using a call with optional params)
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