excel - 如何更改引用输入框的变体？

问题描述

我正在尝试将 file_split 脚本简化为我部门中的自助服务点。没有人真正了解该语言，因此我正在检查是否可以进一步简化其中的任何内容，以便同事不必从编辑器窗格更新代码。

例如，我有一些事情想Basepath指定文件将被保存在哪里。我该如何改变

Dim Basepath As String
Basepath = "C:\Users\File Cuts\" 
directory as string

到这样的用户可以选择文件夹路径的东西？

Dim Basepath as filedialog
with basepath
    .title = "Select save location"
    .directory = .selecteditems(1)
end with

然后是我有特定列要引用的实例（每个新文件的目标值列、命名约定列等......）

如：

Dim Manager_Name, Login_ID, Leader 

Manager_Name = SourceData(i,4)
Login_ID = SourceData(i,5)
Leader = SourceData(i,9)

由列字母的输入框输入，例如：

Dim column_selection as variant

column_selection = InputBox("Enter Column Letter") 
Manager_Name = SourceData(i,column_selection)

有很多参考资料我想看看是否可以更改，以便在不实际接触代码的情况下进行编辑（名称和登录 ID 等变体的列范围将发生很大变化）

其余代码：

Option Explicit
Sub File_Splits()
    Dim Wb As Workbook
    Dim SourceData, Mgr_Name, Login_Id
    Dim i As Long, j As Long, k As Long, a As Long
    Dim Destination_Cell As Range
    Dim Basepath As String, strNewpath As String, strLeader As String
    Basepath = "C:\File Cuts\" '1. paste in file save pathway, keep last \
    Set Wb = Workbooks.Open("C:\File_Split_Mgr_Template.xlsx") '2. paste template ws address here
    Set Destination_Cell = Wb.Worksheets("Manager Data").Range("A2") '3. Update worksheet name and target cell
    With ThisWorkbook.Worksheets("Roster")
        SourceData = .Range("I10", .Range("A" & Rows.Count).End(xlUp)) '4. change I10 to your last column letter, dont change the number(keep the 10)
    End With
    Wb.Activate
    Call Speed_Up_Code(True)
    For i = 1 To UBound(SourceData)
        If SourceData(i, 5) <> Login_Id Then '5. change the 1 to login column #
            If i > 9 Then
                Destination_Cell.Select

                strNewpath = Basepath & strLeader & "\" 'comment this out if folders aren't needed
                If Len(Dir(strNewpathD, vbDirectory)) = 0 Then 'comment this out if folders aren't needed
                    MkDir strNewpath 'comment this out if folders aren't needed
                End If 'comment this out if folders aren't needed

                Wb.SaveCopyAs strNewpath & _
                ValidFileName(Login_Id & "_" & Mgr_Name & "_File Name.xlsx") '6. update file name
            End If
            With Wb.Worksheets("Manager Data") '7. change to template sheet
                .Rows(2 & ":" & .Rows.Count).ClearContents '8. change 2 to row after header(s)--if header isn't in row 1
            End With
            Mgr_Name = SourceData(i, 4) '9. change 1 to mgr name column
            Login_Id = SourceData(i, 5) '10. change 2 to login ID column
            strLeader = SourceData(i, 9) '11. change 5 to lvl 3 mgr column
            j = 0
        End If
        a = 0
        For k = 1 To UBound(SourceData, 2)
            Destination_Cell.Offset(j, a) = SourceData(i, k)
            a = a + 1
        Next
        j = j + 1
    Next
    If Len(Dir(strNewpath, vbDirectory)) = 0 Then
        MkDir strNewpath
    End If
    SaveCopy Wb, strNewpath, Login_Id, Mgr_Name
    Call Speed_Up_Code(False)
End Sub
Public Sub SaveCopy(Wb As Workbook, strNewpath As String, Login_Id, Mgr_Name)
    Wb.SaveCopyAs strNewpath & _
        ValidFileName(Login_Id & "_" & Mgr_Name & "_File Name.xlsx") '12. update file name
End Sub

标签： excelvba

excel - 如何更改引用输入框的变体？

问题描述

解决方案

推荐阅读