devxlogo

Export Records to CSV File for Excel

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:TempAuthors.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 = TrueTableToSpreadsheet_Exit:	Exit FunctionTableToSpreadsheet_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

devx-admin

Share the Post: