如何将变量存储在.txt文件中，并在多个工作簿中使用它自动填充用户表单文本框

Question

我已经创建了一个用户表单来保存和打印文件，并在每个副本上增加序列号。用户表单提示用户输入下一个可用序列号以及要创建多少副本。一旦用户单击"create“按钮，它将填充一个单元格，保存一个新副本，打印副本，然后在循环之前将1添加到序列号中。我要做的是将最终序列号存储在保存在网络文件夹中的.txt文件中，然后在另一个工作簿中检索该编号，以便自动填充用户表单中的序列号文本框。这个是可能的吗？variale可以用来自动填充用户表单文本框吗？

模块打开可用表格：

Sub start()
    '''Retrieve stored serial number to autofill userform'''
    WO_BulkCreate.Show
End Sub

​

​

userform按钮的代码(来自userform文本框的nextWOnumber和createAmount )：

Private Sub Cancel_Click()
    Unload WO_BulkCreate
End Sub

Private Sub Create_Click()
    'Variables
    Dim Amount As Integer                        'Number of WOs to be created
    Dim WO_Name As String                        'WO number
    Dim i As Integer                             'loop tracker
    Dim saveName As String                       'New File Name

    'initialize variables
    Amount = createAmount
    i = 0

    'Begin loop
    Do While i < Amount

        'Assemble WO number and filename
        WO_Name = "Customer Code-" & nextWOnumber
        saveName = WO_Name & " - part number - part description"

        'Update WO number Cell
        Sheets("WO Charge Sheet").Range("WO") = WO_Name

        'Save New Copy
        ActiveWorkbook.SaveAs Filename:="C:\filepath\" & saveName

        'Set orientation to landscape
        Worksheets("WO Charge Sheet").PageSetup.Orientation = xlLandscape
        Worksheets("Ops Planning").PageSetup.Orientation = xlLandscape

        'Print WO
        Sheets(Array("WO Charge Sheet", "Ops Planning")).PrintOut

        'Increment WO number
        nextWOnumber = nextWOnumber + 1

        'Step loop
        i = i + 1

        'End loop
    Loop

    '''Store final serial number in text file'''

    'Close form
    Unload WO_BulkCreate
End Sub

我希望这是有意义的。对不起，如果这是tmi，这是我第一次在

面包豆瓣

Answer 1

这就是我解决这个问题的方法。

存储变量：

'Store next serial number
    Dim fileNo As Integer, serialNo As String

    fileNo = FreeFile

    Open "C:\filepath\nextSerialNumber.txt" For Output As #fileNo
    Write #fileNo, serialNo; 'I had to add this semicolon to prevent it from writing a linebreak

'Close file
    Close #fileNo

这可以用来存储我的变量，但它确实在它之后添加了一个逗号。在检索变量的代码中，我为逗号找到了一个解决方案：

Private Sub UserForm_Initialize()

'Variables
    Dim serialNo As String, fileNo As Integer

'Intialize
    fileNo = FreeFile

'Read next serial number
    Open "C:\filepath\nextSerialNumber.txt" For Input As #fileNo
    serialNo = Input$(LOF(fileNo), fileNo)

'Remove Comma
    If (Right(serialNo, 1)) = "," Then
        serialNo = Left(serialNo, Len(serialNo) - 1)
    End If

'Fill Text Boxes
    nextSerialNumber.Value = serialNo

'End Read File
    Close #fileNo

End Sub

这对我来说很好，希望这对我有帮助。

Answer 2

类似这样的东西可以工作，但您需要注意可能的争用问题，这将需要稍微复杂的方法，可能使用一个“锁”文件，以确保一次只能读取一个用户.

Sub Tester()
    Debug.Print NextSequence()
End Sub


Function NextSequence() As String
    Const SEQ_FILE As String = "C:\Tester\seq.txt" '<<location of the sequence file
    Dim txt As String, seq As Long
    With CreateObject("scripting.filesystemobject")
        If .fileexists(SEQ_FILE) Then  '<< does the file exist ?
            txt = Trim(.opentextfile(SEQ_FILE, 1).readall())
            If IsNumeric(txt) Then
                seq = CLng(txt)
            End If
        Else
            seq = 1  '<< no file yet, start with 1
        End If
        With .createtextfile(SEQ_FILE)
            .writeline seq + 1 '<<write the next sequence
        End With
    End With
    NextSequence = seq
End Function