Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

lucas4bi
Contributor

Export multiple tables on excel (dynamic rows)

Hello everyone,

i have a question: is there a way to export multiple tables to excel so that everytime the macro reads the last written row on the worksheet to restart writing the tables from that row?

i need this because the rows number on each qv table may change everytime.

thank you very much

1 Reply
Not applicable

Export multiple tables on excel (dynamic rows)

Hi,

I have this example.



Function Correos()

Set Doc = ActiveDocument
Set DocProp = Doc.GetProperties
Directory = DocProp.MyWorkingDirectory

Doc.GetApplication.Refresh

Set AppExcel = CreateObject("Excel.Application")

AppExcel.Visible = True

AppExcel.WorkBooks.Add()

Set Gra01 = Doc.GetSheetObject("CH16")
Set Gra02 = Doc.GetSheetObject("CH17")

Set Txt01 = Doc.GetSheetObject("TX13")

// ***************** Se realiza filtro de Negocio y Dummy **********************************************
ActiveDocument.ClearAll false

ActiveDocument.Fields("Cia").ToggleSelect "010"
ActiveDocument.Fields("Cia").ToggleSelect "100"
ActiveDocument.Fields("Cia").ToggleSelect "500"
ActiveDocument.Fields("Cia").ToggleSelect "700"

ActiveDocument.getApplication.sleep 5000

AppExcel.Sheets.Add()

Txt01.CopyTextToClipboard
AppExcel.ActiveSheet.Cells(1,3).Activate
AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Name = "Stock PT"
AppExcel.ActiveSheet.Cells(4,1).Activate
Gra01.CopyTableToClipboard True
AppExcel.ActiveSheet.Paste
AppExcel.ActiveSheet.Columns("B").Delete

AppExcel.ActiveSheet.Range("B5:AZ20").NumberFormat = ("#,##0")

AppExcel.ActiveSheet.Columns("A").ColumnWidth = 40
AppExcel.ActiveSheet.Columns("A").AutoFit

ActiveDocument.getApplication.sleep 5000

Set Gra02 = Doc.GetSheetObject("CH17")
Gra02.CopyBitMapToClipboard
AppExcel.ActiveSheet.Cells(17,1).Activate
Gra02.CopyBitMapToClipboard
AppExcel.ActiveSheet.Paste

AppExcel.ActiveSheet.Cells(1,1).Activate

// ********************* Salva y Cierra el Excel ***************************************

Ruta = Directory & "\Stock" & Year(Now) & Month(Now) & Day(Now) & " a las " & Hour(Now) & Minute(Now) & Second(Now) & ".XLS"

AppExcel.ActiveSheet.SaveAs (Ruta)

End Function




Community Browser