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

[Asher]

Hello,

I need to create a form in Excel that appears on opening the file, allows people to enter data, then hit a button to submit the data, have the data be swept to a csv file (appended to any other data already swept from previous entries), then clear the form for immediate re-use. The csv file will be kept in a network folder.

I know how to create a form from scratch in VBA developer, I can create a submit button, but I don't know how to write the code that would send the entered data to another file in csv format, appending it to any existing data, and clear the form.

I plan on making it just a single csv file that is held in one place on the network.

If anyone could give me an idea of where to look to find out how to do this I am willing to research and learn. I just really need to get this done.

Thanks for any suggestions/information/assistance,

Asher

[HansV]

Will the data entered by the user be saved to one line in the .csv file?

[Asher]

Will the data entered by the user be saved to one line in the .csv file?

That's what I was thinking would best simplest, but it can be different if need be.

[HansV]

Here is the basic idea; you will have to modify it for your specific situation, of course.

Code: Select all

Private Sub cmdSubmit_Click()
  Dim f As Integer
  Dim strList As String
  Dim ctl As Control

  ' Open file for appending
  f = FreeFile
  Open "C:\MyFiles\Log.csv" For Append As #f
  ' Build comma-separated list
  strList = Me.txtThis & "," & _
    Me.txtThat & "," & Me.txOther
  ' 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
End Sub

[Asher]

Thanks hans. I'm reading the code and the one part I don't quite understand is this:

Code: Select all

strList = Me.txtThis & "," & _
    Me.txtThat & "," & Me.txOther

What does the Me stand for ? And .txtThis and that? are those the input fields on the form or the labels for the associated input fields or something else?

[HansV]

I assumed that you had created a userform. The code that I posted is intended as the On Click event procedure for a command button on that userform. In code behind a userform, the keyword Me refers to the form itself.
I also assumed that you have a series of text boxes and perhaps combo boxes in which the users enter data. The names txtThis etc. were just examples - you should replace them with the names of your controls, and you can expand the lines to include more controls.

If I misinterpreted your setup, please provide more info.

[Asher]

You were right, I just didn't know how to call on the form controls in code. I think I got it now, thanks :-)

[Asher]

Is there a way to, instead of submitting the data to a static .csv form on a network, the submit code can create a new .csv form for every submission and save it/them to whatever computers hard drive it is run on?

[HansV]

The line

Open "C:\MyFiles\Log.csv" For Append As #f

opens the text file in append mode, i.e. new data will be written below existing data. If you change the line to

Open "C:\MyFiles\Log.csv" For Output As #f

the code will create a new file, deleting an existing file if necessary.

If you want to create a different file each time, you'll have to specify how you want to name the files - use a sequence number, or include the date and time in the filename, or ...

[Asher]

Hi all,

I've got the form all set up and did a test run of the code and I ran in to some issues:

First, the code doesn't mention how to call the ListBox items, I added those control calls just like the others ex: Me.lstControl1 and it doesn't show up even 1 selected item from the list box.

Also the list boxes don't clear the same way the text and combo boxes do when the clear form part of the macro runs.

And lastly, I'm not sure if you want me to create a new thread for this one, but I am using 4 date and time pickers. Basically because I have 2 areas that need a date and a time value but the date time picker (although you can format both the date and time to show) will only let you select the date in the calendar, but when I added one for date and a second one for the time format, the valuse show the date I selected in the one for date, but the one for time shows a value of todays date along with the time I selected... how do I get the second one to show JUST the time I selected?

I've been watching youtube tutorials all morning and can't find one that fits my situation. I also have been reading the list box portion of Walkenbach's Excel 2007 Power Programming with VBA and it doesn't seem to touch on the issues, or I didn't understand it right (which is all too possible)...

Any assistance would be much appreciated.

[HansV]

First, the code doesn't mention how to call the ListBox items.

This is the first mention of a list box in this thread. You don't really expect us to answer questions you haven't asked yet, do you?

It's becoming too complicated for me to follow. It would help if you could attach (a stripped down copy of) your workbook, and perhaps a sample of what you want the output to look like.

[Asher]

I apologize, I wasn't trying to suggest that you would have known I was using a list box, especially since I wasn't aware that I needed one at the time, only that I did add some and, since the same code you used to textboxes worked for combo-boxes, I tried it with list boxes because that was my only frame of reference, and it wasn't the same. I can see how I worded that badly.

I have attached a stripped down form (although for some reason, now that it's stripped down, I get an error on one of the combo boxes that i never got before).

Here is how one entry should look:
System,Test Title 1,Person 1,Person 5,Person 7,11/16/2010,3:15:00 PM,11/17/2010,4:00:00 PM,Station 3,Device 4,Device 6,SubDevice 3,Dataset 5,Class 3,Building 2,Lab 7,Procedure 1,Procedure 2, Test Description 1,Test Results 1,5.23,5.50,5.15,5.55,script test 1,load repository test 1,Branch 3,or provide test 1

[HansV]

I don't know what happened, but I can't do much with the userform - it appears to be completely corrupted, whenever I delete a control that isn't recognized, the next one isn't recognized.

But I can tell you how to handle a multi-select list box:

Code: Select all

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

I can't test further, sorry.

[Asher]

Ok, thanks for trying to test, and thanks bunches for the multi-select list box handling instruction. :-)

[Asher]

Hans,

I wanted to let you and anyone else that may have read these posts know that thanks to the code you (Hans) gave me, and some research on Ozgrid, Techbook reprot, and VBAExpress, I was able to vary it a bit and make the listboxes write to the file, as well as clear the form.. I was also able to add a required field handing statement.

The only issue I have left is to try and get the dtPicker working correctly.

The code is below as I have altered it to work for the situation:

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 & "," & Me.lstAdditionalPeople.List(i)
        End If
    Next i
    
    For i = 0 To Me.lstAssets.ListCount - 1
        If Me.lstAssets.Selected(i) = True Then
            selectedAssets = selectedAssets & "," & Me.lstAssets.List(i)
        End If
    Next i
      
    For i = 0 To Me.lstAssembly.ListCount - 1
        If Me.lstAssembly.Selected(i) = True Then
            selectedAssembly = selectedAssembly & "," & Me.lstAssembly.List(i)
        End If
    Next i
    
    For i = 0 To Me.lstProcedures.ListCount - 1
        If Me.lstProcedures.Selected(i) = True Then
            selectedProcedures = selectedProcedures & "," & Me.lstProcedures.List(i)
        End If
    Next i
    
    For i = 0 To Me.lstDefinedTests.ListCount - 1
        If Me.lstDefinedTests.Selected(i) = True Then
            selectedTests = selectedTests & "," & Me.lstDefinedTests.List(i)
        End If
    Next i
  
  ' Build comma-separated list
  strList = Me.cboLogEntryType & "," & Me.txtTitle & "," & Me.cboPerformedBy & "," & _
    selectedPeople & "," & Me.dtpStartDate & "," & Me.dtpStartTime & "," & _
    Me.dtpEndDate & "," & Me.dtpEndTime & "," & Me.cboTestStation & "," & selectedAssets & "," & _
    selectedAssembly & "," & Me.cboTGINDataset & "," & Me.cboClassification & "," & Me.cboLocation & "," & _
    Me.cboLocationDetail & "," & selectedProcedures & "," & selectedTests & "," & _
    Me.txtDescription & "," & Me.txtResults & "," & Me.txtStationPowerStartTime & "," & _
    Me.txtStationPowerEndTime & "," & Me.txtStartupPowerStartTime & "," & _
    Me.txtStartupPowerEndTime & "," & Me.txtScriptRepository & "," & Me.txtLoadRepository & "," & _
    Me.cboBranch & "," & Me.txtOrProvideBranch
    
  ' 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

[Asher]

Hello again,

As I said in my most recent post, this has been working great like this, but writing the whole line to one single cell is bringing up issues elsewhere. I know this is a change but is there a way to put each element in subsequent cells of the same row and have each new submission still be a new row?

I think this would be handled with thinkgs such as I've seen like Cells(1,1).xlUp and such, but I've not got the full grasp of those commands as of yet (if anyone knows a good tutorial I'd be happy to check it out too).

[HansV]

The code writes a line to a text file, not to a cell in a worksheet. Do you now want to fill cells in a worksheet? If so, how exactly?

[Asher]

Let me get back to you on that. I must apologize, I have been working with someone that has been none too clear on what he really wants and keeps changing things. Let me make sure I know exactly what he wants this time.

I appreciate your time very much (as well as everything I learn from you) and I don't want to waste any of it.

[Asher]

Ok,

I think I finally have some answers, although they are somewhat above me. I will try to explain as best I can.

Apparently he is using this .csv file to do something with a PHP that will then upload the information to a space on the internet.

The troubles are that, when he opens the Microsoft Office Excel Comma Separated Values File that I have saved all this to, it opens as one element . He is openeing it with the Notepad to show me what he was seeing.

Basically, what he is seeing when he opens the file in notepad is this (notice the entire thing in quotes):

"System Test,TitleTest,Person1, Test,Person2, Test;Person3, Test,1/1/2001 12:00:00 AM,1/1/2001 12:00:00 AM,Stationtest,ASSEMBLY1; ASSEMBLY2,SubASSEMBLY1;SubAssembly2,8456,Classification3,Location3,Detail2,Procedure2,TestDefinition5,desc tst,res tst,1.3,1.5,1.0,1.6,1,8,N/A,br5"

and the same .csv file opens as this in excel (entire string in the first cell w/o quotes):

System Test,TitleTest,Person1, Test,Person2, Test;Person3, Test,1/1/2001 12:00:00 AM,1/1/2001 12:00:00 AM,Stationtest,ASSEMBLY1; ASSEMBLY2,SubASSEMBLY1;SubAssembly2,8456,Classification3,Location3,Detail2,Procedure2,TestDefinition5,desc tst,res tst,1.3,1.5,1.0,1.6,1,8,N/A,br5

What he needs to see when he opens it in notepad is this:

"System Test","TitleTest","Person1, Test","Person2, Test;Person3, Test","1/1/2001 12:00:00 AM","1/1/2001 12:00:00 AM","Stationtest","ASSEMBLY1; ASSEMBLY2","SubASSEMBLY1;SubAssembly2","8456","Classification3","Location3","Detail2","Procedure2","TestDefinition5","desc tst","res tst","1.3","1.5","1.0","1.6","1","8","N/A","br5"

which, when opened in excel is this (each control in its own cell):

System Test TitleTest Person1, Test Person2, Test;Person3, Test 1/1/2001 0:00 1/1/2001 0:00 Stationtest ASSEMBLY1; ASSEMBLY2 SubASSEMBLY1;SubAssembly2 8456 Classification3 Location3 Detail2 Procedure2 TestDefinition5 desc tst res tst 1.3 1.5 1 1.6 1 8 N/A br5

*Note that these are all in a single row in the files. I have tried to attach the results files but it tells me that .csv extensions are not allowed, can I email them? It really did make it easier to see what was wanted when I opened them both in excel then in notepad.

Another thing, he said I need a STRING REPLACE so that if people type quotation marks in their text boxes, it doesn't "break" the .csv ....?

[Asher]

Since I could not attach a .csv file I attached an image of them all.