Marked comments save!

All comments within a marked range are stored in a file in the temp directory. You can select between data attach or file overwrite. The file is opened at the end.

The following code belonged in "Module1"

Option Explicit
Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Declare Function GetTempPath Lib "kernel32" Alias _
"GetTempPathA" (ByVal nBufferLength As Long, ByVal _
lpBuffer As String) As Long
Public Sub Comment_TXT()
Dim intFileNumber As Integer
Dim strComment As String
Dim rngRange As Range
Dim rngCell As Range
On Error GoTo Comment_TXT_Error
intFileNumber = FreeFile
Set rngRange = Application.InputBox("Mark a range!", , "$C$1:$L$9", Type:=8)
Select Case MsgBox _
("Comments attach (Click YES), or file overwrite (Click NO)?", _
vbYesNo Or vbQuestion Or vbDefaultButton1, "Comment")
Case vbYes
Open GetTempDir & "Comment.txt" For Append As #intFileNumber
Case vbNo
Open GetTempDir & "Comment.txt" For Output As #intFileNumber
End Select
For Each rngCell In rngRange
If Not rngCell.Comment Is Nothing Then
strComment = strComment & rngCell.Comment.Text & Chr(13) & Chr(10)
End If
Next rngCell
If strComment = "" Then Exit Sub
Print #intFileNumber, strComment
Close #intFileNumber
ShellExecute Application.hwnd, "Open", GetTempDir & _
"Comment.txt", vbNullString, vbNullString, vbNormalFocus
On Error GoTo 0
Exit Sub
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
End Sub
Public Function GetTempDir() As String
Dim strPath As String
Dim lngCount As Long
Dim strTMP As String
strTMP = Space(255)
lngCount = GetTempPath(255, strTMP)
If lngCount > 0 Then
strPath = Left$(strTMP, lngCount)
If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
End If
GetTempDir = strPath
End Function

Sample 2003

Sample 2007


Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...