Export to CSV file with Header and Footer

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it posible to do this with code, i have tryed exporting it from Access to
exel and then exporting it from there, but ise there a straight way.
The output file shudt look like this:

H2;12888;My Magasine'(This ise the Header, alvays the same)
A2;123456;;;;;;1;Ole Olsen;;Street 103;;8510 Mytown '(From my tblCustomers)
A2;125412;;;;;;2;Dunald Duck;;Mysterey streat 13;8564 Anywhwer
S2;[Count of records]'(Footer: The S2 must be there , the count ise volentery)

Hope you understand what i need
 
Leika,

Here some aircode:

Sub Export_Customers()
Dim db As DAO.Database
Dim rst As DAO.Recordset
vFile = "C:\My Documents\MyCSVFile.txt
Set db = CurrentDb
Set rst = db.OpenRecordset("tblCustomers")
If rst.Count = 0 Then
msg = "No records in table."
typ = vbExclamation
ttl = "Export Customers"
Msgbox msg, typ, ttl
Goto No_Recs
End If
Open vFile For Output As #1
Print #1, "H2;12888;My Magasine"
Do Until rst.EOF
vLine = ""
For i = 0 To rst.Fields.Count - 1
vLine = vLine & rst.Fileds(i) & ";"
Next
vLine = Left(vLine, Len(vLine) - 1)
Print #1, vLine
rst.MoveNext
Loop
Print #1, "S2;" & rst.Recordcount
Close #1
No_Recs:
rst.close
Set rst = Nothing
Set db = Nothing
End Sub

Paste this in any (existing or new) standard module, set vFile to the
desired file path and name and run it.

Note: To run this code, an appropriate DAO Object Library reference is
required. While in the VB editor window, go to menu item Tools >
References; check if a Microsoft DAO reference is present among the ones
checked at the top of the list. If not, scroll down to find the
appropriate Microsoft DAO X.X Object Library reference and check it. The
appropriate reference is DAO 3.51 for A97, DAO 3.6 for A2K or later.

HTH,
Nikos
 
This line returnes the folowing error, on vFile :
There is a check in: Microsoft DAO 3.6 Object Library
vFile = "C:\My Documents\MyCSVFile.txt
Click to expand...

Compile error:Variable not defined

I just call it with a button
What am i doing wrong
 
Leika,

Not your fault, mine! I forgot the closing quotes. The line should be:

vFile = "C:\My Documents\MyCSVFile.txt"

Hope it works now.

Nikos
 
Well, Nikos DID warn you it was air-code!

There are a number of variables that haven't been declared. You've obviously
got Option Explicit turned on (as you should!), so you'll have to declare
them all.

Looking at the code, I think this is all of them:

Dim vFile As String
Dim vLine As String
Dim msg As String
Dim typ As Long
Dim ttl As String
Dim i As Integer

BTW, I'd strong recommend NOT using #1 for the file number, but instead
declare

Dim intFile As Integer

intFile = FreeFile

Open vFile For Output As #intFile
Print #intFile, "H2;12888;My Magasine"
Do Until rst.EOF
vLine = ""
For i = 0 To rst.Fields.Count - 1
vLine = vLine & rst.Fileds(i) & ";"
Next
vLine = Left(vLine, Len(vLine) - 1)
Print #intFile, vLine
rst.MoveNext
Loop
Print #intFile, "S2;" & rst.Recordcount
Close #1
 
I'm totally new at this so I hope you beer over with me
Dim vFile As String
Dim vLine As String
Dim msg As String
Dim typ As Long
Dim ttl As String
Dim i As Integer
Click to expand...

I have put this declaretions in and it helped, but now I get:

Methode or data member not found
For the .Count in this line:
If rst.Count = 0 Then

I have tryed but i dont know how to fix it.
 
Thanks for all you'r help
I made a cuple of changes myself, now it woorks.
Now it lookes like this:

Sub Export_Customers()

Dim vFile As String
Dim vLine As String
Dim msg As String
Dim typ As Long
Dim ttl As String
Dim i As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset
vFile = "C:\Anja\MyCSVFile.txt"

Set db = CurrentDb
Set rst = db.OpenRecordset("tblEksportDanske")
If rst.Fields.Count = 0 Then
msg = "No records in table."
typ = vbExclamation
ttl = "Export Customers"
MsgBox msg, typ, ttl
GoTo No_Recs
End If
Open vFile For Output As #1
Print #1, "H2;12888;Kniplebrevet"
Do Until rst.EOF
vLine = ""
For i = 0 To rst.Fields.Count - 1
vLine = vLine & rst.Fields(i) & ";"
Next
vLine = Left(vLine, Len(vLine) - 1)
Print #1, vLine
rst.MoveNext
Loop
Print #1, "S2;" & rst.RecordCount
Close #1

No_Recs:
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
 
Back
Top