2013年4月20日 星期六

Excel Function to return Pivot Table Display Range in WorkSheet

***Excel Function to return Pivot Table Display Range in WorkSheet***
//
   When using Linked Excel Object in Word Report , Word uses absolute address to link object in Excel.
This way could cause error when Excel Object Range is changed. For example, Pivot Table X-Y size changed, the linked range in Word will become incorrect.
So the workaround is using Named Range in Excel. Unfortunately, there is direct function in Excel to do that.
Using Offset & counta function is one way we can make it. However, when user put several Pivot Table in same sheet, counta might not be correct value.

So I create a Excel function to handle this situation.  Any suggestion is welcome.

//



Function PVRange(Sheet_Name, Pivot_Name) As Range
' function to return PivotTable Display Range
'
Dim pvt As PivotTable
Dim PVRange As Range

Set pvt = Worksheets(Sheet_Name).PivotTables(Pivot_Name)
Set PVRange = pvt.TableRange1


End Function



2013年2月1日 星期五

[Visual Basic] Get Excel Chart Count in all WorkSheets




Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

    Sub Main()
        Dim oExcel As Excel.Application
        Dim oWorkbook As Excel.Workbook
        Dim oWorksheet As Excel.Worksheet
        Dim chart_count As Integer
        Dim oChart As Excel.ChartObjects
        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oExcel.DisplayAlerts = False

        oWorkbook = oExcel.Workbooks.Open("F:\Google 雲端硬碟\pythonprogram\Bugzilla_Issue_Summary_VT58.xlsm")
        For Each oWorksheet In oWorkbook.Worksheets
            'Console.Write(oWorksheet.Name)
            oChart = oWorksheet.ChartObjects()
            chart_count = oChart.Count
            MsgBox(oWorksheet.Name & "Chart:" & chart_count)
        Next

        'oWorkbook.Save()
        oWorkbook.Close()
        oExcel.Quit()

    End Sub

End Module