daily pastebin goal
10%
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Export all worksheets in a known Excel file to csv's programatically using a VBS script?
  2. Dim source As Workbook
  3. Set source = Application.Workbooks.Open(WScript.Arguments.Item(0), ReadOnly:=True)
  4. For Each sheet In source.Sheets
  5. .SaveAs Filename:= WScript.Arguments.Item(1) & Source.Sheets.Name, FileFormat:=xlCSV
  6. Next sheet
  7. wb.Close
  8.    
  9. Dim strFilename
  10. Dim objFSO
  11. Set objFSO = CreateObject("scripting.filesystemobject")
  12. strFilename = "C:temptest.xlsx"
  13. If objFSO.fileexists(strFilename) Then
  14.     Call Writefile(strFilename)
  15. Else
  16.     wscript.echo "no such file!"
  17. End If
  18. Set objFSO = Nothing
  19.  
  20.  
  21. Sub Writefile(ByVal strFilename)
  22. Dim objExcel
  23. Dim objWB
  24. Dim objws
  25. Set objExcel = CreateObject("Excel.Application")
  26. Set objWB = objExcel.Workbooks.Open(strFilename)
  27. For Each objws In objWB.Sheets
  28. objws.Copy
  29. objExcel.ActiveWorkbook.SaveAs objWB.Path & "" & objws.Name & ".csv", 6
  30. objExcel.ActiveWorkbook.Close False
  31. Next
  32. objWB.Close False
  33. objExcel.Quit
  34. Set objExcel = Nothing
  35. End Sub
  36.    
  37. -------------------------------
  38. SELECT * FROM Demo
  39. -------------------------------
  40. |F1|F2 |F3        |F4        |
  41. | 1|1.1|12/10/2011|text elm 1|
  42. | 2|2.2|12/11/2011|text elm 2|
  43. | 3|4.4|12/12/2011|text elm 3|
  44. -------------------------------
  45.    
  46. Provider=MSDASQL.1;Extended Properties="DBQ=<FullPathToYourXls>;Driver={Microsoft
  47.  Excel Driver (*.xls)};
  48.    
  49. SELECT * INTO [Demo.csv] IN '<PathToYourCsvFolder>' 'Text;' FROM Demo
  50.    
  51. type ..dataExcelCsv2Demo.csv
  52. "F1";"F2";"F3";"F4"
  53. 1;1,10;10.12.2011 00:00:00;"text elm 1"
  54. 2;2,20;11.12.2011 00:00:00;"text elm 2"
  55. 3;4,40;12.12.2011 00:00:00;"text elm 3"
  56.    
  57. [Demo.csv]
  58. ColNameHeader=True
  59. CharacterSet=1252
  60. Format=Delimited(;)
  61. Col1=F1 Integer
  62. Col2=F2 Float
  63. Col3=F3 Date
  64. Col4=F4 Char Width 50
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top