Excel VBA form that will save data ina csv file-reusable

[HansV]

See if this works better.

Code: Select all

Private Sub cmdSubmit_Click()
    Dim i As Integer
    Dim f As Integer
    Dim selectedPeople As String
    Dim selectedAssets As String
    Dim selectedAssembly As String
    Dim selectedProcedures As String
    Dim selectedTests As String
    Dim strList As String
    Dim ctl As Control

    'Make certain fields required
    If Me.cboLogEntryType.Value = "" Or Me.txtTitle.Value = "" Or _
        Me.cboPerformedBy.Value = "" Or Me.cboLocation.Value = "" Or _
        Me.txtDescription = "" Then
        MsgBox "You are missing one or more required fields. Please make all required selections."
        Exit Sub
    End If

    ' Open file for appending
    f = FreeFile
    Open "C:\Desktop\LogFiles\TestLog.csv" For Append As #f

    'Assign variable to multi-select listbox controls selected items
    For i = 0 To Me.lstAdditionalPeople.ListCount - 1
        If Me.lstAdditionalPeople.Selected(i) = True Then
            selectedPeople = selectedPeople & "," & Chr(34) & _
                Replace(Me.lstAdditionalPeople.List(i), Chr(34), Chr(39)) & Chr(34)
        End If
    Next i

    For i = 0 To Me.lstAssets.ListCount - 1
        If Me.lstAssets.Selected(i) = True Then
            selectedAssets = selectedAssets & "," & Chr(34) & _
                Replace(Me.lstAssets.List(i), Chr(34), Chr(39)) & Chr(34)
        End If
    Next i

    For i = 0 To Me.lstAssembly.ListCount - 1
        If Me.lstAssembly.Selected(i) = True Then
            selectedAssembly = selectedAssembly & "," & Chr(34) & _
                Replace(Me.lstAssembly.List(i), Chr(34), Chr(39)) & Chr(34)
        End If
    Next i

    For i = 0 To Me.lstProcedures.ListCount - 1
        If Me.lstProcedures.Selected(i) = True Then
            selectedProcedures = selectedProcedures & "," & Chr(34) & _
                Replace(Me.lstProcedures.List(i), Chr(34), Chr(39)) & Chr(34)
        End If
    Next i

    For i = 0 To Me.lstDefinedTests.ListCount - 1
        If Me.lstDefinedTests.Selected(i) = True Then
            selectedTests = selectedTests & "," & Chr(34) & _
                Replace(Me.lstDefinedTests.List(i), Chr(34), Chr(39)) & Chr(34)
        End If
    Next i

    ' Build comma-separated list
    strList = Chr(34) & Replace(Me.cboLogEntryType, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtTitle, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.cboPerformedBy, Chr(34), Chr(39)) & Chr(34) & "," & _
        selectedPeople & "," & Chr(34) & Replace(Me.dtpStartDate, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.dtpStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.dtpEndDate, Chr(34), Chr(39)) & "," & _
        Chr(34) & Replace(Me.dtpEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.cboTestStation, Chr(34), Chr(39)) & Chr(34) & "," & selectedAssets & "," & _
        selectedAssembly & "," & Replace(Me.cboTGINDataset, Chr(34), Chr(39)) & "," & _
        Replace(Me.cboClassification, Chr(34), Chr(39)) & "," & Replace(Me.cboLocation, Chr(34), Chr(39)) & "," & _
        Chr(34) & Replace(Me.cboLocationDetail, Chr(34), Chr(39)) & Chr(34) & "," & _
        selectedProcedures & "," & selectedTests & "," & _
        Chr(34) & Replace(Me.txtDescription, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtResults, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtStationPowerStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtStationPowerEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtStartupPowerStartTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtStartupPowerEndTime, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtScriptRepository, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtLoadRepository, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.cboBranch, Chr(34), Chr(39)) & Chr(34) & "," & _
        Chr(34) & Replace(Me.txtOrProvideBranch, Chr(34), Chr(39)) & Chr(34)

    ' Output to file
    Write #f, strList
    ' Close file
    Close #f

    ' Clear controls
    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox", "ComboBox"
                ctl.Value = ""
        End Select
    Next ctl

    For i = 0 To Me.lstAdditionalPeople.ListCount - 1
        Me.lstAdditionalPeople.Selected(i) = False
    Next i

    For i = 0 To Me.lstAssets.ListCount - 1
        Me.lstAssets.Selected(i) = False
    Next i

    For i = 0 To Me.lstAssembly.ListCount - 1
        Me.lstAssembly.Selected(i) = False
    Next i

    For i = 0 To Me.lstProcedures.ListCount - 1
        Me.lstProcedures.Selected(i) = False
    Next i

    For i = 0 To Me.lstDefinedTests.ListCount - 1
        Me.lstDefinedTests.Selected(i) = False
    Next i
End Sub

You can attach ANY kind of file in a zip file (up to 256 KB).

[Asher]

, the code above is now writing as it should as long as I use the Print# instead of Write#.

However, the listbox strings are writing each selection as separate elements and with an extra comma at front.

Ex: the additional people results: ,"Person2,Test","Person3,Test","Person4,Test"

I was asked to change the commas in between selections of a single listbox to semi-colons and make all selctions in a list box one element so I changed it to this:

Code: Select all

For i = 0 To Me.lstAdditionalPeople.ListCount - 1
        If Me.lstAdditionalPeople.Selected(i) = True Then
            selectedPeople = selectedPeople & ";" & Me.lstAdditionalPeople.List(i)
        End If
    Next i

The result is this: ";Person2,Test;Person3,Test;Person4,Test" which is almost perfect except for the ; at the very beginning. Is there any way to get rid of that one in the loop somehow?

* Note, I removed the replace for the listboxes because they can't type into them, so I added the Chr(34) to them in the strList the same way it is for the other elements*


Aside from that this is perfect! I can't tell you how grateful I am for this lounge and all your help. I am also so excited about learning these new things from you such as how to use Replace in the VBA, and Append, and even putting a For Each and Select Case together. I feel like every time I log on I'm adding to my own VBA stores and my goal is to be able to help others on the forum when I get confident enough (I've seen some posts I could probably help with but I feel like you'd probably do a better job :-)).

[HansV]

You could do this:

Code: Select all

    For i = 0 To Me.lstAdditionalPeople.ListCount - 1
        If Me.lstAdditionalPeople.Selected(i) = True Then
            selectedPeople = selectedPeople & ";" & Me.lstAdditionalPeople.List(i)
        End If
    Next i
    If selectedPeople <> "" Then
        selectedPeople = Mid(selectedPeople, 2)
    End If

If at least one item was selected, the Mid function will take the string starting at the 2nd character, i.e. skip the first semi-colon.

[Asher]

Perfect. Many, many thanks.