长话短说,感谢您的阅读:)
我正在尝试自动分析AD导出。我发送客户PowerShell脚本以将AD数据导出到CSV。他们向我发送CSV文件,然后将它们导入到名为WeGalvanice ACL(简称ACL)的分析工具中。在ACL中,我可以进行过滤/匹配/比较以及我需要做的任何事情。
到目前为止一切都很好。现在,我想使用这些结果并将它们放在.docx文件中,这会带来麻烦。ACL无法导出到docx,只能导出到xlsx / txt / csv / html / json / del。
确定好我们可以变通解决(或所以我想),我做了一个template.docx
内template.docx
我写的占位符一样%certainvariable%
。
在ACL中,我创建了一个命令,将需要的所有变量写到被调用的文件中%variablename%.txt
,而txt中的值是我需要的值。
例如:
ACL创建disabledusers.txt
。如果您打开txt,它只会显示10,这意味着我们有10个残疾用户。
然后,我尝试创建一个VBA宏来导入txt文件,并使用包含的值替换%...%
Word文档中的相应文本。
一切看起来都很顺利,直到我注意到运行脚本后,我看到那个20
框,意思是“我不知道如何显示此字符”。
在运行脚本之前:
运行脚本后:
我导入的.txt内容图片:
最后但并非最不重要的一点是从单词Macro manager中使用的VBS代码(为方便起见,我仅上传了1个导入和替换作业,但在我的脚本中,我只是为每个文件和变量复制了30次):
Sub ACL()
' ACL Macro
FilePath = "local folderpath\date.txt"
TextFile = FreeFile
Open FilePath For Input As TextFile
new_date = Input(LOF(TextFile), TextFile)
old_date = "%date%"
Close TextFile
With ActiveDocument.Content.Find
.Forward = True
.Wrap = wdFindStop
.Execute findtext:=old_date, replacewith:=new_date, Replace:=wdReplaceAll
End With
End Sub
当我打开手册date.txt
并删除第二行时,这一切似乎正常。但是我要自动化。;)
我试过了
split textfile,/n
并split new_date,/n
都与\n\r
和\r
无济于事。我愿意就任何编程语言提出建议,(对它们来说都是可怕的),只是按正确的顺序将所有内容排入队列以使其正常工作;)
预先感谢大家!当然问你是否需要更多信息
更新感谢Tomalak我得到了原理的工作:
' removes CR & LF from the end of a string (not pretty but gets the job done)
Function TrimRight(InputStr As String) As String
While Right(InputStr, 1) = vbCr Or Right(InputStr, 1) = vbLf
InputStr = Left(InputStr, Len(InputStr) - 1)
Wend
TrimRight = Trim(InputStr)
End Function
' reads any text file and returns the content as-is
Function ReadFile(FilePath As String) As String
Dim filenum As Integer
If Dir(FilePath) > "" Then
filenum = FreeFile
Open FilePath For Input As filenum
ReadFile = Input(LOF(filenum), filenum)
Close filenum
Else
Debug.Print "File not found: " & FilePath
End If
End Function
' replaces all instances of a single word in the given document
Sub ReplaceAll(oldValue As String, newValue As String, doc As Document)
With doc.Content.Find
.Forward = True
.Wrap = wdFindStop
.Execute findtext:=oldValue, replacewith:=newValue, Replace:=wdReplaceAll
End With
End Sub
Sub ReplaceAllVariables()
Dim variables As Variant, variable As Variant
Dim placeholder As String, realValue As String
variables = Split("customer,c_adgroup,c_adgroup_Cannot_change_PW,c_adgroup_disabled,c_adgroup_enabled,c_adgroup_expire_and_disabled,c_adgroup_LockedOut,c_adgroup_Locked_and_disabled,c_adgroup_loginx,c_adgroup_loginx_disabled,c_adgroup_nolastpasswordset,c_adgroup_nologondate,c_adgroup_PWRQ_and_disabled,c_adgroup_PWx,c_adgroup_PWx_and_disabled,c_adgroup_PW_and_disabled,c_adgroup_PW_and_enabled,c_adgroup_PWRQ_and_enabled ,c_adgroup_PW_not_required,c_adgroup_PW_no_expire,c_adgroup_loginx_and_enabled,c_adusers_f_Cannot_change_PW,c_adusers_f_disabled,c_adusers_f_enabled,c_adusers_f_expire_and_disabled,c_adusers_f_LockedOut,c_adusers_f_Locked_and_disabled,c_adusers_f_loginx,c_adusers_f_loginx_disabled,c_adusers_f_nolastpasswordset,c_adusers_f_nologondate,c_adusers_f_PWRQ_and_disabled,c_adusers_f_PWx,c_adusers_f_PWx_and_disabled,c_adusers_f_PW_and_disabled,c_adusers_f_PW_not_required,c_adusers_f_PW_no_expire,c_adusers_f_loginx_and_enabled,date,c_adusers_f_PW_and_enabled,lastlogon", ",")
For Each variable In variables
placeholder = "%" & variable & "%"
realValue = ReadFile("C:\Users\jerryw\Documents\Klanten\ACL\AD analyse\var\" & variable & ".txt")
realValue = TrimRight(realValue)
ReplaceAll placeholder, realValue, ActiveDocument
Next
End Sub
问题似乎是换行符(CRLF)。您必须摆脱它。
而且,虽然我们正在使用它,但也摆脱了“我已将该代码复制了30次”位,这是解决此问题的可怕方法。循环更好。
帮手:
' removes CR & LF from the end of a string (not pretty but gets the job done)
Function TrimRight(InputStr As String) As String
While Right(InputStr, 1) = vbCr Or Right(InputStr, 1) = vbLf
InputStr = Left(InputStr, Len(InputStr) - 1)
Wend
TrimRight = Trim(InputStr)
End Function
' reads any text file and returns the content as-is
Function ReadFile(FilePath As String) As String
Dim filenum As Integer
If Dir(FilePath) > "" Then
filenum = FreeFile
Open FilePath For Input As filenum
ReadFile = Input(LOF(filenum), filenum)
Close filenum
Else
Debug.Print "File not found: " & FilePath
End If
End Function
' replaces all instances of a single word in the given document
Sub ReplaceAll(oldValue As String, newValue As String, doc As Document)
With doc.content.Find
.Forward = True
.Wrap = wdFindStop
.Execute findtext:=oldValue, replacewith:=newValue, Replace:=wdReplaceAll
End With
End Sub
工人:
Sub ReplaceAllVariables()
Dim variables As Variant, variable As Variant
Dim placeholder As String, realValue As String
variables = Split("date,disabledusers,and,any,other,variable,you,have", ",")
For Each variable In variables
placeholder = "%" & variable & "%"
realValue = ReadFile("\local folderpath\" & variable & ".txt")
realValue = TrimRight(realValue)
ReplaceAll placeholder, realValue, ActiveDocument
Next
End Sub
该TrimRight()
函数仅从给定的输入字符串中去除最后的换行符。内容中间的任何换行符都会保留。如果您的数据可能发生这种情况,Word将为LF
找到的每个段落创建新的段落。并且它将为CR
找到的每一个画框。
如果确实有多行txt文件,但又不想有新的段落,也不想有框,则在使用之前修改文本文件的内容(除去CR,用垂直制表符替换LF ,这是Word表示“软休息”的方式,即使用Shift + Enter可以得到相同的结果):
realValue = ReadFile("\local folderpath\" & variable & ".txt")
realValue = TrimRight(realValue)
realValue = Replace(Replace(realValue, vbCr, ""), vbLf, vbVerticalTab)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句