Member Avatar for GB4mytime

Can anyone tell me how to write files or output to an excel file with tabs etc.

Thanks,

GB4

  • 3 Contributors
  • 3 Replies
  • 182 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by mnemtsas

Dani AI

Generated

— three practical ways to get data into Excel from VB, with quick pros/cons and a tiny example for the “fast and dirty” approach that already touched on. ’s comma-delimited idea is the simplest: create a CSV/TSV that Excel opens. ’s COM automation is the right choice when you must set column widths, number formats, formulas or bold headers. If you cannot or do not want to start Excel, a third option is to write into a workbook via an OLE DB provider or generate an Excel-compatible XML/OpenXML file (these let you create a real workbook without manual formatting after open).

When you only need tab/column separation (no formatting), write a tab-delimited file and quote fields that contain tabs/quotes/newlines. This is small, robust, and opens directly in Excel. Use .tsv or .txt so users (and some import dialogs) pick the right delimiter. To preserve leading zeros or force text, emit values as ="00123" or prefix with an apostrophe ('00123).

Example (VBA/VB6-style) — write a couple of rows to a TSV and quote fields as needed:

Function QuoteField(s As String) As String
  s = Replace(CStr(s), """", """""")
  If InStr(s, vbTab) > 0 Or InStr(s, vbCr) > 0 Or InStr(s, vbLf) > 0 Then
    QuoteField = """" & s & """"
  Else
    QuoteField = s
  End If
End Function

Sub SimpleTSV()
  Dim f As Integer: f = FreeFile
  Open "C:\temp\report.tsv" For Output As #f
  Print #f, QuoteField("Name") & vbTab & QuoteField("Code")
  Print #f, QuoteField("Acme, Inc.") & vbTab & QuoteField("=""00123""")
  Close #f
End Sub

Troubleshooting and tips: Excel won’t remember column widths or cell formats from CSV/TSV — use COM automation if you need that. Be careful with locale decimal separators (comma vs dot) and with large datasets—stream output line-by-line rather than building one giant string. If automating Excel, always Quit the app and release COM references to avoid orphaned Excel.exe processes.

Recommended Answers

All 3 Replies

Member Avatar for Buff

Can anyone tell me how to write files or output to an excel file with tabs etc.

Thanks,

GB4

i've been able to write a comma-delimited file for use as an excel spreadsheet
quite easily. Even put a Total at the end of one column (only number column).

It does, however require the user to format the columns as to widths, etc. so
I would also be interested in any info about formatting the columns.

Member Avatar for GB4mytime

Thnnks for your reply. If you had a little more information or details on how to do this it would be much appreciated

Thanks

i've been able to write a comma-delimited file for use as an excel spreadsheet
quite easily. Even put a Total at the end of one column (only number column).

It does, however require the user to format the columns as to widths, etc. so
I would also be interested in any info about formatting the columns.

Member Avatar for mnemtsas

i've been able to write a comma-delimited file for use as an excel spreadsheet
quite easily. Even put a Total at the end of one column (only number column).

It does, however require the user to format the columns as to widths, etc. so
I would also be interested in any info about formatting the columns.

Hi There,

Here's a code snippet that writes the contents of an ADO recordset to a Excel file, you'll need to reference an Excel library in your project for it to work. Basically you need to learn about the Excel object model, try looking in your MSDN documentation.

'*******************************************************************************
' excelPrintRecordSet(Sub)
'
' PARAMETERS:
'
'
' RETURN VALUE:
'
'
' DESCRIPTION:
' Test function that will print out all the records from a recordset in Excel.
'*******************************************************************************
Public Sub excelPrintRecordSet(rstTmp As ADODB.Recordset)
  Dim appExcel As Excel.Application
  Dim wbkReport As Excel.Workbook
  Dim wksReport As Excel.Worksheet
  Dim intField As Integer, intRow As Integer
  
  Const PROCEDURE_NAME As String = "excelPrintRecordSet"

  On Error GoTo errorHandler

  Set appExcel = New Excel.Application
  appExcel.Visible = True
  Set wbkReport = appExcel.Workbooks.Add
  'wbkReportame = "Kilometer Report"
  Set wksReport = wbkReport.Worksheets(1)

  If rstTmp.EOF <> True Then
    rstTmp.MoveFirst
    intRow = 1
    Do
      For intField = 0 To rstTmp.Fields.Count - 1
        wksReport.Cells(intRow, intField + 1) = rstTmp.Fields(intField).Name & "=" & rstTmp.Fields(intField).Value
      Next intField
      rstTmp.MoveNext
      intRow = intRow + 1
    Loop Until rstTmp.EOF = True
  End If
  Exit Sub

errorHandler:
  frmErrorHandler.errorForm MODULE_NAME, PROCEDURE_NAME
  Err.Clear
End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.