Advertisement
Guest User

Untitled

a guest
Jul 26th, 2017
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.08 KB | None | 0 0
  1. Sub Button1_Click()
  2.  
  3. Dim orgFilename As String
  4. Dim temp As String
  5. Dim strarray(3) As String
  6. Dim vert(4) As String
  7. Dim vert2(3) As String
  8. Dim newFilename As String
  9. Dim numRows As Integer
  10. Dim i As Integer
  11. Dim j As Integer
  12. Dim k As Integer
  13. Dim segCount As Integer
  14. Dim vertex(3, 100) As Double
  15.  
  16. Dim oldwb As Workbook
  17. Dim newwb As Workbook
  18.  
  19. orgFilename = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
  20. If orgFilename = "False" Then Exit Sub
  21. Workbooks.OpenText Filename:=orgFilename, _
  22. Origin:=950, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
  23. xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
  24. Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
  25. Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
  26. Set oldwb = ActiveWorkbook
  27. Set newwb = Workbooks.Add
  28.  
  29. oldwb.Activate
  30. Cells(5, 1).Select
  31. numRows = Cells(5, 1).End(xlDown).Row
  32.  
  33. ' Parse through data
  34. segCount = 0
  35. j = 1
  36. For i = 5 To numRows
  37. If Cells(i, 1) <> "VRTX" And segCount <> 0 Then
  38. For k = 1 To segCount - 1
  39. newwb.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
  40. With newwb.Worksheets("Sheet1")
  41. .Cells(j, 2) = vertex(1, k)
  42. .Cells(j, 3) = vertex(3, k)
  43. .Cells(j, 4) = vertex(2, k)
  44. .Cells(j, 5) = vertex(1, k + 1)
  45. .Cells(j, 6) = vertex(3, k + 1)
  46. .Cells(j, 7) = vertex(2, k + 1)
  47. End With
  48. j = j + 1
  49. Next k
  50. segCount = 0
  51. ElseIf Cells(i, 1) = "VRTX" Then
  52. ' Save vertices to save an endpoint
  53. vertex(1, segCount + 1) = Cells(i, 3)
  54. vertex(2, segCount + 1) = Cells(i, 4)
  55. vertex(3, segCount + 1) = Cells(i, 5)
  56. segCount = segCount + 1
  57. End If
  58. Next i
  59.  
  60. ' Save as a new file
  61. temp = Mid$(orgFilename, InStrRev(orgFilename, "") + 1)
  62. temp = Replace$(temp, ".pl", ".csv")
  63. strarray(1) = Left(orgFilename, InStrRev(orgFilename, ""))
  64. strarray(2) = "processed_"
  65. strarray(3) = temp
  66. newFilename = Join(strarray, "")
  67. newwb.SaveAs Filename:=newFilename, _
  68. FileFormat:=xlCSV, _
  69. Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
  70. CreateBackup:=False
  71. End Sub
  72.  
  73. Sub main()
  74. openPlFile
  75. readPlFile
  76. writeCsvFile
  77. saveCsvFile
  78. End Sub
  79.  
  80. On Error GoTo error_handler
  81.  
  82. Exit Sub
  83. error_handler:
  84. 'code to handle the error for example:
  85. MsgBox "There was an error: " & Err.Description
  86. End Sub
  87.  
  88. Workbooks.OpenText Filename:=orgFilename, _
  89. Origin:=950, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
  90. xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
  91. Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
  92. Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True`
  93.  
  94. Workbooks.OpenText Filename:=orgFilename, _
  95. Origin:=950, _
  96. StartRow:=1, _
  97. DataType:=xlDelimited, _
  98. TextQualifier:= xlDoubleQuote, _
  99. ConsecutiveDelimiter:=True, _
  100. Tab:=True, _
  101. Semicolon:=False, _
  102. Comma:=False, _
  103. Space:=True, _
  104. Other:=False, _
  105. FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
  106. TrailingMinusNumbers:=True`
  107.  
  108. Option Explicit
  109. Sub Button1_Click()
  110. ' Constants are declared at the beginning of the routine.
  111. Const ROW_SKIP As Long = 5
  112.  
  113. ' Avoid Dim blocks like these. It is always best to declare variables as close to their initial use
  114. ' as possible. This makes your code easier to read/maintain as well.
  115. 'Dim orgFilename As String
  116. 'Dim temp As String
  117. 'Dim strarray(3) As String
  118. 'Dim vert(4) As String
  119. 'Dim vert2(3) As String
  120. 'Dim newFilename As String
  121. 'Dim numRows As Integer
  122. 'Dim i As Integer
  123. 'Dim j As Integer
  124. 'Dim k As Integer
  125. 'Dim segCount As Integer
  126. 'Dim vertex(3, 100) As Double
  127. '
  128. 'Dim oldwb As Workbook
  129. 'Dim newwb As Workbook
  130.  
  131. ' I will declare the variable name, but I will also use a name that is slightly more descriptive.
  132. ' This will allow others to understand what I am doing. I also encapsulate this in a function to allow for
  133. ' easy error handling.
  134. 'orgFilename = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
  135.  
  136.  
  137.  
  138. ' Instead of just exiting the sub, handle this error.
  139. ' If orgFilename = "False" Then Exit Sub
  140.  
  141. Dim InputFileName As String
  142. InputFileName = GetInputFileName
  143.  
  144. If InputFileName = vbNullString Then
  145. ' We can add a messagebox here if needed. For now, we just exit the routine silently.
  146. Exit Sub
  147. End If
  148.  
  149. ' For your field info here, you are using an uninitialized, undeclared, array. What effect are you intending to achieve?
  150. Workbooks.OpenText _
  151. Filename:=orgFilename, _
  152. Origin:=950, _
  153. StartRow:=1, _
  154. DataType:=xlDelimited, _
  155. TextQualifier:= _
  156. xlDoubleQuote, _
  157. ConsecutiveDelimiter:=True, _
  158. Tab:=True, Semicolon:=False, _
  159. Comma:=False, _
  160. Space:=True, _
  161. Other:=False, _
  162. FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
  163. TrailingMinusNumbers:=True
  164.  
  165. ' I declare more descriptive workbook variable names, and separate the assignments.
  166. ' Set oldwb = ActiveWorkbook
  167. ' Set newwb = Workbooks.Add
  168.  
  169. ' I am changing this to a Sheet reference since you seem to be referring to the ActiveSheet implicitly, and not just the ActiveWorkbook
  170. Dim CurrentWorksheet As Worksheet
  171. Set CurrentWorksheet = ActiveSheet
  172.  
  173. ' While the default scope of `Workbooks.Add` is `Application.Workbooks.Add` it is better to be explicit.
  174. Dim OutputWorkbook As Workbook
  175. Set OutputWorkbook = Application.Workbooks.Add
  176.  
  177. ' No need for Activate. Try to avoid this behavior.
  178. ' oldwb.Activate
  179.  
  180. ' Avoid Select as well
  181. ' Cells(5, 1).Select
  182. ' numRows = Cells(5, 1).End(xlDown).Row
  183.  
  184. ' Declare new variable, and qualify the range reference when finding the row. Without the qualifying reference
  185. ' to `CurrentWorkbook` the `Cells` reference refers to the `ActiveWorkbook`.
  186. Dim NumberOfRows As Long
  187. NumberOfRows = CurrentWorksheet.Cells(5, 1).End(xlDown).Row
  188.  
  189. ' Instead of making changed within the loop, I am just going to rewrite it to make changes easier to read.
  190. ' Parse through data
  191. 'segCount = 0
  192. 'j = 1
  193. 'For i = 5 To numRows
  194. ' If Cells(i, 1) <> "VRTX" And segCount <> 0 Then
  195. ' For k = 1 To segCount - 1
  196. ' newwb.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
  197. ' With newwb.Worksheets("Sheet1")
  198. ' .Cells(j, 2) = vertex(1, k)
  199. ' .Cells(j, 3) = vertex(3, k)
  200. ' .Cells(j, 4) = vertex(2, k)
  201. ' .Cells(j, 5) = vertex(1, k + 1)
  202. ' .Cells(j, 6) = vertex(3, k + 1)
  203. ' .Cells(j, 7) = vertex(2, k + 1)
  204. ' End With
  205. ' j = j + 1
  206. ' Next k
  207. ' segCount = 0
  208. ' ElseIf Cells(i, 1) = "VRTX" Then
  209. ' ' Save vertices to save an endpoint
  210. ' vertex(1, segCount + 1) = Cells(i, 3)
  211. ' vertex(2, segCount + 1) = Cells(i, 4)
  212. ' vertex(3, segCount + 1) = Cells(i, 5)
  213. ' segCount = segCount + 1
  214. ' End If
  215. 'Next i
  216.  
  217. ' Assumes that the UsedRange of the Input sheet is the data we need
  218. Dim InputData As Variant
  219. InputData = CurrentWorksheet.UsedRange.Value
  220.  
  221. Dim SegmentCount As Long
  222.  
  223. Dim i As Long
  224. Dim j As Long
  225. Dim k As Long
  226.  
  227. j = 1
  228.  
  229. ' Re-creating your vertex array though it is not at all clear what it is being used for.
  230. Dim Vertices As Variant
  231. ReDim Vertices(3, 100)
  232.  
  233. ' I use a constant variable instead of 5 here since the 5 may change, and it can be difficult to track it down later.
  234. For i = ROW_SKIP To NumberOfRows
  235. ' Note: This will always return false on the first pass since SegmentCount will always equal 0
  236. If InputData(i, 1) <> "VRTX" And SegmentCount <> 0 Then
  237. For k = 1 To segCount - 1
  238. OutputWorkbook.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
  239. With OutputWorkbook.Worksheets("Sheet1")
  240. .Cells(j, 2) = Vertices(1, k)
  241. .Cells(j, 3) = Vertices(3, k)
  242. .Cells(j, 4) = Vertices(2, k)
  243. .Cells(j, 5) = Vertices(1, k + 1)
  244. .Cells(j, 6) = Vertices(3, k + 1)
  245. .Cells(j, 7) = Vertices(2, k + 1)
  246. End With
  247. j = j + 1
  248. Next k
  249. SegmentCount = 0
  250. ElseIf InputData(i, 1) = "VRTX" Then
  251. Vertices(1, SegmentCount + 1) = InputData(i, 3)
  252. Vertices(2, SegmentCount + 1) = InputData(i, 4)
  253. Vertices(3, SegmentCount + 1) = InputData(i, 5)
  254.  
  255. SegmentCount = SegmentCount + 1
  256. End If
  257. Next i
  258.  
  259. ' This can be condensed into a much more concise format
  260.  
  261. ' Save as a new file
  262. ' temp = Mid$(orgFilename, InStrRev(orgFilename, "") + 1)
  263. ' temp = Replace$(temp, ".pl", ".csv")
  264.  
  265.  
  266. ' strarray(1) = Left(orgFilename, InStrRev(orgFilename, ""))
  267. ' strarray(2) = "processed_"
  268. ' strarray(3) = temp
  269.  
  270. ' newFilename = Join(strarray, "")
  271.  
  272. Dim OutputFileName As String
  273.  
  274. ' This takes care of the entire operation in one line, and allows others to see what these operations are being used for.
  275. OutputFileName = Left(orgFilename, InStrRev(orgFilename, "")) & "processed_" & Replace$(Mid$(orgFilename, InStrRev(orgFilename, "") + 1), ".pl", ".csv")
  276.  
  277. OutputWorkbook.SaveAs Filename:=OutputFileName, _
  278. FileFormat:=xlCSV, _
  279. Password:="", _
  280. WriteResPassword:="", _
  281. ReadOnlyRecommended:=False, _
  282. CreateBackup:=False
  283. End Sub
  284. Private Function GetInputFileName() As String
  285. ' I use a variant declaration because the return of `Cancel` is the Boolean false.
  286. Dim InputFileNameResult As Variant
  287.  
  288. InputFileNameResult = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
  289.  
  290. If Not InputFileNameResult Then
  291. GetInputFileName = InputFileNameResult
  292. Else
  293. ' You can handle this as needed. For now, we just assume the user wants to exit the routine.
  294. ' As such, we do nothing.
  295. End If
  296. End Function
  297.  
  298. Option Explicit
  299. Sub Button1_Click()
  300. ' Constants are declared at the beginning of the routine.
  301. Const ROW_SKIP As Long = 5
  302.  
  303. Dim InputFileName As String
  304. InputFileName = GetInputFileName
  305.  
  306. If InputFileName = vbNullString Then
  307. ' We can add a messagebox here if needed. For now, we just exit the routine silently.
  308. Exit Sub
  309. End If
  310.  
  311. ' For your field info here, you are using an uninitialized, undeclared, array. What effect are you intending to achieve?
  312. Workbooks.OpenText _
  313. Filename:=orgFilename, _
  314. Origin:=950, _
  315. StartRow:=1, _
  316. DataType:=xlDelimited, _
  317. TextQualifier:= _
  318. xlDoubleQuote, _
  319. ConsecutiveDelimiter:=True, _
  320. Tab:=True, Semicolon:=False, _
  321. Comma:=False, _
  322. Space:=True, _
  323. Other:=False, _
  324. FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
  325. TrailingMinusNumbers:=True
  326.  
  327. ' I am changing this to a Sheet reference since you seem to be referring to the ActiveSheet implicitly, and not just the ActiveWorkbook
  328. Dim CurrentWorksheet As Worksheet
  329. Set CurrentWorksheet = ActiveSheet
  330.  
  331. ' While the default scope of `Workbooks.Add` is `Application.Workbooks.Add` it is better to be explicit.
  332. Dim OutputWorkbook As Workbook
  333. Set OutputWorkbook = Application.Workbooks.Add
  334.  
  335. ' Declare new variable, and qualify the range reference when finding the row. Without the qualifying reference
  336. ' to `CurrentWorkbook` the `Cells` reference refers to the `ActiveWorkbook`.
  337. Dim NumberOfRows As Long
  338. NumberOfRows = CurrentWorksheet.Cells(5, 1).End(xlDown).Row
  339.  
  340. ' Assumes that the UsedRange of the Input sheet is the data we need
  341. Dim InputData As Variant
  342. InputData = CurrentWorksheet.UsedRange.Value
  343.  
  344. Dim SegmentCount As Long
  345.  
  346. Dim i As Long
  347. Dim j As Long
  348. Dim k As Long
  349.  
  350. j = 1
  351.  
  352. ' Re-creating your vertex array though it is not at all clear what it is being used for.
  353. Dim Vertices As Variant
  354. ReDim Vertices(3, 100)
  355.  
  356. ' I use a constant variable instead of 5 here since the 5 may change, and it can be difficult to track it down later.
  357. For i = ROW_SKIP To NumberOfRows
  358. ' Note: This will always return false on the first pass since SegmentCount will always equal 0
  359. If InputData(i, 1) <> "VRTX" And SegmentCount <> 0 Then
  360. For k = 1 To segCount - 1
  361. OutputWorkbook.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
  362. With OutputWorkbook.Worksheets("Sheet1")
  363. .Cells(j, 2) = Vertices(1, k)
  364. .Cells(j, 3) = Vertices(3, k)
  365. .Cells(j, 4) = Vertices(2, k)
  366. .Cells(j, 5) = Vertices(1, k + 1)
  367. .Cells(j, 6) = Vertices(3, k + 1)
  368. .Cells(j, 7) = Vertices(2, k + 1)
  369. End With
  370. j = j + 1
  371. Next k
  372. SegmentCount = 0
  373. ElseIf InputData(i, 1) = "VRTX" Then
  374. Vertices(1, SegmentCount + 1) = InputData(i, 3)
  375. Vertices(2, SegmentCount + 1) = InputData(i, 4)
  376. Vertices(3, SegmentCount + 1) = InputData(i, 5)
  377.  
  378. SegmentCount = SegmentCount + 1
  379. End If
  380. Next i
  381.  
  382. ' This takes care of the entire operation in one line, and allows others to see what these operations are being used for.
  383. Dim OutputFileName As String
  384. OutputFileName = Left(orgFilename, InStrRev(orgFilename, "")) & "processed_" & Replace$(Mid$(orgFilename, InStrRev(orgFilename, "") + 1), ".pl", ".csv")
  385.  
  386. OutputWorkbook.SaveAs Filename:=OutputFileName, _
  387. FileFormat:=xlCSV, _
  388. Password:="", _
  389. WriteResPassword:="", _
  390. ReadOnlyRecommended:=False, _
  391. CreateBackup:=False
  392. End Sub
  393. Private Function GetInputFileName() As String
  394. ' I use a variant declaration because the return of `Cancel` is the Boolean false.
  395. Dim InputFileNameResult As Variant
  396.  
  397. InputFileNameResult = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
  398.  
  399. If Not InputFileNameResult Then
  400. GetInputFileName = InputFileNameResult
  401. Else
  402. ' You can handle this as needed. For now, we just assume the user wants to exit the routine.
  403. ' As such, we do nothing.
  404. End If
  405. End Function
  406.  
  407. SomeWorkbook.Activate
  408. Sheets("SomeSheet").Select
  409. msgbox Cells(1,10)
  410.  
  411. msgbox SomeWorkbook.Sheets("SomeSheet").Cells(1,10).Value
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement