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

[Python] Get Excel Chart Count in all WorkSheets




# -*- coding: utf-8 -*-
import win32com
from win32com.client import DispatchEx, constants
msexcel = DispatchEx('Excel.Application')
msexcel.Visible = 1
msexcel.DisplayAlerts = 0
report_template_xlsx=u'ABC.xlsm'
workbook = msexcel.Workbooks.Open(report_template_xlsx)
sheet_count = workbook.Sheets.Count
print "Sheet #: " + str(sheet_count)
#print "Chart #: " + str(chart_count)
#workbook.Close()
#msexcel.Quit()
#quit()
for lngS in range(1,sheet_count+1):
  oWorksheet = workbook.Sheets(lngS)
  #oWorksheet = workbook.Worksheets("Created.vs.Resolved")
  oChart = oWorksheet.ChartObjects()
  chart_count = oChart.Count
  print "oWorksheet [" + str(oWorksheet.Name) + "] Chart count#: " + str(chart_count)

workbook.Close()
msexcel.Quit()

[Visual Basic] Refresh All




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

        oExcel = CreateObject("Excel.Application")
        oExcel.Visible = True
        oExcel.DisplayAlerts = False

        oWorkbook = oExcel.Workbooks.Open("test.xlsm")
        oWorkbook.RefreshAll()
        oWorkbook.Save()
        oWorkbook.Close()
        oExcel.Quit()

    End Sub

End Module

[Visual Basic]Automatic Email with Word & VB



It is much easier to control Office with VB.
Rewrite my Python version to VB in 10 minutes with difficulty...
Of course, Python is still good.

============
Imports Word = Microsoft.Office.Interop.Word
Imports System.IO
Module Module1

    Sub Main()
        Dim oWord As Word.Application
        Dim oDoc As Word.Document
        Dim WordEmail_docx, Email_recipient As String
        Dim mm As Word.MailMerge

        WordEmail_docx = "F:\MyProgram\python\Word2Email\WordEmail2_template2.docx"
        Email_recipient = "F:\MyProgram\python\Word2Email\recipient.csv"

        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oWord.DisplayAlerts = False
        oDoc = oWord.Documents.Open(FileName:=WordEmail_docx)
        mm = oDoc.MailMerge
        mm.OpenDataSource(Name:=Email_recipient)

        'send the merge result to Email
        mm.MainDocumentType = WdMailMergeMainDocType.wdEMail
        mm.Destination = WdMailMergeDestination.wdSendToEmail
        mm.MailAddressFieldName = "EMail"
        mm.MailFormat = WdMailMergeMailFormat.wdMailFormatHTML
        mm.MailSubject = "This is a test mail from Python Win32---" & Format(Now, "yyyy_MM_dd_hh_mm_s")

        'merge just two record - this step may be redundant
        mm.DataSource.FirstRecord = 1
        mm.DataSource.LastRecord = 2
        mm.SuppressBlankLines = True

        'merge
        mm.Execute()

        'cleanup
        oDoc.Close()
        oWord.Quit()
        oDoc = Nothing
        oWord = Nothing


    End Sub

End Module

2013年1月30日 星期三

[Python] Sending Plain-text email with outlook



# -*- coding: utf-8 -*-
#
# note: can't send HTML email
#
import win32com.client
olMailItem = 0x0
obj = win32com.client.Dispatch("Outlook.Application")
newMail = obj.CreateItem(olMailItem)
newMail.Subject = "I AM SUBJECT!!"
newMail.Body = "I AM IN THE BODY\nSO AM I!!!"
newMail.To = "victor_hsu@compalcomm.com"
newMail.CC = "victor_hsu@compalcomm.com"
#newMail.BCC = "address"
#attachment1 = "Path to attachment no. 1"
#attachment2 = "Path to attachment no. 2"
#newMail.Attachments.Add(attachment1)
#newMail.Attachments.Add(attachment2)
#newMail.display()
newMail.Send()

[Python]Automatic Email with Word & Python


Below is a python script to send Email with Word Merge Mail feature under Windows environment.

Environment: Win7 professional
Office: 2010 English version
Python 2.7

note:
1. Remember to run Makepy and choose Microsoft Word 14.0 Object Library(8.5) first.
==========================================

# -*- coding: utf-8 -*-
#
# Word Merge to Email
#
import win32com
from win32com.client import Dispatch, constants
import time,os

const=win32com.client.constants

doc_template_name = os.path.abspath('WordEmail2_template2.docx')
data_source_name = os.path.abspath('recipient.csv')

app = win32com.client.Dispatch("Word.Application")
app.Visible = 1
app.DisplayAlerts = 0

doc_template = app.Documents.Open(doc_template_name)
mm = doc_template.MailMerge

#attach data source to template
mm.OpenDataSource(data_source_name)

#
#send the merge result to Email
mm.MainDocumentType = const.wdEMail
mm.Destination = const.wdSendToEmail
mm.MailAddressFieldName = "EMail"
mm.MailFormat = const.wdMailFormatHTML
mm.MailSubject = "This is a test mail from Python Win32---"+time.strftime('%Y_%m_%d_%H_%M_%S')
mm.MailFormat = const.wdMailFormatHTML

#merge just two record - this step may be redundant
mm.DataSource.FirstRecord = 1
mm.DataSource.LastRecord = 2
mm.SuppressBlankLines = True

#merge
mm.Execute()
#mm.Execute(Pause=False)

#cleanup
doc_template.Close()
app.Quit()