Hi i created macro for exporting one pivot table and list box data the code is below
and now i want to format excel output like color boarder cells heght widht
for that i created record macro in excel now i want to includr that record macro script in to above macro where can i fit this code
please help on that
1>Qlikview macro code
SUB TEExport
confirmation = MSGBOX ("TopSpenders Excel export has been initiated." & vbCrLf & "Do you wish to continue?"& vbCrLf &"", 36, "Export Confirmation")
IF confirmation = 7 THEN
EXIT SUB
END IF
DIM xlApp
DIM xlBook
DIM xlSheet
DIM strSheetName
DIM var
DIM fname
SET f = ActiveDocument.Variables("vfname")
fname = f.GetContent.STRING
SET v = ActiveDocument.Variables("vMacroChartId")
var = v.GetContent.STRING
SET xlApp = CREATEOBJECT("Excel.Application")
xlApp.Visible = TRUE
SET xlBook = xlApp.Workbooks.Add
SET xlSheet = xlBook.Worksheets("Sheet1")
ActiveDocument.Fields(fname).Clear
SET Doc = ActiveDocument
SET Field = Doc.Fields(fname).GetPossibleValues
FOR i=0 to Field.Count-1
Doc.Fields(fname).Clear
Doc.FIelds(fname).SELECT Field.Item(i).Text
Doc.GetApplication.WaitForIdle
Doc.GetSheetObject(var).CopyTableToClipBoard TRUE
xlApp.ActiveSheet.Paste
xlSheet.Cells.EntireColumn.AutoFit
xlSheet.Cells.EntireRow.AutoFit
strSheetName = Field.Item(i).Text
xlApp.ActiveSheet.Name = strSheetName
IF(i<Field.Count-1)THEN
IF(i>=2)THEN
xlApp.ActiveWorkbook.Worksheets.Add
END IF
IF(i<2) THEN
xlApp.Worksheets(xlApp.ActiveSheet.Index +1).SELECT
END IF
END IF
NEXT
Doc.Fields(fname).Clear
MSGBOX "TopSpenders Excel export is complete!",64,"Task Completion Notification"
END SUB
excele recorded macro script
Sub Macro1()
'
' Macro1 Macro
'
'
Selection.Font.Bold = False
Selection.Font.Bold = True
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.499984740745262
End With
Range("A1:C1").Select
With Selection.Font
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.349986266670736
End With
Range("E6").Select
Columns("B:B").EntireColumn.AutoFit
Columns("B:B").ColumnWidth = 24.14
Rows("10:10").RowHeight = 13.5
Rows("14:14").RowHeight = 15
Rows("14:14").RowHeight = 11.25
Range("A1:C17").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$C$17"), , xlYes).Name = _
"Table1"
Range("E16").Select
Application.Run "Book2!Macro1"
End Sub
