Excel/VBA/ファイルの操作

ファイルを開く †

Excelブックとして開く †

Dim openFileName As String

openFileName = ThisWorkbook.Path & "/hoge.xlsx"
If Dir(openFileName) = "" Then
    openFileName = Application.GetOpenFilename("Excel ブック, *.xls*,CSV（コンマ区切り）, *.csv,すべてのファイル, *.*")        
    If openFileName = "False" Then
        Exit Sub
    End If
End If
Workbooks.Open openFileName

GetOpenFilename?の引数 †

CSVファイルをLine Inputで開く †

Dim openFileName As String
Dim strLine As String
Dim strArray() As String

openFileName = ThisWorkbook.Path & "/hoge.csv"
Open openFileName For Input As #1
Do Until EOF(1)
    Line Input #1, strLine
    If strLine <> "" Then
        strArray = Split(strLine, ",")
        For i = 0 To UBound(strArray)
            Debug.Print Replace(strArray(i), """", "")
        Next
    End If
Loop
Close #1

CSVファイルをTSVファイルに変換 †

Sub MainProcedure()
    strArray = Split(CSVtoTSV(strLine), vbTab)
End Sub

Function CSVtoTSV(ByVal str As String) As String
    Dim strTemp As String
    Dim quotCount As Long
    Dim l As Long

    For l = 1 To Len(str)
        strTemp = Mid(str, l, 1)
        If strTemp = """" Then
            quotCount = quotCount + 1
        ElseIf strTemp = "," Then
            If quotCount Mod 2 = 0 Then
                str = Left(str, l - 1) & vbTab & Right(str, Len(str) - l)
            End If
        End If
    Next l
    CSVtoTSV = str
End Function

ADODB.StreamでUTF-8のCSVファイルを読み込む †

Dim openFileName As String
Dim adoSt As Object
Dim strBuf As String
Dim strLine() As String
Dim strArray() As String

openFileName = ThisWorkbook.Path & "/sample-csv.csv"

Set adoSt = CreateObject("ADODB.Stream")
With adoSt
    .Charset = "UTF-8"
    .Open
    .LoadFromFile openFileName
    strBuf = .ReadText
    .Close
End With

strLine = Split(strBuf, vbCrLf)
For i = 1 To UBound(strLine)
    If strLine(i) <> "" Then
        strArray = Split(strLine(i), ",")
        For j = 0 To UBound(strArray)
            Debug.Print Replace(strArray(j), """", "")
        Next j
    End If
Next r

Microsoft ActiveX Data Objects 2.8 Libraryを有効にする †

1. VBEを起動
2. ツール
3. 参照設定
4. Microsoft ActiveX Data Objects 2.8 Libraryにチェックを入れる

OpenText?で形式と文字コードを指定して開く †

Dim openFileName As String
Dim rowEnd As Long
Dim strArray As Variant

openFileName = ThisWorkbook.Path & "/sample-csv.csv"

Call Workbooks.OpenText(openFileName, Origin:=65001, Comma:=True)
rowEnd = Range("A" & Rows.Count).End(xlUp).Row
strArray = Range("A1:F" & rowEnd).Value
ActiveWorkbook.Close SaveChanges:=False

For i = 1 To UBound(strArray, 1)
    For j = 1 To UBound(strArray, 2)
        Debug.Print strArray(i, j)
    Next j
Next i

第2引数で文字コードを指定 †

第3引数で区切り文字を指定 †

フォルダが存在しなければ作成 †

Dim folderPath As String
folderPath = ThisWorkbook.Path & "hoge"
If Dir(filePath, vbDirectry) = "" Then
    MkDir folderPath
End If

フォルダ内のファイルを順番に開く †

Dim path, fso, file, files
Dim wb As Workbook
path = ThisWorkbook.path & "/hoge"
Set fso = CreateObject("Scripting.FileSystemObject")
Set files = fso.GetFolder(path).files
For Each file In files
    Set wb = Workbooks.Open(file)

    '処理を記述

    ActiveWorkbook.Close SaveChanges:=False
Next file

ファイルの名前を変更 †

file.Name = "hoge"

上記のループ内に記述すれば、対象フォルダ内の全ファイルに対してファイル名変更を行う。

新規ブックを作成して保存 †

Dim tmpName As String       'デフォルトの保存名
Dim saveFileName As String  '保存するファイル名
Workbooks.Add
tmpName = "default"
saveFileName = Application.GetSaveAsFilename(tmpName, FileFilter:="Excelファイル,*.xlsx,すべてのファイル,*.*")
If saveFileName <> "False" Then
    ActiveWorkbook.SaveAs Filename:=saveFileName, FileFormat:=xlNormal
End If

ファイル形式とFileFormat?の引数 †

テキストファイルを作成して書き込み †

Dim openFileName As String  'ファイル名
openFileName = ThisWorkbook.Path & "\sample.txt"
'テキストファイルを新規作成（存在していれば上書き）
Open openFileName For Output As #1
Print #1, "hoge"
Print #1, "fuga"
Print #1, "piyo"
Close #1

指定のブックを閉じる †

Dim wb As Workbook
Dim fileName As String
fileName = "sample.xlsx"
For Each wb In Workbooks
    If wb.Name = fileName Then
        Application.DisplayAlerts = False
        wb.Close
        Application.DisplayAlerts = True     
    End If
Next wb