Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub Button1_Click()
- Dim orgFilename As String
- Dim temp As String
- Dim strarray(3) As String
- Dim vert(4) As String
- Dim vert2(3) As String
- Dim newFilename As String
- Dim numRows As Integer
- Dim i As Integer
- Dim j As Integer
- Dim k As Integer
- Dim segCount As Integer
- Dim vertex(3, 100) As Double
- Dim oldwb As Workbook
- Dim newwb As Workbook
- orgFilename = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
- If orgFilename = "False" Then Exit Sub
- Workbooks.OpenText Filename:=orgFilename, _
- Origin:=950, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
- xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
- Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
- Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
- Set oldwb = ActiveWorkbook
- Set newwb = Workbooks.Add
- oldwb.Activate
- Cells(5, 1).Select
- numRows = Cells(5, 1).End(xlDown).Row
- ' Parse through data
- segCount = 0
- j = 1
- For i = 5 To numRows
- If Cells(i, 1) <> "VRTX" And segCount <> 0 Then
- For k = 1 To segCount - 1
- newwb.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
- With newwb.Worksheets("Sheet1")
- .Cells(j, 2) = vertex(1, k)
- .Cells(j, 3) = vertex(3, k)
- .Cells(j, 4) = vertex(2, k)
- .Cells(j, 5) = vertex(1, k + 1)
- .Cells(j, 6) = vertex(3, k + 1)
- .Cells(j, 7) = vertex(2, k + 1)
- End With
- j = j + 1
- Next k
- segCount = 0
- ElseIf Cells(i, 1) = "VRTX" Then
- ' Save vertices to save an endpoint
- vertex(1, segCount + 1) = Cells(i, 3)
- vertex(2, segCount + 1) = Cells(i, 4)
- vertex(3, segCount + 1) = Cells(i, 5)
- segCount = segCount + 1
- End If
- Next i
- ' Save as a new file
- temp = Mid$(orgFilename, InStrRev(orgFilename, "") + 1)
- temp = Replace$(temp, ".pl", ".csv")
- strarray(1) = Left(orgFilename, InStrRev(orgFilename, ""))
- strarray(2) = "processed_"
- strarray(3) = temp
- newFilename = Join(strarray, "")
- newwb.SaveAs Filename:=newFilename, _
- FileFormat:=xlCSV, _
- Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
- CreateBackup:=False
- End Sub
- Sub main()
- openPlFile
- readPlFile
- writeCsvFile
- saveCsvFile
- End Sub
- On Error GoTo error_handler
- Exit Sub
- error_handler:
- 'code to handle the error for example:
- MsgBox "There was an error: " & Err.Description
- End Sub
- Workbooks.OpenText Filename:=orgFilename, _
- Origin:=950, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
- xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
- Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
- Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True`
- Workbooks.OpenText Filename:=orgFilename, _
- Origin:=950, _
- StartRow:=1, _
- DataType:=xlDelimited, _
- TextQualifier:= xlDoubleQuote, _
- ConsecutiveDelimiter:=True, _
- Tab:=True, _
- Semicolon:=False, _
- Comma:=False, _
- Space:=True, _
- Other:=False, _
- FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
- TrailingMinusNumbers:=True`
- Option Explicit
- Sub Button1_Click()
- ' Constants are declared at the beginning of the routine.
- Const ROW_SKIP As Long = 5
- ' Avoid Dim blocks like these. It is always best to declare variables as close to their initial use
- ' as possible. This makes your code easier to read/maintain as well.
- 'Dim orgFilename As String
- 'Dim temp As String
- 'Dim strarray(3) As String
- 'Dim vert(4) As String
- 'Dim vert2(3) As String
- 'Dim newFilename As String
- 'Dim numRows As Integer
- 'Dim i As Integer
- 'Dim j As Integer
- 'Dim k As Integer
- 'Dim segCount As Integer
- 'Dim vertex(3, 100) As Double
- '
- 'Dim oldwb As Workbook
- 'Dim newwb As Workbook
- ' I will declare the variable name, but I will also use a name that is slightly more descriptive.
- ' This will allow others to understand what I am doing. I also encapsulate this in a function to allow for
- ' easy error handling.
- 'orgFilename = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
- ' Instead of just exiting the sub, handle this error.
- ' If orgFilename = "False" Then Exit Sub
- Dim InputFileName As String
- InputFileName = GetInputFileName
- If InputFileName = vbNullString Then
- ' We can add a messagebox here if needed. For now, we just exit the routine silently.
- Exit Sub
- End If
- ' For your field info here, you are using an uninitialized, undeclared, array. What effect are you intending to achieve?
- Workbooks.OpenText _
- Filename:=orgFilename, _
- Origin:=950, _
- StartRow:=1, _
- DataType:=xlDelimited, _
- TextQualifier:= _
- xlDoubleQuote, _
- ConsecutiveDelimiter:=True, _
- Tab:=True, Semicolon:=False, _
- Comma:=False, _
- Space:=True, _
- Other:=False, _
- FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
- TrailingMinusNumbers:=True
- ' I declare more descriptive workbook variable names, and separate the assignments.
- ' Set oldwb = ActiveWorkbook
- ' Set newwb = Workbooks.Add
- ' I am changing this to a Sheet reference since you seem to be referring to the ActiveSheet implicitly, and not just the ActiveWorkbook
- Dim CurrentWorksheet As Worksheet
- Set CurrentWorksheet = ActiveSheet
- ' While the default scope of `Workbooks.Add` is `Application.Workbooks.Add` it is better to be explicit.
- Dim OutputWorkbook As Workbook
- Set OutputWorkbook = Application.Workbooks.Add
- ' No need for Activate. Try to avoid this behavior.
- ' oldwb.Activate
- ' Avoid Select as well
- ' Cells(5, 1).Select
- ' numRows = Cells(5, 1).End(xlDown).Row
- ' Declare new variable, and qualify the range reference when finding the row. Without the qualifying reference
- ' to `CurrentWorkbook` the `Cells` reference refers to the `ActiveWorkbook`.
- Dim NumberOfRows As Long
- NumberOfRows = CurrentWorksheet.Cells(5, 1).End(xlDown).Row
- ' Instead of making changed within the loop, I am just going to rewrite it to make changes easier to read.
- ' Parse through data
- 'segCount = 0
- 'j = 1
- 'For i = 5 To numRows
- ' If Cells(i, 1) <> "VRTX" And segCount <> 0 Then
- ' For k = 1 To segCount - 1
- ' newwb.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
- ' With newwb.Worksheets("Sheet1")
- ' .Cells(j, 2) = vertex(1, k)
- ' .Cells(j, 3) = vertex(3, k)
- ' .Cells(j, 4) = vertex(2, k)
- ' .Cells(j, 5) = vertex(1, k + 1)
- ' .Cells(j, 6) = vertex(3, k + 1)
- ' .Cells(j, 7) = vertex(2, k + 1)
- ' End With
- ' j = j + 1
- ' Next k
- ' segCount = 0
- ' ElseIf Cells(i, 1) = "VRTX" Then
- ' ' Save vertices to save an endpoint
- ' vertex(1, segCount + 1) = Cells(i, 3)
- ' vertex(2, segCount + 1) = Cells(i, 4)
- ' vertex(3, segCount + 1) = Cells(i, 5)
- ' segCount = segCount + 1
- ' End If
- 'Next i
- ' Assumes that the UsedRange of the Input sheet is the data we need
- Dim InputData As Variant
- InputData = CurrentWorksheet.UsedRange.Value
- Dim SegmentCount As Long
- Dim i As Long
- Dim j As Long
- Dim k As Long
- j = 1
- ' Re-creating your vertex array though it is not at all clear what it is being used for.
- Dim Vertices As Variant
- ReDim Vertices(3, 100)
- ' I use a constant variable instead of 5 here since the 5 may change, and it can be difficult to track it down later.
- For i = ROW_SKIP To NumberOfRows
- ' Note: This will always return false on the first pass since SegmentCount will always equal 0
- If InputData(i, 1) <> "VRTX" And SegmentCount <> 0 Then
- For k = 1 To segCount - 1
- OutputWorkbook.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
- With OutputWorkbook.Worksheets("Sheet1")
- .Cells(j, 2) = Vertices(1, k)
- .Cells(j, 3) = Vertices(3, k)
- .Cells(j, 4) = Vertices(2, k)
- .Cells(j, 5) = Vertices(1, k + 1)
- .Cells(j, 6) = Vertices(3, k + 1)
- .Cells(j, 7) = Vertices(2, k + 1)
- End With
- j = j + 1
- Next k
- SegmentCount = 0
- ElseIf InputData(i, 1) = "VRTX" Then
- Vertices(1, SegmentCount + 1) = InputData(i, 3)
- Vertices(2, SegmentCount + 1) = InputData(i, 4)
- Vertices(3, SegmentCount + 1) = InputData(i, 5)
- SegmentCount = SegmentCount + 1
- End If
- Next i
- ' This can be condensed into a much more concise format
- ' Save as a new file
- ' temp = Mid$(orgFilename, InStrRev(orgFilename, "") + 1)
- ' temp = Replace$(temp, ".pl", ".csv")
- ' strarray(1) = Left(orgFilename, InStrRev(orgFilename, ""))
- ' strarray(2) = "processed_"
- ' strarray(3) = temp
- ' newFilename = Join(strarray, "")
- Dim OutputFileName As String
- ' This takes care of the entire operation in one line, and allows others to see what these operations are being used for.
- OutputFileName = Left(orgFilename, InStrRev(orgFilename, "")) & "processed_" & Replace$(Mid$(orgFilename, InStrRev(orgFilename, "") + 1), ".pl", ".csv")
- OutputWorkbook.SaveAs Filename:=OutputFileName, _
- FileFormat:=xlCSV, _
- Password:="", _
- WriteResPassword:="", _
- ReadOnlyRecommended:=False, _
- CreateBackup:=False
- End Sub
- Private Function GetInputFileName() As String
- ' I use a variant declaration because the return of `Cancel` is the Boolean false.
- Dim InputFileNameResult As Variant
- InputFileNameResult = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
- If Not InputFileNameResult Then
- GetInputFileName = InputFileNameResult
- Else
- ' You can handle this as needed. For now, we just assume the user wants to exit the routine.
- ' As such, we do nothing.
- End If
- End Function
- Option Explicit
- Sub Button1_Click()
- ' Constants are declared at the beginning of the routine.
- Const ROW_SKIP As Long = 5
- Dim InputFileName As String
- InputFileName = GetInputFileName
- If InputFileName = vbNullString Then
- ' We can add a messagebox here if needed. For now, we just exit the routine silently.
- Exit Sub
- End If
- ' For your field info here, you are using an uninitialized, undeclared, array. What effect are you intending to achieve?
- Workbooks.OpenText _
- Filename:=orgFilename, _
- Origin:=950, _
- StartRow:=1, _
- DataType:=xlDelimited, _
- TextQualifier:= _
- xlDoubleQuote, _
- ConsecutiveDelimiter:=True, _
- Tab:=True, Semicolon:=False, _
- Comma:=False, _
- Space:=True, _
- Other:=False, _
- FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
- TrailingMinusNumbers:=True
- ' I am changing this to a Sheet reference since you seem to be referring to the ActiveSheet implicitly, and not just the ActiveWorkbook
- Dim CurrentWorksheet As Worksheet
- Set CurrentWorksheet = ActiveSheet
- ' While the default scope of `Workbooks.Add` is `Application.Workbooks.Add` it is better to be explicit.
- Dim OutputWorkbook As Workbook
- Set OutputWorkbook = Application.Workbooks.Add
- ' Declare new variable, and qualify the range reference when finding the row. Without the qualifying reference
- ' to `CurrentWorkbook` the `Cells` reference refers to the `ActiveWorkbook`.
- Dim NumberOfRows As Long
- NumberOfRows = CurrentWorksheet.Cells(5, 1).End(xlDown).Row
- ' Assumes that the UsedRange of the Input sheet is the data we need
- Dim InputData As Variant
- InputData = CurrentWorksheet.UsedRange.Value
- Dim SegmentCount As Long
- Dim i As Long
- Dim j As Long
- Dim k As Long
- j = 1
- ' Re-creating your vertex array though it is not at all clear what it is being used for.
- Dim Vertices As Variant
- ReDim Vertices(3, 100)
- ' I use a constant variable instead of 5 here since the 5 may change, and it can be difficult to track it down later.
- For i = ROW_SKIP To NumberOfRows
- ' Note: This will always return false on the first pass since SegmentCount will always equal 0
- If InputData(i, 1) <> "VRTX" And SegmentCount <> 0 Then
- For k = 1 To segCount - 1
- OutputWorkbook.Worksheets("Sheet1").Cells(j, 1) = "GLINE"
- With OutputWorkbook.Worksheets("Sheet1")
- .Cells(j, 2) = Vertices(1, k)
- .Cells(j, 3) = Vertices(3, k)
- .Cells(j, 4) = Vertices(2, k)
- .Cells(j, 5) = Vertices(1, k + 1)
- .Cells(j, 6) = Vertices(3, k + 1)
- .Cells(j, 7) = Vertices(2, k + 1)
- End With
- j = j + 1
- Next k
- SegmentCount = 0
- ElseIf InputData(i, 1) = "VRTX" Then
- Vertices(1, SegmentCount + 1) = InputData(i, 3)
- Vertices(2, SegmentCount + 1) = InputData(i, 4)
- Vertices(3, SegmentCount + 1) = InputData(i, 5)
- SegmentCount = SegmentCount + 1
- End If
- Next i
- ' This takes care of the entire operation in one line, and allows others to see what these operations are being used for.
- Dim OutputFileName As String
- OutputFileName = Left(orgFilename, InStrRev(orgFilename, "")) & "processed_" & Replace$(Mid$(orgFilename, InStrRev(orgFilename, "") + 1), ".pl", ".csv")
- OutputWorkbook.SaveAs Filename:=OutputFileName, _
- FileFormat:=xlCSV, _
- Password:="", _
- WriteResPassword:="", _
- ReadOnlyRecommended:=False, _
- CreateBackup:=False
- End Sub
- Private Function GetInputFileName() As String
- ' I use a variant declaration because the return of `Cancel` is the Boolean false.
- Dim InputFileNameResult As Variant
- InputFileNameResult = Application.GetOpenFilename(FileFilter:="All files (*.), *.", Title:="Please select a file")
- If Not InputFileNameResult Then
- GetInputFileName = InputFileNameResult
- Else
- ' You can handle this as needed. For now, we just assume the user wants to exit the routine.
- ' As such, we do nothing.
- End If
- End Function
- SomeWorkbook.Activate
- Sheets("SomeSheet").Select
- msgbox Cells(1,10)
- msgbox SomeWorkbook.Sheets("SomeSheet").Cells(1,10).Value
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement