Export

[captnk]

I want to export some data from Column A in a spreadsheet as textand save it ,without adding a " ",to the data.
For instance the current data (say in sheet1 ColumnA) reads like:
"name1","name2","data3","data4","etc",
Exporting as text results in getting:
""name1"",""name2"",""data3"",""data4"",""etc"",
How can I export it in a text format without excel adding the extra " "
(The reason it already has an " " in the excel data is so I can cut and paste the data,however I would like to have an auto option available as well without it adding the extra " ").
Any suggestion please

 

[captnk]

Well judging by the nil response,no one seems to have a solution to that in this format.
So can it be done with some code that simply autos the cut and a paste (manual function)
All that is required is to select Column A (Selectall) then cut and save (or paste) as a .txt file
Thanks

 

[georgedwilkinson]

I think I used a built in Excel function to remove formatting to get rid of unwanted "stuff" Access/Excel put in while moving data around.

Cells.ClearFormats

 

[captnk]

Tks George
That works fine within access and excel,but doesnt seem to work when applied to exporting data.
Maybe its because windows takes control once u r outside of Office.
Anyway it has given me an idea and I will have a look at doing in DOS with a bat file
Thanks

 

[georgedwilkinson]

OK, let us know how you solve it. I'm not sure I totally understand the issue you're having but I did have a similar issue that I solved as posted earlier.

 

[captnk]

Tks George
The situation is that in an excel file in say column A is I have data in the following format:
"name1","name2","data1","data2","","","data3"
I have inserted the " " to make the data as text,so I can cut and paste it manually into say notepad,so it is in the format I need to Import it into another prog.
If I use excel to export it ,it adds another set of " ",onto the existing data.
eg ""name1"",etc
If I use the method u have suggested,by clearing the " " initially,it of course also clears the "" in the blank columns as well ,so that they are no longer seen as text
eg Name1,Name2,data1,data2,,,data3
Exporting as u suggest can then add the "" back onto the data,but it doesnt add the "" in the null (blank fields) fields.
Excel seems to be a bit choosy as to where it adds back a "".
Anyway yr idea is food for thought and I will play around a bit with yr suggested theme and see how it progresses.
Tks for your thoughts

 

[captnk]

Incidentally I should probably explain thats its in that format,"data1","","","data2" etc,because I actually mostly buy the source data,and its in that format.
So all my developement has been based on using the commercial data.
All Im trying to do is build up an alternative source for getting the data,otherwise if they go bust or whatever the whole thing will be useless.
So the data format was not my decision,Im just trying to match it so I have a backup option available
Its much quicker to buy and download it,but unfortunately u cant be sure how long its all going to be available for
Regards

 

[georgedwilkinson]

I comprehend. We have a long time forum member here, Mike375, who uses a lot of commercial lists. I always thought he had a pretty good grasp on how to manipulate the data he gets. Might be worth asking him.

 

[chergh]

Your wanting to use Chip Pearsons exporttotextfile sub.

Here's the code:

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a 
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Text
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum

End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile

The original page is at:

http://www.cpearson.com/excel/ImpText.aspx

which will also provide guidance on how to use.

 

[captnk]

Tks chergh !
I have been a way a few days and just back,so I will have a look at yr idea
regards