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