我是一个vbs noob,所以请原谅一个简单的问题。我创建了一个脚本来打开桌面上的.xlsx文档,并运行各种操作。我想将脚本移植到其他用户。也就是说,我如何创建一条适合所有用户的路径,即用户桌面变量。在PowerShell中,我可以进行'$env:USERPROFILE + 'Desktop''
,它将解决当前用户的桌面。有VBS等效吗?
到目前为止我拥有的东西:
Set xl = CreateObject("Excel.application")
xl.Application.Visible = True
Dim wb1
Set wb1 = xl.Application.Workbooks.Open("C:UsersUsernameDesktopMissed_ScansReportsReport.xlsx")
Dim wb2
Set wb2 = xl.Workbooks.Add
wb1.Sheets("Incomplete_ASINs").Range("$A$1:$J$52951").AutoFilter 1, "SDF8"
wb1.Sheets("Incomplete_ASINs").Columns("B:D").Copy
wb2.Worksheets(1).Paste
wb2.Worksheets(1).Rows(1).AutoFilter
wb2.SaveAs "C:UsersUsernameDesktopMissed_ScansReportsMissed_Scans.xlsx", 51, , , , False
wb2.Close
wb1.Close False
xl.Quit
Set xl = Nothing
第5和13行是需要使用某种类型的用户环境变量的领域。我了解environ("UserName")
可以提供用户名,但我不确定如何合并。
只需使用Expandenvironmentsstrings:
Set osh = CreateObject("wscript.shell")
xl.workbooks.open osh.ExpandEnvironmentStrings("%userprofile%DesktopMissed_ScansReportsReport.xlsx")
对于第13行,您也可以写出类似的内容:
wb2.SaveAs osh.ExpandEnvironmentStrings("%userprofile%DesktopMissed_ScansReportsMissed_Scans.xlsx"), 51, , , , False
更新:
注意:我没有对任何逻辑进行任何更改。刚刚删除了错误。
Set xl = CreateObject("Excel.application")
xl.Application.Visible = True
Dim wb1
Set osh = CreateObject("wscript.shell")
Set wb1 = xl.Workbooks.Open(osh.ExpandEnvironmentStrings("%userprofile%DesktopMissed_ScansReportsReport.xlsx"))
Dim wb2
Set wb2 = xl.Workbooks.Add
wb1.Sheets("Incomplete_ASINs").Range("$A$1:$J$52951").AutoFilter 1, "SDF8"
wb1.Sheets("Incomplete_ASINs").Columns("B:D").Copy
wb2.Worksheets(1).Paste
wb2.Worksheets(1).Rows(1).AutoFilter
wb2.SaveAs osh.ExpandEnvironmentStrings("%userprofile%DesktopMissed_ScansReportsMissed_Scans.xlsx"), 51, , , , False
wb2.Close
wb1.Close False
xl.Quit
Set xl = Nothing