EXCEL+VBA 管理windows用户文件服务器用户新增或删除用户

本是古典何须时尚 2022-10-02 03:53 147阅读 0赞

1.调用DOS命令：net user 获取系统用户到txtfile  
2.打开txtfile把内容处理（空格全替换为换行；已换行分列）后赋值到A  
3.把A列不为空的值且不到特殊用户表的值写到临时表  
4.与EXCEL用户表进行对比 使用函数COUNTIF  
（1）临时表有，用户表没有——编写删除用户命令： "net user " & Range("a" & i) & " /DELETE"

（2）临时表没有，用户表有——编写新增用户命令："net user " & Range("a" & i) & " " & Range("B" & i) & " /add /fullname:" & Chr(34) & Range("D" & i) & Chr(34) & " /comment:" & Chr(34) & Range("E" & i) & Range("F" & i) & Chr(34)  
写到batfile文件；

5.执行batfile脚本，实现windows用户与EXCEL用户同步；

视频教程制作中：[https://edu.51cto.com/lecturer/6969164.html][https_edu.51cto.com_lecturer_6969164.html]

VBA代码如下：

Option Explicit
    Dim a, K%, i%, z%, ii%
        Public Const batfile   As String = "D:\TOOLS\smbr.BAT"
    Public Const txtfile  As String = "D:\TOOLS\windowssmb.txt"
    
    Sub UPDATEUSERS()
        Shell "cmd.exe /c " & " net user >" & txtfile, vbHide
         t = Timer + 0.3
         Do Until Timer > t
         DoEvents
         Loop
     Sheets("SMB").Select
     Columns("A:A").Select
     Selection.ClearContents
     Range("A1") = "SMBUSERS"
         Open txtfile For Input As #1
         a = Split(Replace(StrConv(InputB(LOF(1), 1), vbUnicode), " ", vbCrLf), vbCrLf)
         Close #1
         K = UBound(a)
         z = 2
    For i = 5 To K
              If a(i) <> "" And Application.WorksheetFunction.CountIf(Sheets("SPUSERS").Range("A:A"), a(i)) = 0 And a(i) <> "命令成功完成。" Then
              Cells(z, 1) = a(i)
              z = z + 1
              End If
    Next
        Sheets("SMB").Select
        Open batfile For Append As #1
          h = Range("A65535").End(xlUp).Row
          For i = 2 To h
            If Application.WorksheetFunction.CountIf(Sheets("DATA").Range("A:A"), Range("A" & i)) = 0 Then
            Print #1, "net user " & Range("a" & i) & " /DELETE"
            End If
          Next
    
        Sheets("DATA").Select
          h = Range("A65535").End(xlUp).Row
          For i = 2 To h
            If Application.WorksheetFunction.CountIf(Sheets("SMB").Range("A:A"), Range("A" & i)) = 0 Then
            Print #1, "net user " & Range("a" & i) & " " & Range("B" & i) & " /add /fullname:" & Chr(34) & Range("D" & i) & Chr(34) & " /comment:" & Chr(34) & Range("E" & i) & Range("F" & i) & Chr(34)
            Print #1, "NET LOCALGROUP  Users  " & Range("a" & i) & " /Delete"
            End If
          Next
         Close #1
    
    End Sub

[![EXCEL+VBA 管理windows用户 文件服务器用户  新增或删除用户][EXCEL_VBA _windows_ _]][EXCEL_VBA _windows_ _ _EXCEL_VBA _windows_]

转载于:https://blog.51cto.com/mflag/2373923

[https_edu.51cto.com_lecturer_6969164.html]: https://edu.51cto.com/lecturer/6969164.html
[EXCEL_VBA _windows_ _]: https://s1.51cto.com/images/blog/201904/08/1bbb5b2970b0c4b2eef5ea14591d3ec9.jpg?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[EXCEL_VBA _windows_ _ _EXCEL_VBA _windows_]: /images/20220113/b15edbeec25d4cb9a43bf2c3afe5e2d2.png