public class ThisAddIn
Private Sub ThisAddIn_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
' Start of VSTO generated code
Me.Application = CType(Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(GetType(Excel.Application), Me.Application), Excel.Application)
' End of VSTO generated code
End Sub
Private Sub ThisAddIn_Shutdown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shutdown
End Sub
Private Sub Application_WorkbookBeforeSave(ByVal wb As Microsoft.Office.Interop.Excel.Workbook, ByVal SaveAsUI As Boolean, ByRef Cancel As Boolean) Handles Application.WorkbookBeforeSave
SynchProperties(wb)
End Sub
Private Sub Application_WorkbookOpen(ByVal wb As Microsoft.Office.Interop.Excel.Workbook) Handles Application.WorkbookOpen
SynchRanges(wb)
End Sub
Private Sub SynchProperties(ByVal wb As Excel.Workbook)
Dim props As Office.DocumentProperties = wb.BuiltinDocumentProperties
Dim ctprops As Office.MetaProperties = wb.ContentTypeProperties
If props("Content Type").Value.ToString() = "PurchaseOrder" Then
Dim wsMap As Excel.Worksheet = wb.Worksheets("CTMap")
Dim wsPurchase As Excel.Worksheet = wb.Worksheets("Purchase Order")
Dim rg As Excel.Range = wsMap.Cells(1, 1)
While Not (rg.Value2 Is Nothing)
Try
ctprops(rg.Offset(0, 1).Value2).Value = wsPurchase.Range(rg.Value2).Value2.ToString()
Catch ex As Exception
End Try
rg = rg.Offset(1, 0)
End While
End If
End Sub
Private Sub SynchRanges(ByVal wb As Excel.Workbook)
Dim props As Office.DocumentProperties = wb.BuiltinDocumentProperties
Dim ctprops As Office.MetaProperties = wb.ContentTypeProperties
If props("Content Type").Value.ToString() = "PurchaseOrder" Then
Dim wsMap As Excel.Worksheet = wb.Worksheets("CTMap")
Dim wsPurchase As Excel.Worksheet = wb.Worksheets("Purchase Order")
Dim rg As Excel.Range = wsMap.Cells(1, 1)
While Not (rg.Value2 Is Nothing)
Try
If rg.Value2.ToString <> "GrandTotal" Then
wsPurchase.Range(rg.Value2).Value2 = ctprops(rg.Offset(0, 1).Value2).Value
End If
Catch ex As Exception
End Try
rg = rg.Offset(1, 0)
End While
End If
End Sub
End Class
|