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 →カレントディレクトリの中で指定の値を含んだファイル名を削除