use vba to export to csv

Posted on 2007-08-02
Last Modified: 2012-08-13
I am using macros to export testing data from a powerpoint to a Microsoft Excel Comma Separated Values File and it works fine.  My question is that if I have 20 different users running the same powerpoint test and 2 or more just happen to hit the submit button at the same time will both their data show up in the csv file or can it only handle one response at a time and thus one person's data would not make it through?
Question by:etdreaming
    LVL 13

    Expert Comment

    The posibility of one user's date being missed though slim is possible.

    To avoid this you will have to trap the error and run a loop until the file is available for write, then write to the file afterward.  It would be simpler to use a database but that should work pretty well.

    Another thing you could do is have the clients write to their own separate files and have another application that will combine them into a single csv.
    LVL 35

    Expert Comment

    Hi Mike,

    Depending on how your macro is setup (basically unless you have the macro appending data to the csv file), only one users' information will be in it. Why not have the exported csv file have a dynamic name, for example you could enter their username and/or the date/time of the export to ensure a unique name so you would not have to worry about this.

     Dim vFileName As String, vFilePath As String
     vFilePath = "C:\Test Results\"
     vFileName = vFilePath & Environ("username") & Format(Date, "-yyyy-mm-dd") & ".csv"


    Author Comment

    Thanks much for both....Corey2, how do you run this loop thing you're talking about?  I'm learning as I go.  thx!
    LVL 13

    Assisted Solution

    I would add a timer to the form and watch for timer ticks

    'something like this should work
        Dim WithEvents WaitTimer As Timer

        Sub WriteData()
                'writing work
            Catch ex As Exception
                'assuming the exception is a write access acception
                With Me.WaitTimer
                    .Interval = 500 'wait time in Milli-seconds
                End With
            End Try
        End Sub

        Private Sub WaitTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles WaitTimer.Tick
        End Sub
    LVL 35

    Accepted Solution

    Using VBA would be a bit different (though someone did make a Timer object you can easily use in VBA) as there is no try/catch methods to be used.

    Depending on how you're writing to the CSV, you could use a function like this to return the file number of the opened file (once its available--I'm guessing you're opening it for Append but you can change the statement in your calling sub):

    Function WaitUntilFileReady(ByVal vFilePath As String) As Long
     'returns file number for already opened file for append
     Dim vFF As Long, vLOF As Long
     vLOF = -1
     On Error Resume Next
     Do Until vLOF > -1
      vFF = FreeFile
      Open vFilePath For Append Access Write Lock Read Write As #vFF
      vLOF = LOF(vFF)
     On Error GoTo 0
     WaitUntilFileReady = vFF
    End Function

    Using it with something like:

     Dim vFileNum As Long
     vFileNum = WaitUntilFileReady("C:\your file.csv")
     'file is now yours to write to
     Print #vFileNum, YourOutput
     Close #vFileNum

    To help with processor time, you could also add a Sleep call to the function so it sleeps for 100ms or so each iteration:

    Add this to top of module:
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

    Change part of the function:
     Do Until vLOF > -1
      Sleep 100 'Add this line

    LVL 13

    Expert Comment

    Woops good catch matt.  I typically work in .Net I got a little lost there it.

    Author Comment

    Thanks to both!

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

     Regular Expressions Microsoft Word has sophisticated search tools that can search for patterns. For example if you wanted to search for all UK phone numbers that followed a pattern of five digits, a space and then six digits you can easily do th…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now