Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Issues with ExportBiff - QV 11.20.12354x64

When I start an export, in a loop Excel was not produced with large amounts of data (?). The loop is executed without error message.

I start the code block individually, the Excel was generated. Adding "ActiveDocument.GetApplication.Sleep 5000" has no effect.

The memory usage is no problem.

Does anyone know this problem?

Thanks for your time!

cheers

Martin

<snip>

ActiveDocument.Fields("Contract_Partner").Clear

set val=ActiveDocument.Fields("Contract_Partner").GetPossibleValues

for i=0 to val.Count-1

  ActiveDocument.ClearAll true

  set XLApp = CreateObject("EXCEL.application")

  XLApp.Visible = FALSE

  set XLDoc = XLApp.Workbooks.Add

  ParentFolder = ServerPfad 

  set objShell = CreateObject("Shell.Application")

  set objFolder = objShell.NameSpace(ParentFolder)

  objFolder.NewFolder val.Item(i).Text

  set MyTable = ActiveDocument.GetSheetObject("CH918")

  path= ServerPfad &  val.Item(i).Text & "\"

  ActiveDocument.Fields("Contract_Partner").Select val.Item(i).Text

  'ActiveDocument.GetApplication.Sleep 5000

  ' Wait for selecting/deselecting Datas in case of big results

  'if val.Item(i).Text = "myCustomer" or val.Item(i).Text="myCustomer2" then ActiveDocument.GetApplication.Sleep 10000

  MyTable.ExportBiff  path & val.Item(i).Text &"_last_6_months_cases" & ".xls"

  writelog "last_6_months|CH918|" & path & val.Item(i).Text &"_last_6_months_cases" & ".xls"   

  'Customer report

  set ASCNSE = ActiveDocument.GetSheetObject("CH920")

  NSEpath= ServerPfad & val.Item(i).Text & "\"

  ActiveDocument.Fields("Contract_Partner").Select val.Item(i).Text

  ASCNSE.ExportBiff  NSEpath & val.Item(i).Text & "_report.xls"        

  writelog "ASCNSE|CH920|" & NSEpath & val.Item(i).Text & "_Customer_report.xls"

  XLApp.Quit

  Set XLApp = Nothing

  Set Table = Nothing

          

next

</snip>

5 Replies
Highlighted
Partner
Partner


Have you tried ActiveDocument.GetApplication.WaitForIdle instead of Sleep?

Highlighted
Not applicable

Hi Jerry,


"ActiveDocument.GetApplication.WaitForIdle" has no effect.


Strangely, no log entry is created.


  MyTable.ExportBiff  path & val.Item(i).Text &"_last_6_months_cases" & ".xls"

  writelog "last_6_months|CH918|" & path & val.Item(i).Text &"_last_6_months_cases" & ".xls"

Is there any way to debug the script? SFAIK this is no longer supported since QV11?!

I add "on error goto 0" to my sub and now the script stops at "waitforidle". Obviously "on error resume next" the default..hmmm.

Highlighted
Partner
Partner

Debug is hard in vbscript.

Your writelog function, is that a function you built yourself? Try verifying that it works by adding a writelog entry at the top of your code.

Highlighted
Not applicable

Jepp, the writelog function works:

edit:

works for SQL Server and build a text file

<snip>

Function writeLog(text)

  aText  = split(text,"|")

  Modul = aText(0)

  Objekt = aText(1)

  Pfad = aText(2)

  Report = ActiveDocument.Name

  

    Set fs = CreateObject("Scripting.FileSystemObject")   

    logpath = "c:\qlikview\Log\"

    logfile = logpath & "Qlikview.log"

    ISODate = DatePart("yyyy", Now) & "-" & right("0" & DatePart("m", Now),2)& "-" & DatePart("d", Now)

    If Not fs.FolderExists(logpath) Then

  fs.CreateFolder logpath

  End If   

    if fs.FileExists(logfile) then

  Set f = fs.OpenTextFile(logfile,8,2)

    else

  Set f = fs.CreateTextFile(logfile, True)

    end if

  If text = "" Then

        f.Write vbCrLf

     Else

       f.WriteLine  ISODAte  & "T" & Time  & "|" & Report & "|" & Modul & "|" & Objekt & "|" & Pfad     

    End If

  

    f.Close

    set f = nothing

    set fs = nothing

      

    Set objConnection = CreateObject("ADODB.Connection")

  objConnection.Open "Provider=SQLOLEDB;Data Source=yourserver;Trusted_Connection=Yes;Initial Catalog=Export"

  sql = "IF OBJECT_ID('Qlikview_Log') is null CREATE TABLE Qlikview_Log (Timestamp DATETIME, Report VARCHAR(255), Module VARCHAR(255), Object VARCHAR(255), Path VARCHAR (255))"

  objConnection.Execute sql

  sql = "INSERT INTO dbo.Qlikview_Log (Timestamp, Report, Module, Object, Path) VALUES ('" & ISODate & "T" & Time & "','" & Report & "','" & Modul & "','" & Objekt& "','" & Pfad &"')"

  objConnection.Execute sql

  objConnection.close

  Set objConnection = nothing

  

End Function

</snip>

have fun 😉 !

Highlighted
Contributor II
Contributor II

Hi Martin,

Did you find any solution to using ExportBiff in Loop Statement? I am facing the same issue. The loop abruptly stops midway at the exportbiff call

Thanks,

Santhya