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
