Re: Chip Pearson Import/Export Macros - Added Twist

From: saturnin02 (saturnin02_at_hotmail.com)
Date: 04/04/04 

Date: Sun, 4 Apr 2004 18:44:26 -0400

Chip,
Everything works good with the export to text macro.
I need to modify it to reflect a specila twist so that another program can
read it, and that is I want the macro to export all cell values as text, but
put each cell value in one column only i.e. in destination file.

A1 A2 A3
B1 B2 B3

to
A1
A2
A3
B1
B2
B3

Is it possible to modify it that way?
Sorry, put I am using a program in DELPHI that will only import text files
with values in one column only.

Let me know.
Tx,

"Chip Pearson" <chip@cpearson.com> wrote in message
news:OkbimkZGEHA.3592@TK2MSFTNGP10.phx.gbl...
> The " _" characters (note the leading space before the
> underscore) indicate that a line of code is continued on the next
> line of text in the file. You can use this to split logical lines
> of code across several physical lines of text in order to improve
> readability. They have no effect on the execution of the code.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> news:u$jx6TZGEHA.704@tk2msftngp13.phx.gbl...
> > Chip,
> > You were absolutely right! I put the 'Sep As String' and
> 'SelectionOnly As
> > Boolean' on the same line and it works.
> > What is the "_" at the end or within some of the lines?
> > Tx for your help!
> > S
> > "Chip Pearson" <chip@cpearson.com> wrote in message
> > news:eHB01uYGEHA.3984@TK2MSFTNGP10.phx.gbl...
> > > The code works for me just fine. In the Public Sub
> > > ExportToTextFile declaration, ensure that 'Sep As String' and
> > > 'SelectionOnly As Boolean' are on the same line. You may be
> > > suffering from line break problems caused by copy/paste.
> > >
> > > --
> > > Cordially,
> > > Chip Pearson
> > > Microsoft MVP - Excel
> > > Pearson Software Consulting, LLC
> > > www.cpearson.com
> > >
> > > "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> > > news:u6%23hz%23RGEHA.2980@TK2MSFTNGP09.phx.gbl...
> > > > Sorry Chip--U didn't realize you meant post it HERE.
> > > > I am just using is AS IS on your site.
> > > > Public Sub ExportToTextFile(FName As String, _
> > > > Sep As String,
> > > > SelectionOnly 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 Active***.UsedRange
> > > > StartRow = .Cells(1).row
> > > > StartCol = .Cells(1).Column
> > > > EndRow = .Cells(.Cells.Count).row
> > > > EndCol = .Cells(.Cells.Count).Column
> > > > End With
> > > > End If
> > > >
> > > > Open FName For Output Access Write As #FNum
> > > >
> > > > For RowNdx = StartRow To EndRow
> > > > WholeLine = ""
> > > > For ColNdx = StartCol To EndCol
> > > > If Cells(RowNdx, ColNdx).Value = "" Then
> > > > CellValue = Chr(34) & Chr(34)
> > > > Else
> > > > CellValue = _
> > > > Application.WorksheetFunction.Text _
> > > > (Cells(RowNdx, ColNdx).Value, _
> > > > Cells(RowNdx, ColNdx).NumberFormat)
> > > > 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
> > > >
> > > > And the other one is:
> > > > Public Sub DoTheExport()
> > > > Dim FName As Variant
> > > > Dim Sep As String
> > > >
> > > > FName = Application.GetSaveAsFileName()
> > > > If FName = False Then
> > > > MsgBox "You didn't select a file"
> > > > Exit Sub
> > > > End If
> > > >
> > > > Sep = InputBox("Enter a single delimiter character (e.g.,
> comma
> > > or
> > > > semi-colon)", _
> > > > "Export To Text File")
> > > >
> > > > ExportToTextFile CStr(FName), Sep, _
> > > > MsgBox("Do You Want To Export The Entire Work***?", _
> > > > vbYesNo, "Export To Text File") = vbNo
> > > > End Sub
> > > >
> > > > I am sure I need to DEFINE some parameters here is that
> > > correct?
> > > >
> > > > S
> > > >
> > > > "saturnin02" <saturnin02_at_hotmail.com> wrote in message
> > > > news:#lLLA2RGEHA.2512@TK2MSFTNGP12.phx.gbl...
> > > > > Chip, Bear with me but I do not understand.
> > > > > Do you mean in the same workbook as the import one,
> separate
> > > workbook
> > > > .....
> > > > > Which macro first, etc.
> > > > > I am really a newb--please help me get going.
> > > > > TX.
> > > > > S
> > > > >
> > > > > "Chip Pearson" <chip@cpearson.com> wrote in message
> > > > > news:#W6JrGRGEHA.3188@TK2MSFTNGP10.phx.gbl...
> > > > > > Post the code you are using to call the export macro.
> > > > > >
> > > > > >
> > > > > > --
> > > > > > Cordially,
> > > > > > Chip Pearson
> > > > > > Microsoft MVP - Excel
> > > > > > Pearson Software Consulting, LLC
> > > > > > www.cpearson.com
> > > > > >
> > > > > >
> > > > > > "saturnin02" <saturnin02_at_hotmail.com> wrote in
> message
> > > > > > news:%230d2j6QGEHA.744@TK2MSFTNGP09.phx.gbl...
> > > > > > > Hi,
> > > > > > >
> > > > > > > I need help on the Macros posted on chip's web site
> at
> > > > > > > http://www.cpearson.com/excel/imptext.htm
> > > > > > >
> > > > > > > (I am a newbie at VB.)
> > > > > > >
> > > > > > > I have basically copied and pasted the 2 macros for
> > > > > > importing-each in a
> > > > > > > separate module and saved in a file
> (importtotext.xls).
> > > > > > >
> > > > > > > When I open the file and then minimize it, if I have
> > > another
> > > > > > file open into
> > > > > > > which I want to import text data, I run the macro and
> it
> > > works.
> > > > > > >
> > > > > > > (I may not even be doing this right.)
> > > > > > >
> > > > > > > Doing the same for the EXPORT macro does not work.
> > > > > > >
> > > > > > > I just copied and pasted into a module each of the
> EXPORT
> > > > > > macros but no
> > > > > > > joy--I get error messages such as "delimiter"
> expected
> > > here, or
> > > > > > parameter
> > > > > > > expected etc.
> > > > > > >
> > > > > > > I was wondering if anyone could help me figure it out
> as
> > > I
> > > > > > could REALLY use
> > > > > > > the export macros!
> > > > > > >
> > > > > > > Tx a million.
> > > > > > >
> > > > > > > S
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>