topic Easy export of some Excel-Files in QlikView

Easy export of some Excel-Files

Wed, 05 Aug 2009 02:19:07 GMT

Dear Forum,

I can write some loading scripts - but no Macros.

So I need some help:

I have a table with some rows.

The content of first row shall be the name of the excel-file and all the rows shall be the content of the file.

So there has to be a loop like

for i=1 to fieldvaluecount ('firtsrowname')

let aktvalue=fieldvalue ('firstrowname', $(i));

qualify *;

temptable:

load * resident firstrowname where firstrowname='$aktvalue');

store * from temptable into '$aktvalue'.qvd;

drop table temptable;

But now i want to have the data not in separate QSV but in separate Excel - the same like the script above.

How can i do?

TIA

Bjoern

Easy export of some Excel-Files

Wed, 05 Aug 2009 12:59:47 GMT

Dear Mehlhorn ,

Do it in two steps.

First Step:-

Test:

load * from abc.xls;

let x=fieldvalue(your_column_name,1);

let y='Your path' & $(x);

Second Step:-

Test2:

noconcatenate load * from abc.xls;

(here load the excel file from second row onwards)

store Test2 into $(y);

Try this. It should work fine.

Easy export of some Excel-Files

amars — Wed, 05 Aug 2009 13:04:05 GMT

Hi,

I don't really understood ur problem , but still see if this help.

I have written a macro which will export all the object on a sheet(Table, chart & List's) in a excel file leaving one alternate column. It is like this

'****************************************************************************************************************************************************************

sub CopyToXL

dim iCount

dim ObjName

dim ObjCaption

iCount=65

set XLApp = CreateObject("Excel.Application") ' Define Object

XLApp.Visible = True 'Visible set as true

set XLDoc = XLApp.Workbooks.Add 'Open new workbook

'Take the List of Object's in Excel

set XLSheet = XLDoc.Worksheets(1)

Shtobj = ActiveDocument.ActiveSheet.GetSheetObjects

for i = lBound(Shtobj) to uBound(Shtobj)

' Get TB1

set SheetObj = ActiveDocument.GetSheetObject(Shtobj(i).GetObjectId)

ObjName = Shtobj(i).GetObjectId

ObjCaption = Shtobj(i).GetCaption.Name.v

If Mid(ObjName,10,2)="LB" or Mid(ObjName,10,2)="TB" or Mid(ObjName,10,2)="CH" Then

'ObjCaption = SheetObj.Caption

If(((iCount-65)/26) > 1 ) Then

Cell = chr(((iCount-65)/26) + 64) & Chr(((iCount -65) Mod 26) + 65)

else

Cell = Chr(iCount)

End if

SheetObj.CopyTableToClipboard true

XLSheet.Range(Cell& "1") = ObjCaption

XLSheet.Range(Cell& "1").Font.Bold = True

'msgbox Cell & iCount

XLSheet.Paste XLSheet.Range(Cell& "2")

'XLSheet.Paste XLSheet.Cells(1,iCount)

iCount= XLSheet.UsedRange.Columns.Count + 1

iCount = iCount + 65

End if

'msgbox iCount

end sub

'****************************************************************************************************************************************************************

Try using this one , It is not properly formated , so you will need to add the formating.

Regard's

Amar

Easy export of some Excel-Files

Thu, 03 Sep 2009 16:39:37 GMT

Look this:

'*********************************************
' Constants for the Document.
'*********************************************
'
' For EXCEL
'
Const xlContinuous = 1
Const xlDiagonalDown = 5
Const xlDiagonalUp = 6
Const xlEdgeLeft = 7
Const xlEdgeTop = 8
Const xlEdgeBottom = 9
Const xlEdgeRight = 10
Const xlInsideVertical = 11
Const xlInsideHorizontal = 12
Const xlHairLine = 1
Const xlSolid = 1
Const xlThin = 2
Const xlAutomatic = -4105
Const xlCenter = -4108
Const xlNone = -4142
Const xlDown = -4121
Const xlMedium = -4138
Const xlRight = -4152
Const xTop = -4160
Const xlLastCell = 11

'*************************************************************************
'*
'*************************************************************************
Function IntestaFoglio(XLSheet, vDesChi, slett, iRigheInt)

XLSheet.Range("A1") = "Daniela "
XLSheet.Range("A1").Font.Bold = True
XLsheet.Range("A1").Font.Size = 11

XLSheet.Range("A2") = "F.V. Sig. "
XLSheet.Range("A2").Font.Bold = False
XLsheet.Range("A2").Font.Size = 10
XLsheet.Range("A2").HorizontalAlignment = xlRight
XLSheet.Range("B2") = vDesChi
XLSheet.Range("B2").Font.Bold = True
XLsheet.Range("B2").Font.Size = 10
XLsheet.Range("B2:E2").MergeCells = True

XLSheet.Range("Z"&iRigheInt) = " "
XLsheet.Range("A1:"&sLett&iRigheInt-1).Interior.ColorIndex = 2
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlDiagonalDown).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlDiagonalUp).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlEdgeLeft).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlEdgeTop).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlEdgeBottom).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlEdgeRight).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlInsideVertical).LineStyle = xlNone
XLsheet.Range("A1:"&sLett&iRigheInt-1).Borders(xlInsideHorizontal).LineStyle = xlNone

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).Weight = xlThin
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).ColorIndex = xlAutomatic
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeLeft).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeLeft).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeTop).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeTop).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeBottom).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeBottom).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeRight).LineStyle = xlContinuous
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlEdgeRight).Weight = xlMedium
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Borders(xlInsideVertical).LineStyle = xlContinuous

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).WrapText = True
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).HorizontalAlignment = xlCenter
XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).VerticalAlignment = xlCenter

XLsheet.Range("A"&iRigheInt&":"&sLett&iRigheInt).Interior.ColorIndex = 40
end Function

Ok???????