Export text file

[Guest]

I have successfully exported data from Access 2000 into a text file using a custom export specification. I'm getting the data I want. However, the resultant text file records are still not in the form that my accounting software needs. Is there any way in VBA to open a text file and modify it record-by-record, or do I have to use Visual Basic outside of Access/VBA to do this? Any direction would be appreciated.

 

[John Marshall, MVP]

How do you want to modify it?

John... Visio MVP

I have successfully exported data from Access 2000 into a text file using

a custom export specification. I'm getting the data I want. However, the
resultant text file records are still not in the form that my accounting
software needs. Is there any way in VBA to open a text file and modify it
record-by-record, or do I have to use Visual Basic outside of Access/VBA to
do this? Any direction would be appreciated.

 

[Immanuel Sibero]

Hi ctdak

Check out this link:

http://www.mvps.org/access/modules/mdl0058.htm
This will give you total control over the format of the resulting text file.

From your question, you seem to be thinking about creating the text file
first and then editing/correcting the file record by record. Using the above
TextExport class, you should be able to format the resulting text file
correctly the first time (so you dont need to go back in the file and make
corrections).

HTH,
Immanuel Sibero

I have successfully exported data from Access 2000 into a text file using

a custom export specification. I'm getting the data I want. However, the
resultant text file records are still not in the form that my accounting
software needs. Is there any way in VBA to open a text file and modify it
record-by-record, or do I have to use Visual Basic outside of Access/VBA to
do this? Any direction would be appreciated.

 

[Guest]

Immanuel,

Thanks very much for your answer. You are correct that I have been thinking about creating the text file and then modifying it afterwards. I didn't know there was any other option. Your suggestion sounds great and would be preferable. I have downloaded the CLS file, but I have no idea how to implement it. Pardon my ignorance, but what is a class and how do I use it? (I'm quite new to software development in the Access environment.)

Also, I have to do three main things to the text file that I can't do with TransferText: 1) I have to add two unique header records and one unique trailing record, 2) I have to combine fields that have been output from different tables, 3) I have to conditionally omit fields altogether in certain records depending on the contents of other fields, and 4) I have to change the output format of some field types (such as removing the time in a Date/Time field and the $ sign in a Currency field). These types of things can easily be done with Basic code, but can the Class you directed me to give this kind of flexibility in the export process?

Clive

----- Immanuel Sibero wrote: -----

Hi ctdak

Check out this link:

http://www.mvps.org/access/modules/mdl0058.htm
This will give you total control over the format of the resulting text file.

From your question, you seem to be thinking about creating the text file
first and then editing/correcting the file record by record. Using the above
TextExport class, you should be able to format the resulting text file
correctly the first time (so you dont need to go back in the file and make
corrections).

HTH,
Immanuel Sibero

I have successfully exported data from Access 2000 into a text file using

a custom export specification. I'm getting the data I want. However, the
resultant text file records are still not in the form that my accounting
software needs. Is there any way in VBA to open a text file and modify it
record-by-record, or do I have to use Visual Basic outside of Access/VBA to
do this? Any direction would be appreciated.

 

[Guest]

John,

I have to do 4 main things to the text file that I can't do with TransferText: 1) I have to add two unique header records and one unique trailing record, 2) I have to combine fields that have been output from different tables, 3) I have to conditionally omit fields altogether in certain records depending on the contents of other fields, and 4) I have to change the output format of some field types (such as removing the time in a Date/Time field and the $ sign in a Currency field). These types of things can easily be done with Basic code, but can they be done from VBA?



----- John Marshall, MVP wrote: -----

How do you want to modify it?

John... Visio MVP

I have successfully exported data from Access 2000 into a text file using

a custom export specification. I'm getting the data I want. However, the
resultant text file records are still not in the form that my accounting
software needs. Is there any way in VBA to open a text file and modify it
record-by-record, or do I have to use Visual Basic outside of Access/VBA to
do this? Any direction would be appreciated.

 

[Joe Fallon]

If you write VBA code you can do pretty much anything you like with the data
as you write it to the file.
You should build 2-3 procedures simialr to this (1 for each major section of
your file) and then call them when needed. If you take your time and review
this code you will see it is not really that difficult to follow. Then take
the ideas and expand on them. (e.g. need data from 2 tables - open 2
recordsets!)

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

John,

I have to do 4 main things to the text file that I can't do with

TransferText: 1) I have to add two unique header records and one unique
trailing record, 2) I have to combine fields that have been output from
different tables, 3) I have to conditionally omit fields altogether in
certain records depending on the contents of other fields, and 4) I have to
change the output format of some field types (such as removing the time in a
Date/Time field and the $ sign in a Currency field). These types of things
can easily be done with Basic code, but can they be done from VBA?

 

[Guest]

Joe
Your code has been a big help. However, program execution stops at

Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot)

I get Run-time error 91, “Object variable or With block variable not setâ€

My Set statement is identical to yours, except for a query name string litera
instead of strTable

I have Dim statements
Dim rs As Recordset
Dim CurrentDb as Database (I added this one, but it didn’t help

Any idea why I am getting this error

Thanks again
ctda

----- Joe Fallon wrote: ----

If you write VBA code you can do pretty much anything you like with the dat
as you write it to the file
You should build 2-3 procedures simialr to this (1 for each major section o
your file) and then call them when needed. If you take your time and revie
this code you will see it is not really that difficult to follow. Then tak
the ideas and expand on them. (e.g. need data from 2 tables - open
recordsets!

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 nam
'strExportFile is the full path and name of file to export t
'strDelimiter is the field deliminator to use like Chr(9) for tab o
Chr(44) for comma or ?

Dim fld As Fiel
Dim varData As Varian
Dim rs As Recordse
Dim intFileNum As Intege

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

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

If blnHeader The
'output the header row if requeste
varData = "
For Each fld In rs.Fields 'traverse the fields collectio
varData = varData & fld.Name & strDelimite
Nex

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

'write out the header ro
Print #intFileNum, varDat
End I

'now your dat
Do While Not rs.EO
varData = "
'concatenate the data ro
For Each fld In rs.Field
varData = varData & fld.Value & strDelimite
Nex

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

'write out data ro
Print #intFileNum, varDat

rs.MoveNex
Loo

Close #intFileNu
rs.Clos
Set rs = Nothin
End Su

--
Joe Fallo
Access MV

John

TransferText: 1) I have to add two unique header records and one uniqu
trailing record, 2) I have to combine fields that have been output fro
different tables, 3) I have to conditionally omit fields altogether i
certain records depending on the contents of other fields, and 4) I have t
change the output format of some field types (such as removing the time in
Date/Time field and the $ sign in a Currency field). These types of thing
can easily be done with Basic code, but can they be done from VBA

 

[Immanuel Sibero]

Hi ctdak

Dim CurrentDb as Database (I added this one, but it didn't help)

Do not use CurrentDb as a variable name since it's a reserved word.
try instead:

Dim db as DAO.Database
Dim rs as DAO.Recordset


HTH,
Immanuel Sibero

 

[Joe Fallon]

Be sure you have references to DAO 3.6.
May as well reomve reference to ADO if you do not need it.
(They both have Recordset objects and the code gets confused if you
reference the wrong one.)
This is the #1 issue in A2000 and newer.
I think in A2003 MS *finally* decided to make DAO the default reference
again and let users choose ADO if they want it.

 

[Guest]

Joe
Your answers have been a BIG HELP! Thanks for taking the time. Yes, I added the reference to DAO 3.6. I don't plan on migrating my application to anything else. It will stay in Access. What you are saying is that DAO is all I need in that case. Is that correct

Also, because I currently have both ADO & DAO referenced, I noticed two recordset objects in the drop down list. You say the code can get confused. Dumb question: Does that mean you have to somehow know which of the two recordsets belongs to which object library? In other words, can you pick the wrong one from the list and mess things up

ctda

----- Joe Fallon wrote: ----

Be sure you have references to DAO 3.6
May as well reomve reference to ADO if you do not need it
(They both have Recordset objects and the code gets confused if yo
reference the wrong one.
This is the #1 issue in A2000 and newer
I think in A2003 MS *finally* decided to make DAO the default referenc
again and let users choose ADO if they want it
--
Joe Fallo
Access MV

Joe
Your code has been a big help. However, program execution stops at

Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot
I get Run-time error 91, "Object variable or With block variable not set
My Set statement is identical to yours, except for a query name strin

litera
instead of strTable

I have Dim statements

Dim rs As Recordse
Dim CurrentDb as Database (I added this one, but it didn't help

Any idea why I am getting this error
Thanks again ctda
----- Joe Fallon wrote: ----
If you write VBA code you can do pretty much anything you like wit

the dat
as you write it to the file
You should build 2-3 procedures simialr to this (1 for each majo section o
your file) and then call them when needed. If you take your time an revie
this code you will see it is not really that difficult to follow Then tak
the ideas and expand on them. (e.g. need data from 2 tables - open
recordsets!

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 nam

'strExportFile is the full path and name of file to export t
'strDelimiter is the field deliminator to use like Chr(9) for ta o
Chr(44) for comma or ?

Dim fld As Fiel

Dim varData As Varian
Dim rs As Recordse
Dim intFileNum As Intege

'set recordset on table or quer

Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot

'get file handle and open for outpu

intFileNum = FreeFile(
Open strExportFile For Output As #intFileNu

If blnHeader The

'output the header row if requeste
varData = "
For Each fld In rs.Fields 'traverse the fields collectio
varData = varData & fld.Name & strDelimite
Nex

'remove extra last strDelimite

varData = Left(varData, Len(varData) - 1

'write out the header ro

Print #intFileNum, varDat
End I

'now your dat

Do While Not rs.EO
varData = "
'concatenate the data ro
For Each fld In rs.Field
varData = varData & fld.Value & strDelimite
Nex

'remove extra last strDelimite

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

 

[Guest]

Joe

I got done what I needed to because you took the time to give me this sample code. I had not yet gotten into using DAO and recordsets, so this was what I needed to get going with them

Thanks very much for your help

ctda

----- Joe Fallon wrote: ----

If you write VBA code you can do pretty much anything you like with the dat
as you write it to the file
You should build 2-3 procedures simialr to this (1 for each major section o
your file) and then call them when needed. If you take your time and revie
this code you will see it is not really that difficult to follow. Then tak
the ideas and expand on them. (e.g. need data from 2 tables - open
recordsets!

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 nam
'strExportFile is the full path and name of file to export t
'strDelimiter is the field deliminator to use like Chr(9) for tab o
Chr(44) for comma or ?

Dim fld As Fiel
Dim varData As Varian
Dim rs As Recordse
Dim intFileNum As Intege

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

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

If blnHeader The
'output the header row if requeste
varData = "
For Each fld In rs.Fields 'traverse the fields collectio
varData = varData & fld.Name & strDelimite
Nex

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

'write out the header ro
Print #intFileNum, varDat
End I

'now your dat
Do While Not rs.EO
varData = "
'concatenate the data ro
For Each fld In rs.Field
varData = varData & fld.Value & strDelimite
Nex

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

'write out data ro
Print #intFileNum, varDat

rs.MoveNex
Loo

Close #intFileNu
rs.Clos
Set rs = Nothin
End Su

--
Joe Fallo
Access MV

John

TransferText: 1) I have to add two unique header records and one uniqu
trailing record, 2) I have to combine fields that have been output fro
different tables, 3) I have to conditionally omit fields altogether i
certain records depending on the contents of other fields, and 4) I have t
change the output format of some field types (such as removing the time in
Date/Time field and the $ sign in a Currency field). These types of thing
can easily be done with Basic code, but can they be done from VBA

 

[Guest]

Immanuel
Thanks for your help
ctda

----- Immanuel Sibero wrote: ----


Hi ctda

Dim CurrentDb as Database (I added this one, but it didn't help

Do not use CurrentDb as a variable name since it's a reserved word
try instead

Dim db as DAO.Databas
Dim rs as DAO.Recordse


HTH
Immanuel Siber

Joe
Your code has been a big help. However, program execution stops at

Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot
I get Run-time error 91, "Object variable or With block variable not set
My Set statement is identical to yours, except for a query name strin

litera
instead of strTable

I have Dim statements

Dim rs As Recordse
Dim CurrentDb as Database (I added this one, but it didn't help

Any idea why I am getting this error
Thanks again ctda
----- Joe Fallon wrote: ----
If you write VBA code you can do pretty much anything you like wit

the dat
as you write it to the file
You should build 2-3 procedures simialr to this (1 for each majo section o
your file) and then call them when needed. If you take your time an revie
this code you will see it is not really that difficult to follow Then tak
the ideas and expand on them. (e.g. need data from 2 tables - open
recordsets!

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 nam

'strExportFile is the full path and name of file to export t
'strDelimiter is the field deliminator to use like Chr(9) for ta o
Chr(44) for comma or ?

Dim fld As Fiel

Dim varData As Varian
Dim rs As Recordse
Dim intFileNum As Intege

'set recordset on table or quer

Set rs = CurrentDb.OpenRecordset(strTable, dbOpenSnapshot

'get file handle and open for outpu

intFileNum = FreeFile(
Open strExportFile For Output As #intFileNu

If blnHeader The

'output the header row if requeste
varData = "
For Each fld In rs.Fields 'traverse the fields collectio
varData = varData & fld.Name & strDelimite
Nex

'remove extra last strDelimite

varData = Left(varData, Len(varData) - 1

'write out the header ro

Print #intFileNum, varDat
End I

'now your dat

Do While Not rs.EO
varData = "
'concatenate the data ro
For Each fld In rs.Field
varData = varData & fld.Value & strDelimite
Nex

'remove extra last strDelimite

varData = Left(varData, Len(varData) - 1

'write out data ro Print #intFileNum, varDat
rs.MoveNex Loo
Close #intFileNu

rs.Clos
Set rs = Nothin
End Su

--

Joe Fallo
Access MV

 

[Joe Fallon]

Yep.
The word you are looking for is: disambiguate.
It is better to use just DAO and no ADO reference at all.
Then you don't need to use a qualifier:
DAO.Recordset

Glad you got it going!
--
Joe Fallon
Access MVP

Joe,
Your answers have been a BIG HELP! Thanks for taking the time. Yes, I

added the reference to DAO 3.6. I don't plan on migrating my application to
anything else. It will stay in Access. What you are saying is that DAO is
all I need in that case. Is that correct?

Also, because I currently have both ADO & DAO referenced, I noticed two

recordset objects in the drop down list. You say the code can get confused.
Dumb question: Does that mean you have to somehow know which of the two
recordsets belongs to which object library? In other words, can you pick
the wrong one from the list and mess things up?