Possible selection within vb

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I use the following vb script to export into one workbook

SUB xlsExport
DIM varDate
DIM d
DIM xlApp
DIM xlBook
DIM xlSheet
DIM strSheetName
DIM FieldName
DIM ff
DIM v
DIM varAutomation
SET v = ActiveDocument.Variables("vTriggerMacro")
  varAutomation = v.GetContent.STRING
IF varAutomation <> 1 THEN
  confirmation = MSGBOX ("Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"",  36, "Export Confirmation")
  IF confirmation = 7 THEN
  EXIT SUB
  END IF
END IF
SET d = ActiveDocument.Variables("vDate")
  varDate = d.GetContent.STRING


SET ff = ActiveDocument.Variables("vFieldName")
  FieldName = ff.GetContent.STRING


SET xlApp = CREATEOBJECT("Excel.Application")


  xlApp.Visible = FALSE
SET xlBook = xlApp.Workbooks.Add
SET xlSheet = xlBook.Worksheets("Feuil1")
SET Doc = ActiveDocument
SET Field = Doc.Fields(FieldName).GetPossibleValues


FOR i=0 to Field.Count-1
    XLSFile = "c:\export-email-automation\ExcelReport_"&varDate&i&".xls"
  Doc.Fields(FieldName).Clear
  Doc.FIelds(FieldName).SELECT Field.Item(i).Text
' Doc.GetApplication.WaitForIdle
  Doc.GetSheetObject("CH461").CopyTableToClipBoard TRUE
  xlApp.ActiveSheet.Paste
  xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireColumn.AutoFit
  xlApp.Worksheets(xlApp.ActiveSheet.Index).Cells.EntireRow.AutoFit
  strSheetName = Field.Item(i).Text
  xlApp.ActiveSheet.Name = strSheetName
  IF(i<Field.Count-1)THEN
  IF(i>=2)THEN 
  xlApp.ActiveWorkbook.Worksheets.Add
  END IF
  IF(i<2) THEN
  xlApp.Worksheets(xlApp.ActiveSheet.Index +1).SELECT
  END IF
  END IF
  xlBook.SaveAs XLSFile, 56
NEXT
Doc.Fields(FieldName).Clear
xlApp.DisplayAlerts = FALSE


xlBook.Close 
IF varAutomation <> 1 THEN
MSGBOX "Excel export is complete!",64,"[Insert Company Name] Business Intelligence"
END IF
END SUB

How to modify the script in order to

get a single sheet in the workbook and save it
get a multiply workbooks within the possible selections of fieldname

topic Possible selection within vb in QlikView

Possible selection within vb