locked
Saving from a userform to a spreadsheet RRS feed

  • Question

  •  
    Hi Out there,

    Does anyone have any code snippets / samples for recording what is entered into a user form and pasting it in to a worksheet, along with the username and date it was recorded?


    Cheers

    Neville
    Monday, June 23, 2008 2:06 PM

Answers

  • Hi Neville,

    Its not really a excel file but a comma separated file which can be imported in excel:

        Private Sub AddData(ByVal vala As String, ByVal valb As String, ByVal valc As String)
            Dim tw As System.IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter("d:\test.csv", True)
            tw.WriteLine(String.Format("""{0}"",""{1}""", vala, valb))
            tw.Close()
            tw.Dispose()
        End Sub

    Ralf
    Tuesday, June 24, 2008 4:31 PM
  • Hi Neville -- Here's a link to another thread in which Ralf provided essentially the same answer but through the clipboard...

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/6f9c15f1-0f97-42e4-8ccb-96ccf4bea3fc

    ...however I found that I encountered problems with teh comma-separated technique so I changed to TAB-delimited and found it works like a charm.  I've used it many times since and get results that are many many times faster than placing values in one cell at a  time.


    ...and here's a link to a more recent thread that illustrates the technique a little more clearly...

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/823836a2-c72e-4a56-8af3-6e557748e053
    Tuesday, June 24, 2008 8:43 PM

All replies

  • Hi Neville,

    Its not really a excel file but a comma separated file which can be imported in excel:

        Private Sub AddData(ByVal vala As String, ByVal valb As String, ByVal valc As String)
            Dim tw As System.IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter("d:\test.csv", True)
            tw.WriteLine(String.Format("""{0}"",""{1}""", vala, valb))
            tw.Close()
            tw.Dispose()
        End Sub

    Ralf
    Tuesday, June 24, 2008 4:31 PM
  • Hi Neville -- Here's a link to another thread in which Ralf provided essentially the same answer but through the clipboard...

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/6f9c15f1-0f97-42e4-8ccb-96ccf4bea3fc

    ...however I found that I encountered problems with teh comma-separated technique so I changed to TAB-delimited and found it works like a charm.  I've used it many times since and get results that are many many times faster than placing values in one cell at a  time.


    ...and here's a link to a more recent thread that illustrates the technique a little more clearly...

    http://forums.msdn.microsoft.com/en-US/vbgeneral/thread/823836a2-c72e-4a56-8af3-6e557748e053
    Tuesday, June 24, 2008 8:43 PM