Re: Export to file from Access2K

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Here is a sample of some general export code:

Public Sub ExportDelim(strTable As String, strExportFile As String,
strDelimiter As String, Optional blnHeader As Boolean)

'strTable is the table or query name
'strExportFile is the full path and name of file to export to
'strDelimiter is the field deliminator to use like Chr(9) for tab or
Chr(44) for comma or ??

Dim fld As Field
Dim varData As Variant
Dim rs As Recordset
Dim intFileNum As Integer

'set recordset on table or query
Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

'get file handle and open for output
intFileNum = FreeFile()
Open strExportFile For Output As #intFileNum

If blnHeader Then
'output the header row if requested
varData = ""
For Each fld In rs.Fields 'traverse the fields collection
varData = varData & fld.Name & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out the header row
Print #intFileNum, varData
End If

'now your data
Do While Not rs.EOF
varData = ""
'concatenate the data row
For Each fld In rs.Fields
varData = varData & fld.Value & strDelimiter
Next

'remove extra last strDelimiter
varData = Left(varData, Len(varData) - 1)

'write out data row
Print #intFileNum, varData

rs.MoveNext
Loop

Close #intFileNum
rs.Close
Set rs = Nothing
End Sub

--
Joe Fallon
Access MVP



"A E" <anders@xxxxxxxxxx> wrote in message
news:uRc7QUWZFHA.1088@xxxxxxxxxxxxxxxxxxxxxxx
> Hi,
>
> I'm trying to run a For each statement, I end up in the debugger with
> Run-time error '3251' Operation is not supported for this type of object.
> What I'm trying to do is to put the Fieldnames in top of the exported
> file.
>
> This is as far as I've reached:
>
> Dim rsCurr As DAO.Recordset
> Dim fldCurr As DAO.Field
> Dim intFile As Integer
> Dim curTotal As Currency
> Dim strFile As String
> Dim strOutput As String
> Dim varData As Variant
>
> strFile = "C:\Output.txt"
> intFile = FreeFile()
> sngTotal = 0#
>
> Set dbCurr = CurrentDb()
> Set rsCurr = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot,
> dbForwardOnly)
>
> If rsCurr.EOF = False Then
> Open strFile For Output As #intFile
> varData = ""
> '"This is the part that doesn't work"
> For Each fldCurr In rsCurr
> varData = varData & fldCurr.Name & ","
> Next fldCurr
>
>
> Best regards, AE
>
>


.

Relevant Pages

  • Re: Inconsistency of Export to a CSV file.
    ... A CSV file is a txt file with a different extension. ... >>Public Sub ExportDelim(strTable As String, ... >> Dim varData As Variant ... >> Dim intFileNum As Integer ...
    (microsoft.public.access.externaldata)
  • RE: combobox lookup for column names
    ... Dim strDelimiter As String ... If you are going to use it for a query, you will want to take all the ...
    (microsoft.public.access.forms)
  • Re: Export to text with special header line
    ... > Public Sub ExportDelim(strTable As String, strExportFile As String, ... > strDelimiter As String, Optional blnHeader As Boolean) ... > Dim intFileNum As Integer ...
    (microsoft.public.access.externaldata)
  • Re: Exporting from Access to a txt file
    ... Public Sub ExportDelim(strTable As String, strExportFile As String, ... Dim varData As Variant ... Dim intFileNum As Integer ...
    (microsoft.public.access.externaldata)
  • How to parse a csv string with text qualifiers
    ... You DO need to kill the schema.ini at the end, ... >Public Function SplitCSVString(strInput As String) As ... > Dim strPathName As String ... > Dim intFileNum As Integer ...
    (microsoft.public.excel.programming)