I'm trying to adapt code from http://www.cpearson.com/excel/imptext.htm to
export 150 spreadheets of data to separate text files.
It seems to work as long as I type the name of each text file into the save
as box. I was hoping someone could show me how to alter the code so that the
name of the text file could set automatically based on either the contents of
cell A1 or the sheet name of the sheet being exported.
Here's what I have so far:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim m As Integer

For m = 1 To Sheets.Count
ChDir "C:\Documents and Settings\TJ\Desktop\QualDatTEXT"
FName = Sheets(m).Range("A1") 'I know this is wrong-I don't know how to fix it
FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = ","

ExportToTextFile CStr(FName), Sep, False
Next m
End Sub


--
tj

Get rid of the line

FName = Application.GetSaveAsFilename()

Other than that, your code looks OK.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"tjtjjtjt" wrote in message
...
I'm trying to adapt code from
http://www.cpearson.com/excel/imptext.htm to
export 150 spreadheets of data to separate text files.
It seems to work as long as I type the name of each text file
into the save
as box. I was hoping someone could show me how to alter the
code so that the
name of the text file could set automatically based on either
the contents of
cell A1 or the sheet name of the sheet being exported.
Here's what I have so far:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim m As Integer

For m = 1 To Sheets.Count
ChDir "C:\Documents and Settings\TJ\Desktop\QualDatTEXT"
FName = Sheets(m).Range("A1") 'I know this is wrong-I don't
know how to fix it
FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = ","

ExportToTextFile CStr(FName), Sep, False
Next m
End Sub


--
tj

Thanks.

tj

"Chip Pearson" wrote:

Get rid of the line

FName = Application.GetSaveAsFilename()

Other than that, your code looks OK.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"tjtjjtjt" wrote in message
...
I'm trying to adapt code from
http://www.cpearson.com/excel/imptext.htm to
export 150 spreadheets of data to separate text files.
It seems to work as long as I type the name of each text file
into the save
as box. I was hoping someone could show me how to alter the
code so that the
name of the text file could set automatically based on either
the contents of
cell A1 or the sheet name of the sheet being exported.
Here's what I have so far:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim m As Integer

For m = 1 To Sheets.Count
ChDir "C:\Documents and Settings\TJ\Desktop\QualDatTEXT"
FName = Sheets(m).Range("A1") 'I know this is wrong-I don't
know how to fix it
FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = ","

ExportToTextFile CStr(FName), Sep, False
Next m
End Sub


--
tj

Without knowing what is in cell A1, this is a possibility

Public Sub DoTheExport()
Dim FName As String
Dim Sep As String
Dim m As Integer
Dims sStr as String

sStr = "C:\Documents and Settings\TJ\Desktop\QualDatTEXT\"
chdrive sStr
chdir sStr

For m = 1 To Sheets.Count
FName = Sheets(m).Range("A1").Text
' or FName = Sheets(m).Name
FName = sStr & FName & ".txt"
Sep = ","

ExportToTextFile FName, Sep, False
Next m
End Sub

--
Regards,
Tom Ogilvy


"tjtjjtjt" wrote in message
...
I'm trying to adapt code from http://www.cpearson.com/excel/imptext.htm to
export 150 spreadheets of data to separate text files.
It seems to work as long as I type the name of each text file into the
save
as box. I was hoping someone could show me how to alter the code so that
the
name of the text file could set automatically based on either the contents
of
cell A1 or the sheet name of the sheet being exported.
Here's what I have so far:

Public Sub DoTheExport()
Dim FName As Variant
Dim Sep As String
Dim m As Integer

For m = 1 To Sheets.Count
ChDir "C:\Documents and Settings\TJ\Desktop\QualDatTEXT"
FName = Sheets(m).Range("A1") 'I know this is wrong-I don't know how to
fix it
FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = ","

ExportToTextFile CStr(FName), Sep, False
Next m
End Sub


--
tj