topic Re: iam using vb macro code for exporting the objects? in QlikView

iam using vb macro code for exporting the objects?

Tue, 30 Sep 2014 05:43:16 GMT

iam using vb macro code for exporting the objects

and i want to change format

is there any possibility formating like color ,font ,header,is

we can implement this vb script

please help on that

please find qvw file and excel file

excel file contain output format

cover page header like

i want same excel out if i import to excel by using macro

Re: iam using vb macro code for exporting the objects?

phaneendra_kunc — Tue, 30 Sep 2014 05:50:57 GMT

Can you post a sample QVW?

I have done very extensive VB scripting in Exporting objects to Excel & PPT's. I am pretty sure we can do a formatting on any component.

Sample code looks like...

Private sub Excel_Formatting(ByRef objExcelDoc,objExcelApp)

vSheet = ActiveDocument.Variables("vRegion").GetContent.String

objExcelDoc.Sheets(1).Select

objExcelDoc.Sheets(1).Name = vSheet

objExcelDoc.Sheets(1).Range("H" & 2).Select

objExcelApp.ActiveWindow.FreezePanes = True

objExcelDoc.Sheets(1).Rows("1:1").WrapText = True

objExcelDoc.Sheets(1).Range("B1:D1").EntireColumn.Delete

objExcelDoc.Sheets(1).Range("A1:D1").AutoFilter

objExcelDoc.Sheets(1).Range("A1:N1").EntireColumn.AutoFit()

objExcelDoc.Sheets(1).Range("A4:D10").Interior.Color = RGB(0,255,255)

objExcelDoc.Sheets(1).Range("F11:BE11").ClearContents

objExcelDoc.Sheets(1).Range("F11:BE11").NumberFormat = "General"

objExcelDoc.Sheets(1).Range("F11:BJ11").Interior.Color = RGB(0,255,255)

objExcelDoc.Sheets(1).Rows("1:3").Interior.Color = RGB(0,0,0)

Set Selection = objExcelDoc.Sheets(1).Range("C1:C3")

With Selection

.Borders(5).LineStyle = xlNone

.Borders(6).LineStyle = xlNone

.Borders(7).LineStyle = xlNone

.Borders(8).LineStyle = xlNone

.Borders(9).LineStyle = xlNone

.Borders(10).LineStyle = xlNone

.Borders(11).LineStyle = xlNone

.Borders(12).LineStyle = xlNone

.HorizontalAlignment = 1

.VerticalAlignment = 1

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

' .ReadingOrder = xlContext

.MergeCells = False

End With

End Sub

Re: iam using vb macro code for exporting the objects?

Tue, 30 Sep 2014 05:58:30 GMT

Hello,

Try to have your formats as part of a theme required in a separate file and apply to the object required.

The following lines of code might help. Give a try.

set obj = ActiveDocument.GetSheetObject("CH01")

obj.ApplyTheme "xxx.qvt"

Re: iam using vb macro code for exporting the objects?

vikasmahajan — Tue, 30 Sep 2014 06:02:27 GMT

pfa

Vikas

Re: iam using vb macro code for exporting the objects?

amit_saini — Tue, 30 Sep 2014 06:05:48 GMT

Chaganti,

Try something like below:

sub sendToExcel

set XLApp = CreateObject("Excel.Application")

XLApp.Visible = true

set XLDoc = XLApp.Workbooks.Open("") 'set path to template file

set XLSheet = XLDoc.Worksheets("A3")

XLSheet.Activate

set table = ActiveDocument.GetSheetObject( "CH642" )

for RowIter = 1 to table.GetRowCount-1

for ColIter =0 to table.GetColumnCount-1

set cell = table.GetCell(RowIter,ColIter)

Select Case ColIter

Case 1

cellAddress = "B9" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 2

cellAddress = "H9" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 3

cellAddress = "M9" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 4

cellAddress = "B27" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 5

cellAddress = "H27" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 6

cellAddress = "M27" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 7

cellAddress = "B45" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 8

cellAddress = "H45" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

Case 9

cellAddress = "M45" 'Cell address to paste data in Excel

pasteData cellAddress, cell.Text, Doc, XLSheet

End Select

if ColIter = 18 then

msgbox(cell.Text)

end if

if ColIter = 18 then

msgbox(cell.Text)

cellAddress = "B9" 'Cell address to paste data in Excel

'Alex not used any more fieldName = "Problem Desc." 'Field name in Qlikview

pasteData cellAddress, cell.Text, Doc, XLSheet

end if

end sub

Thanks,

Re: iam using vb macro code for exporting the objects?

Tue, 30 Sep 2014 06:38:42 GMT

please find new attache ment