Flat file / Text file -- Export

Microsoft Excel 2003 (full product)
April 28, 2010 at 04:52:50
Specs: Windows XP
Dear all,

Maybe you can help!

I am facing an export problem with Excel which I am trying to solve... but I am quite unfortunate so far!

The goal is to export a worksheet as a flat file avoiding additionnal quotes (") and allowing for cells with more than 255 characters to be exported... i.e one line per record.

I have tried using an intermediate worksheet (output concatenated adding artificially ";;," in between each field). This works but I cannot get rid of (") nor get more than 255 chars out!

I am using the visual basic macro commands for the export.

any tips?

I have been programming with excel for 15 years... and never solved this problem properly. I have played arround so far (it worked) but now it has to be addressed [cells containing up to 2000 chars, export record up to 20 000 chars!]

Many thanks

Best regards


See More: Flat file / Text file -- Export

April 28, 2010 at 10:17:39
What are you exporting to?


Report •

April 29, 2010 at 00:08:43

I am exporting a set of data including numbers, text, dates.
[Text may be long: up to 2000 char]

They are separated by ";;," to avoid a problem when uploading thereafter (Oracle SQL).

Yesterday I found a solution (posted by someone -- sorry I forgot who -- under another request) which solved my problems... but does not explain why there's no direct way to export from excel using a standard VBA "save / save as filetype" statement.

Below is the solution given which works.

Many thanks

Best regards


Sub Export_Data(A As Integer)
Dim ObjFs As Object
Dim ObjText As Object
Dim StrData As String
Dim I As Integer

Set ObjFs = CreateObject("Scripting.FileSystemObject")

'create the text file with Path & text filename
Set ObjText = ObjFs.CreateTextFile("C:\MBE\M182\M182_exp.txt", True)

For I = 1 To A - 4

'create a string from the data
StrData = Cells(I, 1) + Cells(I, 2)

'write the string to the text file
ObjText.WriteLine (StrData)

Next I

'close the text file

'remove the file system object
Set ObjFs = Nothing

End Sub

Report •
Related Solutions

Ask Question