2008년 07월 03일
엑셀에서 워드 문서 다루기
Public Sub PrintCellComments()
Dim Cmt As String
Dim C As Range
Dim I As Integer
Dim WordObj As Object
Dim ws As Worksheet
Dim PrintValue As Boolean
Dim res As Integer
On Error Resume Next
Err.Number = 0res = MsgBox("Do want to print cell values with comments?", _
vbYesNoCancel + vbQuestion, "Print Cell Comments")
Select Case res
Case vbCancel
Exit Sub
Case vbYes
PrintValue = True
Case Else
PrintValue = False
End SelectSet WordObj = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set WordObj = CreateObject("Word.Application")
Err.Number = 0
End IfWordObj.Visible = True
WordObj.Documents.Add
With WordObj.Selection
.TypeText Text:="Cell Comments In Workbook: " + ActiveWorkbook.Name
.TypeParagraph
.TypeText Text:="Date: " + Format(Now(), "dd-mmm-yy hh:mm")
.TypeParagraph
.TypeParagraph
End WithFor Each ws In Worksheets
For I = 1 To ws.Comments.Count
Set C = ws.Comments(I).Parent
Cmt = ws.Comments(I).Text
With WordObj.Selection
.TypeText Text:="Comment In Cell: " + _
C.Address(False, False, xlA1) + " on sheet: " + ws.Name
If PrintValue = True Then
.TypeText Text:=" Cell Value: " + Format(C.Value)
End If
.TypeParagraph
.TypeText Text:=Cmt
.TypeParagraph
.TypeParagraph
End With
Next I
Next wsSet WordObj = Nothing
MsgBox "Finished Printing Comments To Word", vbInformation, _
"PrintCellComments"End Sub
참고 자료 1 : http://www.cpearson.com/excel/excelM.htm
참고 자료 2 : http://www.microsoft.com/technet/scriptcenter/hubs/office.mspx
이글루스 가든 - professional secur...
# by | 2008/07/03 12:25 | 트랙백 | 덧글(1)
