Excel单元格，用于创建将自动更新并与sharepoint同步的文件夹

Question

我正在寻找从excel单元格创建文件夹的方法，当在单元格中输入新值时，文件夹会自动更新。我还需要这些文件夹与SharePoint同步。

到目前为止，我已经尝试了各种方法，包括VBA模块，这些模块似乎在应该工作的时候无法工作，我使用了kutools，它创建文件夹，但在创建新单元格时需要手动更新(也不能与SharePoint同步，也不能在没有kutools的任何其他设备上工作(。

我试过这个：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Z As Long
Dim xVal As String
On Error Resume Next
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Z = 1 To Target.Count
If Target(Z).Value > 0 Then
Call MakeFolders
End If
Next
Application.EnableEvents = True
End Sub
Sub MakeFolders()
Dim Rng As Range
Dim LastRow As Long
Dim LastColumn As Long
Dim maxRows As Integer
Dim r As Integer
Dim c As Integer
Set sht = Worksheets("Sheet1")
Set StartCell = Range("A1")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
LastColumn = 1
sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select
Set Rng = Selection
maxRows = Rng.Rows.Count
For c = 1 To 1
r = 1
Do While r <= maxRows
If Len(Dir(ActiveWorkbook.Path & "" & Rng(r, c), vbDirectory)) = 0 Then
MkDir (ActiveWorkbook.Path & "" & Rng(r, c))
On Error Resume Next
End If
r = r + 1
Loop
Next c
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
End Sub

Answer 1

您似乎正在检测A列中进行了多少更改，并且您有Target来告诉您这些更改是什么，但您随后试图为A列中的每个条目构建文件夹，根据所做的更改数量重复。

在我看来，您最好为检测到的每个更改调用MakeFolders例程，但只在当时正在测试的单元上调用Target作为MakeFolders的参数。

试试这样的东西：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cellchange As Range
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each cellchange In Intersect(Target, Range("A:A")).Cells
If cellchange.Value <> "" Then
MakeFolders ActiveWorkbook.Path & "" & cellchange.Value
End If
Next
Application.EnableEvents = True
End Sub
Sub MakeFolders(folderpath As String)
If Len(Dir(folderpath, vbDirectory)) = 0 Then
MkDir (folderpath)
End If
End Sub

您可能希望在MakeFolders子中添加常识性检查和错误捕获