Pls Hlp! How to convert a .xls file to .txt file in Access / VB

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

Guest

I have a Access database application written in VB. In the code, I have.xls
file that I need to convert it to .txt file. How should I do this in the
code. I greatly appreciate any ideas.

Thanks

raj
 
Simplest approach would be to link to the spreadsheet so that it appears as
a linked table to Access. Then, you can use TransferText to convert it to a
..txt file.
 
Hi Douglas
Could you please explain me a bit more about linking my spread sheet to so
that it appears as
a linked table to Access

raj
 
Well, whether or not you can do it depends on the nature of the spreadsheet.
If you can, though, you can manually link through File | Get External Data |
Link Tables. Change the Files Of Type at the bottom left to Excel Files and
let the wizard help you. If you don't have a Files Of Type value for Excel,
look for msexcl35.dll on your hard drive (should be in the System folder) if
you're using Access 97, or msexcl36.dll if you're using Access 2000 or newer
and use regsvr32.exe to reregister it.

You can also link programmatically using the TransferSpreadsheet method.
 
THanks Douglas
I got it linked. I'm foing to create the .txt file now. I will let u know
how I go. Pls hlp me if i get stuck, as I have very little experience with
access programmig.

raj
 
HI Douglas

When I transfer the text file is created. But it is a mess. I want to
delimit the text file as a tab delimited. How can I do this in the
TextTransfer method.

raj
 
HI Douglas
When I transfer the text file is created. But it is a mess. I want to
delimit the text file as a tab delimited. How can I do this in the
TextTransfer method.

raj
Click to expand...
 
Did you specify acExportDelim as the type of transfer? I believe you need to
create a schema file using the text import/export wizard, and then provide
the name of as the specification file argument.
 
oops sorry it doesn'twork. I 'm loading the wrong file. (Tab delimitted text
file).

Yes I have specified the "acExportDelim" but still the result is same.

This is my code:

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9,
TableName:=XLSfileName,
fileName:="\\melapp01\RMO\Data\AAPTACT\Customers\AAPT-DATA-FINAL.xls",
hasfieldnames:=True

'converted to an csv file
DoCmd.TransferText acExportDelim, TableName:=XLSfileName,
fileName:="\\melapp01\RMO\Data\AAPTACT\Customers\" & XLSfileName & ".txt",
hasfieldnames:=True

newTextFileName = XLSfileName & ".txt"
 
Did u mean "Get External Data"-> import
Is that the way to create "create a schema file using the text import/export
wizard"

raj
 
Back
Top