將EXCEL裡的電話號碼用逗號分隔匯出

有個同事需要在EXCEL表中某一列匯出電話號碼，並用逗號隔開，GOOGLE查了一些文件後，呼叫VB來完成這個轉換，總結如下：

1、EXCEL裡，選擇選單，工具－宏－Visual Basic 編輯器，貼上以下程式碼。

Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")

' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum

' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count

' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.
Print #FileNum, Selection.Cells(RowCount, _
ColumnCount).Text & ",";

' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub
2、在EXCEL表中選擇你要匯出的那一列電話號碼，點選選單，工具－宏－宏，選擇QuoteCommaExport，點選“執行”。

3、在彈出的對話方塊中，輸入路徑和匯出的檔名。回車，電話號碼就在新的檔案用逗號分隔開來了。

4、在新的檔案裡發現一個電話號碼為一行，NND，同事說不符合要求，必須所有號碼為一行。後來用UltraEdit-32轉換了，選擇所有號碼，點選選單，格式－轉換回車/換行符為換行，所有號碼為一行了。

[@more@]