LoginSignup
0

More than 5 years have passed since last update.

OpenTextFileメソッドやOpenステートメントでのファイル操作や文字置き換えなど

Last updated at Posted at 2018-08-10

業務でVBAを使う事がありまして、取り合えず動いたので自分のメモ用にこれを残します。
もっとこうした方が良いとかありましたらコメント頂けると助かります。

Openステートメントでのファイル作成

Dim ii As Long
Dim bag As String
Dim bag2 As String
Dim f_path As String
Dim f_num As Integer

Set ii = 3
Set f_path = ActiveWorkbook.Path & "\SUM" & "\A_102.csv"
Set f_num = FreeFile

Open f_path For Output As f_num
    Do While Cells(ii, 16).Value <> ""
        If Cells(ii, 16).Value = "稼働中" And Cells(ii, 17).Value = "A" Then
             bag = Cells(ii, 17).Value
             bag2 = Cells(ii, 32).Value
             Print #f_num, bag & "." & bag2
        ElseIf Cells(ii, 16).Value = "待機中" And Cells(ii, 17).Value = "B" Then
             bag = Cells(ii, 17).Value
             bag2 = Cells(ii, 32).Value
             Print #f_num, bag & "." & bag2
        End If
    ii = ii + 1
    Loop
Close f_num

OpenTextFileメソッドでの既存ファイルの読み込みとソート


Dim FSO As Object
Dim List As Object
Dim Fin As TextStream
Dim Fout As TextStream

Set FSO = CreateObject("Scripting.FileSystemObject")
Set List = CreateObject("System.Collections.ArrayList")
Set Fin = FSO.OpenTextFile(Filename:=f_path)
Do Until Fin.AtEndOfLine
  List.Add (Fin.ReadLine)
Loop
Fin.Close

List.Sort

f_path2 = ActiveWorkbook.Path & "\SUM_SORT" & "A_102"
Set Fout = FSO.CreateTextFile(Filename:=f_path2)
For Each Value In List
  Debug.Print Value
  Fout.WriteLine Value
Next
Fout.Close

全体のセルを読み込んで、特定の文字を置き換える

Dim f_path As String
Dim buf As String

Set f_path = ActiveWorkbook.Path & "\SUM" & "\A_102.csv"

With FSO.GetFile(f_path).OpenAsTextStream
Set buf = .ReadAll
.Close
End With

FSO.GetFile(f_path).Delete
FSO.CreateTextFile f_path

buf = Replace(buf, "ABC", "A_B_C_D")
buf = Replace(buf, "XYZ", "X_Y_Z")

With FSO.GetFile(f_path).OpenAsTextStream(8)
.Write buf
.Close
End With

セル内の改行文字の検知と、改行前の文字列取得

一つのセルに2行入ってる様なデータに対し
改行コードより前の、1行目のデータを取得する。

Dim bag3 As String
Dim bag4 As String

Set bag3 = Cells(ii, 17).Value

If InStr(bag3, vbLf) > 0 Then
Debug.Print "LFあり"
pos = InStr(1, bag3, vbLf)
bag4 = Left(bag3, pos - 1)

End If

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0