Advertisement
Guest User

Untitled

a guest
Aug 20th, 2014
218
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.21 KB | None | 0 0
  1. Sub test()
  2. Dim wb As Workbook
  3. Dim ThisSheet As Worksheet
  4. Dim NumOfColumns As Integer
  5. Dim RangeToCopy As Range
  6. Dim RangeOfHeader As Range 'data (range) of header row
  7. Dim WorkbookCounter As Integer
  8. Dim RowsInFile 'how many rows (incl. header) in new files?
  9. Application.ScreenUpdating = False
  10.  
  11. 'Initialize data
  12. Set ThisSheet = ThisWorkbook.ActiveSheet
  13. NumOfColumns = ThisSheet.UsedRange.Columns.Count
  14. WorkbookCounter = 1
  15. RowsInFile = 5 'as your example, just 10 rows per file
  16.  
  17. 'Copy the data of the first row (header)
  18. Set RangeOfHeader = ThisSheet.Range(ThisSheet.Cells(1, 1), ThisSheet.Cells(1, NumOfColumns))
  19.  
  20. For p = 2 To ThisSheet.UsedRange.Rows.Count Step RowsInFile - 1
  21. Set wb = Workbooks.Add
  22.  
  23. 'Paste the header row in new file
  24. RangeOfHeader.Copy wb.Sheets(1).Range("A1")
  25.  
  26. 'Paste the chunk of rows for this file
  27. Set RangeToCopy = ThisSheet.Range(ThisSheet.Cells(p, 1), ThisSheet.Cells(p + RowsInFile - 2, NumOfColumns))
  28. RangeToCopy.Copy wb.Sheets(1).Range("A2")
  29.  
  30. 'Save the new workbook, and close it
  31. wb.SaveAs ThisWorkbook.Path & "" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "_v" & WorkbookCounter & ".csv", FileFormat:=xlCSV
  32. wb.Close True
  33.  
  34. 'Increment file counter
  35. WorkbookCounter = WorkbookCounter + 1
  36. Next p
  37.  
  38. Application.ScreenUpdating = True
  39. Set wb = Nothing
  40. End Sub
  41.  
  42. Sub saveAsUTF8()
  43. Dim myStream As ADODB.Stream
  44. Dim ws As Worksheet
  45. Dim curRow As String
  46. Dim curRowRng As Range
  47. Dim curCell As Range
  48.  
  49. Set myStream = New ADODB.Stream
  50.  
  51. Set ws = ThisWorkbook.ActiveSheet
  52.  
  53. With myStream
  54. .Type = adTypeText
  55. .Charset = "UTF-8"
  56. .Open
  57.  
  58. For Each curRowRng In ws.UsedRange.Rows
  59. curRow = ""
  60. For Each curCell In curRowRng.Cells
  61. curRow = curRow & "," & curCell.Value
  62. Debug.Print curRow
  63. Next curCell
  64. curRow = Right(curRow, Len(curRow) - 1)
  65. Debug.Print curRow
  66. .WriteText curRow, adWriteLine
  67. Next curRowRng
  68.  
  69. 'CHANGE TO YOU DESTINATION DIRECTORY
  70. .SaveToFile "YOUR_PATHutf8file.csv", adSaveCreateOverWrite
  71. .Close
  72.  
  73. End With
  74.  
  75. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement