vba - VBA中的左函数

Question

我有一个通过 power-shell 生成的输出文件，该文件提供共享转储和以下格式的权限:



我希望在 VBA 中编写一个模块，在该模块中我可以将原始数据放入一个名为 Input 的工作表中并让 marco 变得有趣，因此输出如下所示:



我对 VBA 非常陌生，但是在我的 Stackoverflow 社区中更改了一些代码，我已经做到了这一点:

Sub PathAccessSplit()

Dim wsFrom, wsTo As Worksheet
Dim rowFrom, rowTo, lastRow As Long
Dim cellVal As String

Set wsFrom = Sheets("Input")
Set wsTo = Sheets("Output")

lastRow = wsFrom.Cells(wsFrom.Rows.Count, "A").End(xlUp).Row
rowTo = 1

For rowFrom = 1 To lastRow
cellVal = wsFrom.Cells(rowFrom, 1).Text

If (Left(cellVal, 4) = "Name") Then
  wsTo.Cells(rowTo, 1).Value = cellVal
ElseIf (Left(cellVal, 8) = "FullName") Then
  wsTo.Cells(rowTo, 2).Value = cellVal
ElseIf (Left(cellVal, 18) = "InheritanceEnabled") Then
  wsTo.Cells(rowTo, 3).Value = cellVal
ElseIf (Left(cellVal, 13) = "InheritedFrom") Then
  wsTo.Cells(rowTo, 4).Value = cellVal
ElseIf (Left(cellVal, 17) = "AccessControlType") Then
  wsTo.Cells(rowTo, 5).Value = cellVal
ElseIf (Left(cellVal, 12) = "AccessRights") Then
  wsTo.Cells(rowTo, 6).Value = cellVal
ElseIf (Left(cellVal, 7) = "Account") Then
  wsTo.Cells(rowTo, 7).Value = cellVal
ElseIf (Left(cellVal, 16) = "InheritanceFlags") Then
  wsTo.Cells(rowTo, 8).Value = cellVal
ElseIf (Left(cellVal, 11) = "IsInherited") Then
  wsTo.Cells(rowTo, 9).Value = cellVal
ElseIf (Left(cellVal, 16) = "PropagationFlags") Then
  wsTo.Cells(rowTo, 10).Value = cellVal
ElseIf (Left(cellVal, 11) = "AccountType") Then
  wsTo.Cells(rowTo, 11).Value = cellVal

  rowTo = rowTo + 1
End If

但是输出只是转置输出，并且只输出一组结果，并没有移动到第二组权限。

我需要 VBA 足够强大以处理 1000 多组输出。

任何帮助将不胜感激

韦恩

Answer 1

而不是使用所有这些“如果，那么”，我会使用选择案例，
这是另一种方式。

Sub wsfrom_Pulsante1_Click()
Dim wsFrom  As Worksheet, wsTo As Worksheet             'otherwise the first is a variable
Dim rowFrom As Long, rowTo As Long, lastRow As Long
Dim cellVal As String
Set wsFrom = Sheets("Input")
Set wsTo = Sheets("Output")
lastRow = wsFrom.Cells(wsFrom.Rows.Count, "A").End(xlUp).Row
rowTo = 1
For rowFrom = 1 To lastRow
cellVal = wsFrom.Cells(rowFrom, 1).text
If cellVal = "" Then    'the blanck row between one block to another
    rowTo = rowTo + 1   'ad 1 for the next row in wsTo
End If
On Error Resume Next    'jump the error Left(cellVal, InStr(cellVal, " ") - 1) because the cell is ""
Select Case Left(cellVal, InStr(cellVal, " ") - 1)
    Case "Name"
        wsTo.Cells(rowTo, 1).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "FullName"
        wsTo.Cells(rowTo, 2).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "InheritanceEnabled"
        wsTo.Cells(rowTo, 3).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "InheritedFrom"
        wsTo.Cells(rowTo, 4).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "AccessControlType"
        wsTo.Cells(rowTo, 5).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "AccessRights"
        wsTo.Cells(rowTo, 6).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "Account"
        wsTo.Cells(rowTo, 7).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "InheritanceFlags"
        wsTo.Cells(rowTo, 8).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "IsInherited"
        wsTo.Cells(rowTo, 9).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "PropagationFlags"
        wsTo.Cells(rowTo, 10).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
    Case "AccountType"
        wsTo.Cells(rowTo, 11).Value = Mid(cellVal, (InStr(cellVal, ":") + 1))
End Select
Next rowFrom
End Sub