Macro to concatenate multiple charts into one CSV

[avantime]

Hi,

I want to concatenate three charts into one csv file using macro. All the charts have the same headers and number of columns.

Unfortunately I cannot combine them into one chart in Qlikview, all of them being Pareto charts using column sorting to get the final result.

So what I have now looks like:

Header 1	Header 2
A	1
B	2

Header 1	Header 2
C	3
D	4

Header 1	Header 2
E	5
F	6

The output should look like:

Header1;Header2

A;1

B;2

C;3

D;4

E;5

F;6

Thank you!

[avantime · Accepted solution]

Managed to figure it by myself after all... Post approval by the moderators takes a lot of time..

Maybe someone else needs this so here was my approach:

- Found this post: VB Macro to Export Table to CSV with No headers !

- Created a script that exported each chart to a separate CSV;

- First CSV created is left unmodified, the rest have their headers deleted in order to concatenate them to the first CSV.

Here is the script:

Sub ExportChart1

set sObject = ActiveDocument.GetSheetObject("CH01")

sObject.Export "E:\Data1.csv", ";"

ExportChart2

End Sub

Sub ExportChart2

set sObject = ActiveDocument.GetSheetObject("CH02")

sObject.Export "E:\Data2.csv", ";"

ExportChart3

End Sub

Sub ExportChart3

set sObject = ActiveDocument.GetSheetObject("CH03")

sObject.Export "E:\Data3.csv", ";"

Dim obj

Set obj = CreateObject("WScript.Shell")

obj.Run "E:\DeleteFirstLine.bat", , TRUE

Combinefiles

End Sub

Sub Combinefiles

Const ForReading = 1

Const ForWriting = 2

Const ForAppending = 8

' define csv files that will be read

Dim arrCSVFiles : arrCSVFiles = Array("E:\Data1.csv", _

                                      "E:\Data2.csv", _

                                      "E:\Data3.csv")

' define combined csv file

Dim outCSVFile : outCSVFile = "E:\Export.csv"

Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")

' open output csv file

Dim objOutFile : Set objOutFile = objFSO.OpenTextFile(outCSVFile, ForWriting, True)

Dim csvFile, objFile, strTemp

' loop through the list of csv files to read

For Each csvFile In arrCSVFiles

    ' open file for reading

    Set objFile = objFSO.OpenTextFile(csvFile, ForReading)

    Do Until objFile.AtEndOfStream

        strTemp = objFile.ReadLine

        If Not strTemp = "" Then

            ' write line to combined csv file

            objOutFile.WriteLine strTemp

        End If

    Loop

    ' close current opened csv file

    objFile.Close

Next

' close combined csv file

objOutFile.Close

End Sub

Regards,

Catalin