「Option Explicit」マジ大事。適当に書いても動くからVBA厄介デスヨネー。
UTF-8はBOM有で、UTF-8NはBOM無し。
本題。UTF-8はADODBを使って入出力します。FSOもついでに載せときます。
Option Explicit
Public Sub テキストファイルUTF8読み込み(strFilePath As String)
Dim buf As String, temp As Variant
Dim iRow As Long, iClm As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("読込結果")
On Error GoTo err_1
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.Open
.LoadFromFile strFilePath
Do Until .EOS
buf = .ReadText(-2)
iRow = iRow + 1
temp = Split(buf, vbTab)
For iClm = LBound(temp) To UBound(temp)
ws.Cells(iRow, iClm + 1) = temp(iClm)
Next
Loop
err_1:
.Close
End With
Set ws = Nothing
End Sub
Public Sub テキストファイル読み込みFSO(strFilePath As String)
Dim buf As String, temp As Variant, lineTemp As Variant
Dim iRow As Long, iClm As Long
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("読込結果")
With CreateObject("Scripting.FileSystemObject")
With .GetFile(strFilePath).OpenAsTextStream
buf = .ReadAll
.Close
End With
End With
lineTemp = Split(buf, vbCrLf)
For iRow = LBound(lineTemp) To UBound(lineTemp)
temp = Split(lineTemp(iRow), vbTab)
For iClm = LBound(temp) To UBound(temp)
ws.Cells(iRow + 1, iClm + 1) = temp(iClm)
Next
Next
Set ws = Nothing
End Sub
Public Sub テキストファイル書き出しFSO()
Dim strFilePath As String
Dim buf As String
Dim delimiter As String
Dim row As Range, clm As Range
Dim ws As Worksheet
Dim fso As Object
strFilePath = Application.GetSaveAsFilename(InitialFileName:="shift-jis.txt", _
FileFilter:="テキストファイル(*.txt),*txt,CSVファイル(*.csv),*.csv")
If strFilePath = "False" Then Exit Sub
Set fso = CreateObject("Scripting.FileSystemObject")
Set ws = ThisWorkbook.Worksheets("読込結果")
delimiter = vbTab
With fso.CreateTextFile(strFilePath)
For Each row In ws.UsedRange.Rows
buf = ""
For Each clm In row.Columns
buf = buf & clm.Value
If clm.Column < row.Columns(row.Columns.Count).Column Then
buf = buf & delimiter
End If
Next
.WriteLine buf
Next
.Close
End With
Set ws = Nothing
Set fso = Nothing
End Sub
Public Sub テキストファイル書き出しADODB(Optional bom As Boolean = True)
Dim ws As Worksheet
Dim strFilePath As String
Dim buf As String
Dim delimiter As String
Dim row As Range, clm As Range
strFilePath = Application.GetSaveAsFilename(InitialFileName:="utf-8.txt", _
FileFilter:="テキストファイル(*.txt),*txt,CSVファイル(*.csv),*.csv")
If strFilePath = "False" Then Exit Sub
Set ws = ThisWorkbook.Worksheets("読込結果")
delimiter = vbTab
With CreateObject("ADODB.Stream")
.Charset = "UTF-8"
.LineSeparator = -1
.Open
For Each row In ws.UsedRange.Rows
buf = ""
For Each clm In row.Columns
buf = buf & clm.Value
If clm.Column < row.Columns(row.Columns.Count).Column Then
buf = buf & delimiter
End If
Next
.WriteText buf, 1
Next
If Not bom Then
Dim byteData() As Byte
.Position = 0
.Type = 1
.Position = 3
byteData = .Read
.Position = 0
.Write byteData
.SetEOS
.SaveToFile strFilePath, 2
.Close
Else
.SaveToFile strFilePath, 2
.Close
End If
End With
Set ws = Nothing
End Sub