• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1274
  • Last Modified:

use vba to export to csv

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?
thx.
Mike
0
etdreaming
Asked:
etdreaming
  • 3
  • 2
  • 2
2 Solutions
 
Corey ScheichDeveloperCommented:
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.
0
 
mvidasCommented:
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"

Matt
0
 
etdreamingAuthor Commented:
Thanks much for both....Corey2, how do you run this loop thing you're talking about?  I'm learning as I go.  thx!
Mike
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Corey ScheichDeveloperCommented:
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()
        Try
            'writing work
        Catch ex As Exception
            'assuming the exception is a write access acception
            With Me.WaitTimer
                .Interval = 500 'wait time in Milli-seconds
                .Start()
            End With
        End Try
    End Sub

    Private Sub WaitTimer_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles WaitTimer.Tick
        Me.WaitTimer.Stop()
        WriteData()
    End Sub
0
 
mvidasCommented:
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
  DoEvents
  vFF = FreeFile
  Open vFilePath For Append Access Write Lock Read Write As #vFF
  vLOF = LOF(vFF)
 Loop
 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
  DoEvents

Matt
0
 
Corey ScheichDeveloperCommented:
Woops good catch matt.  I typically work in .Net I got a little lost there it.
0
 
etdreamingAuthor Commented:
Thanks to both!
Mike
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now