Adding Records to Closed CSVs with VBA, ADO, and SQL

Post date: Jun 2, 2013 11:38:03 PM

A poster in a forum I frequent asked:

"About 5 to 10 people will be using Excel that contains a subroutine that writes to the same csv file over a network. What's the best way to handle multiple users writing to one file? The Open, Write and Close methods happen quickly, but there is a good chance two or more people will attempt to open the csv at the same time. Thoughts?"

With VBA, ADO, and SQL, this is easy. Before we talk about the solution, however, I want to remind readers that there are free databases out there and this sort of thing is best done in a database. So with that said, let's answer our poster.

The method for writing to CSV's or MS Access Tables or SQL Server Tables is the same.

• 1. Create a Connection Object

  2. Create an SQL "Insert Statement"

  3. Execute the SQL Insert Statement using the Connection Object.

  4. When finished with all updates, close the Connection Object

So, for an example, let’s assume:

• • We have a CSV file called Temp.CSV in C:\TEMP

  • It has three columns

  • Columns have headings

  • We want to insert “V1” in column 1, “V2” in column 2 and "V3" in column 3

  • C:\Temp\Temp.csv is closed (no one has it open in Excel or Notepad)

Add the following code to a normal code module. Afterwards, call it from the immediate window.

Sub Write2CSV()

Dim cn As Object

Dim sSQL As String

Set cn = CreateObject("ADODB.Connection")

sSQL = "Insert Into Temp.CSV Values('V1', 'V2', 'V3')"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:Temp;" & _

"Extended Properties='Text;" & _

"HDR=Yes;" & _

"FMT = CSVDelimited '"

cn.Execute sSQL

cn.Close

End Sub

This shows the basic steps to updating a CSV. To complete this project we:

• • Should add error handling

  • Would add code to pluck data from Excel's cells and replace the SQL Insert Statement's 'V#' with those values

  • Should add code to keep the connection open until all updates are finished and then close it.

References:

Connection Objects: http://msdn.microsoft.com/en-us/library/ms807027.aspx

Connection Strings: http://www.connectionstrings.com/textfile

SQL Insert Statment: http://www.w3schools.com/sql/sql_insert.asp