export many sheets to text files
 

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

export many sheets to text files
 

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

export many sheets to text files
 

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

export many sheets to text files
 

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