Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to store an Excel output to be used by a 3rd party application which cannot use txt or csv. I have used store for txt and qvw but cannot find a solution for excel anywhere. Any ideas?
What I do is save to a text file:
Store [tablename] into [path\KPIExport.csv] (txt);
then use a macro to convert:
Sub LoadFlatFile()
'
Workbooks.OpenText Filename:= _
"path\KPIExport.csv", Origin _
:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Call ...
End Sub
I embed the macro in a spreadsheet and use run-on-open, but you could alternatively place the macro in the QV script.
Regards.
Hi,
Try below
Dept:
LOAD empid,
deptname
FROM
dept.qvd
(qvd);
store Dept into Dept.xls(txt);
Regards
ASHFAQ
Load your table and store with the QVD files
Ex:-
Sales:
Load
Name,
Sales
From ExcelSource;
STORE Sales into Sales.qvd(qvd);
Drop Table Sales;
Load
Name,
Sales
From QVD Location;
Some thing like this ways.
Hi,
Ashfaq Mohammed wrote:
Hi,
Try below
Dept:
LOAD empid,
deptname
FROM
dept.qvd
(qvd);
store Dept into Dept.xls(txt);
Regards
ASHFAQ
This works in principle if you are manually opening the file but it cannot be automatically consumed by the 3rd party system as the format isn't recognised. Is there any way that QV can create an actual spreadsheet? (only xls or xlsx available sadly)
Thanks
Natalie
Inplace of XLS use CSV because XLS is not support by the qlikview
Ex:-
Dept:
LOAD empid,
deptname
FROM
dept.qvd
(qvd);
store Dept into Dept.CSV(txt);
Thanks
Unfortunately the target system will not use anything but xls or xlsx so I'm a bit stuck. I'm looking into alternatives on that side as I have a lot of these to do each day and don't want to be creating them manually.
What I do is save to a text file:
Store [tablename] into [path\KPIExport.csv] (txt);
then use a macro to convert:
Sub LoadFlatFile()
'
Workbooks.OpenText Filename:= _
"path\KPIExport.csv", Origin _
:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
TrailingMinusNumbers:=True
Range("A1").Select
Call ...
End Sub
I embed the macro in a spreadsheet and use run-on-open, but you could alternatively place the macro in the QV script.
Regards.
I believe you can create a CSV file with store command which work similar to the xls or xlsx file or may be you convert the CSV file into XLS or XLSX file formats after creating.
Thank you for the prompt response this is great!
Almost there and should be able to figure out the tweaks.
Much appreciated