Rob van der Woude's Scripting Pages
Powered by GeSHi

Source code for xl2csv.vbs

(view source code of xl2csv.vbs as plain text) 

  1. Option Explicit
  2.  
  3. Dim blnForceOverwrite
  4. Dim objExcel, objFSO
  5. Dim strFileIn, strFileOut
  6.  
  7. Const xlCSV = 6
  8.  
  9. blnForceOverwrite = False
  10.  
  11. With WScript.Arguments
  12. 	If .Named.Count    > 1 Then Syntax
  13. 	If .Named.Count    = 1 Then
  14. 		If .Named.Exists( "Q" ) Then
  15. 			blnForceOverwrite = True
  16. 		Else
  17. 			Syntax
  18. 		End If
  19. 	End If
  20. 	If .Unnamed.Count <> 1 Then Syntax
  21. End With
  22.  
  23. Set objExcel = CreateObject( "Excel.application" )
  24. Set objFSO   = CreateObject( "Scripting.FileSystemObject" )
  25.  
  26. With objFSO
  27. 	strFileIn = WScript.Arguments.Unnamed(0)
  28. 	If .FileExists( strFileIn ) Then
  29. 		strFileIn = .GetAbsolutePathName( strFileIn )
  30. 		objExcel.Workbooks.Open strFileIn, , True
  31. 		strFileOut = .BuildPath( .GetParentFolderName( strFileIn ), .GetBaseName( strFileIn ) & ".csv" )
  32. 		If blnForceOverwrite And .FileExists( strFileOut ) Then
  33. 			.DeleteFile strFileOut, True
  34. 			WScript.Echo "Existing CSV file replaced."
  35. 		End If
  36. 		On Error Resume Next
  37. 		objExcel.ActiveWorkbook.SaveAs strFileOut, xlCSV
  38. 		If Err.Number = 1004 Then
  39. 			WScript.Echo "Existing CSV file not replaced."
  40. 		End If
  41. 		On Error Goto 0
  42. 		objExcel.ActiveWorkbook.Close False
  43. 	Else
  44. 		Syntax
  45. 	End If
  46. End With
  47.  
  48. Set objFSO   = Nothing
  49. Set objExcel = Nothing
  50.  
  51.  
  52. Sub Syntax( )
  53. 	WScript.Echo vbCrLf _
  54. 	           & "XL2CSV.vbs,  Version 1.00" _
  55. 	           & vbCrLf _
  56. 	           & "Convert an Excel sheet to CSV" _
  57. 	           & vbCrLf & vbCrLf _
  58. 	           & "Usage:  " & UCase( WScript.ScriptName ) & "  excel_file.xls  [ /Q ]" _
  59. 	           & vbCrLf & vbCrLf _
  60. 	           & "Where:  excel_file.xls   is the Excel file to be converted" _
  61. 	           & vbCrLf _
  62. 	           & "        /Q               silently overwrites an existing CSV file" _
  63. 	           & vbCrLf _
  64. 	           & "                         (default: prompt for confirmation)" _
  65. 	           & vbCrLf & vbCrLf _
  66. 	           & "Notes:  The CSV file will be saved with the name of the excel file," _
  67. 	           & vbCrLf _
  68. 	           & "        extension "".csv"", in the Excel file's parent folder." _
  69. 	           & vbCrLf _
  70. 	           & "        If the CSV file exists, a dialog window will prompt for" _
  71. 	           & vbCrLf _
  72. 	           & "        confirmation before overwriting the existing file." _
  73. 	           & vbCrLf & vbCrLf _
  74. 	           & "Written by Rob van der Woude" _
  75. 	           & vbCrLf _
  76. 	           & "http://www.robvanderwoude.com"
  77. 	WScript.Quit 1
  78. End Sub
  79.  

page last modified: 2024-04-16; loaded in 0.0081 seconds