Issues with ExportBiff - QV 11.20.12354x64

[#1]

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>

[jerrysvensson]

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

[#3]

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.

[jerrysvensson]

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.

[#5]

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 😉 !

[santhyamuthu]

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