日に日に分からんことが増えていく…

プログラマー初心者としての日々を学んでいることを記録していく。

VBAによるtsvファイルのExcel変換(Mac編)

FileFormat numbers in Mac Excel

These are the main file formats in Windows Excel 2007-2016:

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)
SaveAs and FileFormat numbers in Mac Excel

Dir nameのところは変更して!

Sub CSVcombert()


Dim DirName As String
Dim File As String
Dim OpenBook As Workbook, CurrentBook As Workbook
Dim fileAccessGranted As Boolean
Dim filePermissionCandidates(430) As String
Dim n As Long


Set CurrentBook = ThisWorkbook


Application.ScreenUpdating = False
DirName = "[Dir name]"


n = 1
Do While Worksheets("Sheet1").Cells(n, 1).Value <> "":
    File = Worksheets("Sheet1").Cells(n, 1).Value
    filePermissionCandidates(n) = DirName & "/" & File
    n = n + 1
Loop


fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)

n = 1
Do While Worksheets("Sheet1").Cells(n, 1).Value <> "":
   File = Worksheets("Sheet1").Cells(n, 1).Value
   Set OpenBook = Workbooks.Open(DirName & "/" & File)
   OpenBook.SaveAs FileName:=DirName & "/" & Left(File, Len(File) - 3) & "xlsx", FileFormat:=xlOpenXMLWorkbook
   OpenBook.Close
   ' Kill (DirName & "/" & File) '←元のCSVを削除するときは、Killの前にあるシングルコーテーション(')を消す
   n = n + 1
Loop

End Sub

付録

$ ls * | wc -l →ファイル数確認
$ for F in `ls`; do mv $F ${F/-/_};done →ファイル名置換作業
$ ls | grep -e test -e gokaku | xargs rm →カレントディレクトリの中で指定の値を含んだファイル名を削除