Home
Blog
Inquire
ＶＢＡ

ＶＢＡ--- csvファイルの作成


　エクセルに入力したデータを会計ソフトに取り込むためのcsvファイルを作成するツール（説明は省略）

 Sub 仕入仕訳Importcsv()
  Dim endcolcnt,colcnt,VHi,vNa,vYo,vGa,vKi,vSu As Long
  Dim vfl As String
  Dim inF As Integer
  With ActiveSheet
   endcolcnt = .Range("XFD2").End(xlToLeft).Column
   For colcnt = 1 To endcolcnt
    If .Cells(2, colcnt) = "取引日" Then
     VHi = colcnt
    ElseIf .Cells(2, colcnt) = "取引先名" Then
     vNa  = colcnt
    ElseIf .Cells(2, colcnt) = "取引内容" Then
     vYo = colcnt
    ElseIf .Cells(2, colcnt) = "取引額" Then
     vGa = colcnt
    ElseIf .Cells(2, colcnt) = "支払期限" Then
     vKi = colcnt
    ElseIf .Cells(2, colcnt) = "ステータス" Then
     vSu = colcnt
    End If
   Next colcnt
   inF = FreeFile
   vfl = ThisWorkbook.Path & "\仕入取引.csv"
   Set fs = CreateObject("Scripting.FileSystemObject")
   If Dir(vfl) <> "" Then
    fs.DeleteFile vfl, False
   End If
   Set fs = Nothing
   Open vfl For Append As #inF
   vTXT = "No.,取引日,取引先名,取引内容,取引額,支払期限,ステータス"
   Print #inF, vTXT
   endrowcnt = .Range("E" & Cells.Rows.Count).End(xlUp).Row
   For irowcnt = 3 To endrowcnt
    vTXT = "," & Format(.Cells(irowcnt, VHi), "yyyy/mm/dd") & "," & .Cells(irowcnt, vNa) & "," & _
           .Cells(irowcnt, vYo) & "," & Format(.Cells(irowcnt, vGa), "#####") & "　" & _
           Format(.Cells(irowcnt, vKi), "yyyy/mm/dd")  & .Cells(irowcnt, vSu)
    Print #inF, vTXT
   Next irowcnt
  End With
  Close #inF
  MsgBox "処理終了"
 End Sub

©2018KanazawaNoSakai