Exporting CSV file via VBA

[akki007]

Hi guys and gals, Is it possible to use VBA to export the results of a query to a csv file automatically?

 

[kjv1611]

What about using the TransferText method or the TransferSpreadsheet method? One of those should work, I would think, depending upon your specific needs.

 

[CautionMP]

akki007,
Just in case you wanted to do it the hard way.

Dim MyDatabase as DAO.Database
Dim MyRecordset as DAO.Recordset
Dim MyRecord() as Variant
Dim intFile As Integer, intField As Integer
Dim strRecord As String

Set MyDatabase = CurrentDb
Set MyRecordset = MyDatabase.OpenRecordset("SELECT * FROM [i]SomeTable[/i];")

intFile = FreeFile
Open "C:\Test.csv" For Output As #intFile

While Not MyRecordset.EOF
  MyRecord = MyRecordset.GetRows(1)
  For intField = 0 To UBound(arrTest)
    Select Case TypeName(arrTest(intField))
      Case "Date" 
        strRecord = strRecord & "#" & Format(arrTest(intField), "mm/dd/yyyy") & "#"
      Case "String" 'wrap in double quotes, remove double quotes from text
        strRecord = strRecord & Chr(34) & Replace(arrTest(intField), Chr(34), Chr(39)) & Chr(34)
      Case Else
        strRecord = strRecord & arrTest(intField)
    End Select
    strRecord = strRecord & ","
  Next intField
  Print #intFile, strRecord
  strRecord = ""
Wend
Close #intFile
MyRecordset.Close
MyDatabase.Close

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[JerryKlmns]

CautionMP

your arrTest should be MyRecord?

 

[CautionMP]

JerryKlmns,
Yeah, sory about that.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[CautionMP]

I must have been having a bad day. [tt]GetRows[/tt] returns a two-dimensional array in DAO. Just in case here is the updated routine.
butcher,

Dim MyDatabase As DAO.Database
Dim MyRecordset As DAO.Recordset
Dim MyRecord() As Variant
Dim intFile As Integer, intField As Integer
Dim strRecord As String

Set MyDatabase = CurrentDb
Set MyRecordset = MyDatabase.OpenRecordset("SELECT * FROM [i]SomeTable[/i];")

intFile = FreeFile
Open "C:\Test.csv" For Output As #intFile

While Not MyRecordset.EOF
  MyRecord = MyRecordset.GetRows(1)
  For intField = 0 To UBound(MyRecord)
    Select Case TypeName(MyRecord(intField[b], 0[/b]))
      Case "Date"
        strRecord = strRecord & "#" & Format(MyRecord(intField[b], 0[/b]), "mm/dd/yyyy") & "#"
      Case "String" 'wrap in double quotes, remove double quotes from text
        strRecord = strRecord & Chr(34) & Replace(MyRecord(intField[b], 0[/b]), Chr(34), Chr(39)) & Chr(34)
      Case Else
        strRecord = strRecord & MyRecord(intField[b], 0[/b])
    End Select
    strRecord = strRecord & ","
  Next intField
  Print #intFile, strRecord
  strRecord = ""
Wend

Clean_Up:
Close #intFile
MyRecordset.Close
MyDatabase.Close

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)