Advertisement
Astrocz

xlsx2csv

Apr 29th, 2019
113
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Set fso = CreateObject("Scripting.FileSystemObject")    ' paths'n'shit
  2.  
  3. ' Parse command line args
  4. Set cmd_args = WScript.Arguments
  5. If cmd_args.Count = 1 Then
  6.     filename_in = fso.GetAbsolutePathName(cmd_args(0))
  7. Else
  8.     WScript.Echo("Invalid number of arguments. Use as: cscript export.vbs <xlsx_file>")
  9.     WScript.Quit 1
  10. End If
  11.  
  12. ' Check input file
  13. If Not fso.FileExists(filename_in) Then
  14.     WScript.Echo("Input file " & filename_in & " not found.")
  15.     WScript.Quit 2
  16. End If
  17.  
  18. With CreateObject("Excel.application")
  19.     With .Application
  20.         .Visible=false              ' don't draw shit
  21.         .DisplayAlerts=false        ' overwrite without asking
  22.         .DecimalSeparator = "."
  23.         .ThousandsSeparator = ""
  24.         .UseSystemSeparators = false
  25.     End With
  26.    
  27.     .Workbooks.Open(filename_in)    ' first one becomes .ActiveWorkbook
  28.    
  29.     .ActiveSheet.Unprotect
  30.    
  31.     For Each cell in .ActiveSheet.UsedRange.Cells
  32.         If IsNumeric(cell) Then
  33.             cell.NumberFormat = "@" ' Text
  34.         ElseIf IsDate(cell) Then
  35.             'cell.Value = DateDiff("s", "1/1/1970", cell.Value) ' Unix timestamp
  36.             cell.NumberFormat = "yyyy-mm-dd hh:mm:ss"   ' Random shit
  37.         End If
  38.     Next
  39.    
  40.     ' Save
  41.     With .ActiveWorkbook
  42.         filename_out = fso.BuildPath(.Path, fso.GetBaseName(.Name))
  43.         .SaveAs filename_out, 62    ' 62 = xlCSVUTF8
  44.     End With
  45.  
  46.     .Application.Quit
  47.     .Quit
  48. End With
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement