Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Advanced
Apr 27, 2000

Export Records to CSV File for Excel

Most of my end users use laptops, which can have a wide variety of spreadsheet software installed. I often use this function when working with database tables or queries to produce a spreadsheet when I don't know what program will be used to open it. The function takes any database table or SQL Select statement and turns it into a comma-delimited text file a user can open using Notepad, Excel, or any spreadsheet program, allowing you to send data easily to another user or program. In this sample, Db is a global object variable equal to the database and has already been set by the calling program. sSource is the table or SQL statement that needs to be written to a spreadsheet:
 
Public Function TableToSpreadsheet(sSource _
	As String, sFile As String) As Boolean
	On Error GoTo TableToSpreadsheet_Err
 	' SYNTAX:
	' If TableToSpreadsheet("SELECT * FROM 
	' Authors", "C:\Temp\Authors.csv") = True 
	' Then....
	Dim rsTemp As Recordset
	Dim sHeader As String
	Dim sRow As String
	Dim i As Integer
	Set rsTemp = Db.OpenRecordset(sSource)
	With rsTemp
		'  Make sure there are records to write
		If .RecordCount = 0 Then
			TableToSpreadsheet = False
			.Close
			Set rsTemp = Nothing
			Exit Function
		End If

		'  Create new target file
		Open sFile For Output As #1
		'  Write the header row
		For i = 0 To .Fields.Count - 1
			If i = 0 Then
				sHeader = .Fields(i).Name
			Else
				sHeader = sHeader & "," & .Fields(i).Name
			End If
		Next i
		Print #1, sHeader

		' Loop through the table and write data rows
		.MoveFirst
		Do Until .EOF
			For i = 0 To .Fields.Count - 1
				If i = 0 Then
					sRow = .Fields(i).Value & ""
				Else
					sRow = .Fields(i).Value & ""
				End If
			Next i
			Print #1, sRow
			.MoveNext
		Loop
		.Close
	End With
	Close #1 ' Target file is complete
	Set rsTemp = Nothing    
	' Release recordset, but NOT database objects
	TableToSpreadsheet = True
TableToSpreadsheet_Exit:
	Exit Function
TableToSpreadsheet_Err:
	LogIt "TableToSpreadsheet : " & Err.Description
	' LogIt is a function that creates an error log
	Resume Next 
	' Most errors result in a blank cell and can be ignored.
End Function
Robert Feldsien
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date