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

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
...
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

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" wrote in message
...
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
...
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

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 ActiveSheet.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 Worksheet?", _
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
...
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" wrote in message
...
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
...
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

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
...
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 ActiveSheet.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 Worksheet?", _
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
...
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" wrote in message
...
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
...
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

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" wrote in message
...
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
...
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 ActiveSheet.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 Worksheet?", _
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
...
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" wrote in message
...
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
...
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

Chip,
One last thing.
How do I modify the Export Macro so that when it asks me to Save File As--it
automatically puts the extension ".txt" there instead of .xls?
Tx,
S


"Chip Pearson" wrote in message
...
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
...
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 ActiveSheet.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 Worksheet?", _
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
...
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" wrote in message
...
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
...
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

Where did you install the macros, same workbook ?
Where do you use the macros, same workbook ?

If you use the macro from a different workbook that
workbook should be hidden if you want access to the
macros without specifying the workbook name.

Excel Window menu, hide files

More information in
http://www.mvps.org/dmcritchie/excel/install.htm

I am assuming you copied Chip's code without changes.
Are the two macro in the same module if they are and one
works then they both in standard modules and neither is
in a class module.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"saturnin02" <saturnin02_at_hotmail.com wrote in message ...
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