Option Explicit
Public 文字コード As enm_文字コード
Public 区切り文字 As enm_区切り文字
Public 引用符 As enm_引用符
Public ヘッダー As Boolean
Public 列幅調整 As Boolean
Private p_カンマ区切り As Boolean
Private p_タブ区切り As Boolean
Private p_セミコロン区切り As Boolean
Private p_スペース区切り As Boolean
Private p_区切り文字 As Variant
Public Enum enm_文字コード
UTF8 = 65001
SJIS = 932
End Enum
Public Enum enm_区切り文字
カンマ区切り = 1
タブ区切り = 2
セミコロン区切り = 3
スペース区切り = 4
End Enum
Public Enum enm_引用符
ダブルクォーテーション = 1
シングルクォーテーション = 2
なし = -4142
End Enum
Private Sub Class_Initialize()
文字コード = UTF8
区切り文字 = カンマ区切り
引用符 = ダブルクォーテーション
ヘッダー = True
列幅調整 = False
End Sub
Public Function ReadCsv(ByVal aFullPath As String, aRange As Range) As Boolean
On Error GoTo Err
If InStr(aFullPath, ".") = 0 Then aFullPath = aFullPath & ".csv"
If InStr(aFullPath, "\") = 0 Then aFullPath = CurDir & "\" & aFullPath
Dim v(255) As Long
Dim i As Long
For i = 0 To 255
v(i) = 2
Next
ConfirmDelimiter
Dim qt As QueryTable
Set qt = aRange.Parent.QueryTables.Add(Connection:="TEXT;" & aFullPath, destination:=aRange)
With qt
.TextFilePlatform = 文字コード
.TextFileCommaDelimiter = p_カンマ区切り
.TextFileTabDelimiter = p_タブ区切り
.TextFileSemicolonDelimiter = p_セミコロン区切り
.TextFileSpaceDelimiter = p_スペース区切り
.TextFileTextQualifier = 引用符
.TextFileStartRow = IIf(ヘッダー, 1, 2)
.AdjustColumnWidth = 列幅調整
.TextFileColumnDataTypes = Array(v)
.RefreshStyle = xlOverwriteCells
.Refresh
.Delete
End With
ReadCsv = True
If logging Then AddLog "ReadCsv", "INFO ", "対象:" & aFullPath
Exit Function
Err:
If logging Then AddLog "ReadCsv", "ERROR", "対象:" & aFullPath & " / エラー内容:" & Err.Description
End Function
Public Function ReadCsvByDialog(aRange As Range) As Boolean
On Error GoTo Err
Dim fullPath As String: fullPath = Application.GetOpenFilename("CSV,*.csv")
If fullPath = "False" Then GoTo Err
Dim v(255) As Long
Dim i As Long
For i = 0 To 255
v(i) = 2
Next
ConfirmDelimiter
Dim qt As QueryTable
Set qt = aRange.Parent.QueryTables.Add(Connection:="TEXT;" & fullPath, destination:=aRange)
With qt
.TextFilePlatform = 文字コード
.TextFileCommaDelimiter = p_カンマ区切り
.TextFileTabDelimiter = p_タブ区切り
.TextFileSemicolonDelimiter = p_セミコロン区切り
.TextFileSpaceDelimiter = p_スペース区切り
.TextFileTextQualifier = 引用符
.TextFileStartRow = IIf(ヘッダー, 1, 2)
.AdjustColumnWidth = 列幅調整
.TextFileColumnDataTypes = Array(v)
.RefreshStyle = xlOverwriteCells
.Refresh
.Delete
End With
ReadCsvByDialog = True
If logging Then AddLog "ReadCsvByDialog", "INFO ", "対象:" & fullPath
Exit Function
Err:
If logging Then AddLog "ReadCsvByDialog", "ERROR", "対象:" & fullPath & " / エラー内容:" & Err.Description
End Function
Public Function WriteCsv(ByVal aFullPath As String, ByVal aRange As Range) As Boolean
On Error GoTo Err
Dim csv As String
Dim line As String
Dim row As Range
Dim col As Range
Dim item As String
Dim charset As String
If InStr(aFullPath, ".") = 0 Then aFullPath = aFullPath & ".csv"
If InStr(aFullPath, "\") = 0 Then aFullPath = CurDir & "\" & aFullPath
If ヘッダー = False Then
Set aRange = aRange.Offset(1, 0)
Set aRange = aRange.Resize(RowSize:=aRange.Rows.Count - 1)
End If
ConfirmDelimiter
For Each row In aRange.Rows
line = ""
For Each col In row.Columns
Select Case 引用符
Case 1: item = """" & col.Value & """"
Case 2: item = "'" & col.Value & "'"
Case -4142: item = col.Value
End Select
If line = "" Then
line = item
Else
line = line & p_区切り文字 & item
End If
Next
If csv = "" Then
csv = line
Else
csv = csv & vbCrLf & line
End If
Next
Select Case 文字コード
Case 65001: charset = "UTF-8"
Case 932: charset = "SJIS"
End Select
If Not WriteText(aFullPath, csv, charset) Then GoTo Err
WriteCsv = True
If logging Then AddLog "WriteCsv", "INFO ", "対象:" & aFullPath
Exit Function
Err:
If logging Then AddLog "WriteCsv", "ERROR", "対象:" & aFullPath & " / エラー内容:" & Err.Description
End Function
Public Function WriteCsvByDialog(ByVal aRange As Range) As Boolean
On Error GoTo Err
Dim fullPath As String
Dim csv As String
Dim line As String
Dim row As Range
Dim col As Range
Dim item As String
Dim charset As String
fullPath = Application.GetSaveAsFilename(FileFilter:="csv,*.csv")
If fullPath = "False" Then GoTo Err
If ヘッダー = False Then
Set aRange = aRange.Offset(1, 0)
Set aRange = aRange.Resize(RowSize:=aRange.Rows.Count - 1)
End If
ConfirmDelimiter
For Each row In aRange.Rows
line = ""
For Each col In row.Columns
Select Case 引用符
Case 1: item = """" & col.Value & """"
Case 2: item = "'" & col.Value & "'"
Case -4142: item = col.Value
End Select
If line = "" Then
line = item
Else
line = line & p_区切り文字 & item
End If
Next
If csv = "" Then
csv = line
Else
csv = csv & vbCrLf & line
End If
Next
Select Case 文字コード
Case 65001: charset = "UTF-8"
Case 932: charset = "SJIS"
End Select
If Not WriteText(fullPath, csv, charset) Then GoTo Err
WriteCsvByDialog = True
If logging Then AddLog "WriteCsvByDialog", "INFO ", "対象:" & fullPath
Exit Function
Err:
If logging Then AddLog "WriteCsvByDialog", "ERROR", "対象:" & fullPath & " / エラー内容:" & Err.Description
End Function
Private Function ConfirmDelimiter()
p_カンマ区切り = False
p_タブ区切り = False
p_セミコロン区切り = False
p_スペース区切り = False
Select Case 区切り文字
Case 1: p_カンマ区切り = True: p_区切り文字 = ","
Case 2: p_タブ区切り = True: p_区切り文字 = vbTab
Case 3: p_セミコロン区切り = True: p_区切り文字 = ";"
Case 4: p_スペース区切り = True: p_区切り文字 = " "
End Select
End Function