Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Exporting CSV file via VBA
- Thread starter akki007
- Start date
akki007,
Just in case you wanted to do it the hard way.
CMP
(GMT-07:00) Mountain Time (US & Canada)
Just in case you wanted to do it the hard way.
Code:
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.CloseCMP
(GMT-07:00) Mountain Time (US & Canada)
JerryKlmns
IS-IT--Management
CautionMP
your arrTest should be MyRecord?
your arrTest should be MyRecord?
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,
[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)
butcher,
Code:
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)
- Status
Similar threads
- Locked
- Question
