Excel sheets to text files?
Hi can you help me fill in the code to export my sheets to text (tab) delimited files? This is what I've done so for.
Sub Macro1()
Dim wksht As Worksheet
Dim i As Long
Dim sFilename As String
Dim wkshtnames() 'This is an array definition
i = 0
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
'strFilename = "C:\" & wkshtnames(i) & ".txt"
'sPath = "C:\"
'Open strFilename For Binary As #1
'Put #1, , TextArray 'writes to file
'Close #1
Next wksht
For i = LBound(wkshtnames) To UBound(wkshtnames)
MsgBox wkshtnames(i)
Next i
End Sub
THANK YOU
Re: Excel sheets to text files?
Kenny:
I'm not proficient enough to read your code straight through, and I'm in a rush this morning ... here are a couple of thoughts ...
In your sheet loop ...
(you can record a macro to see how to code these) ...
Edit > Move or Copy Sheet > To Book: (new book)
This gets away from the problem of SaveAs not working for a book with multiple sheets.
then:
SaveAs your "new book" in a file as a Tab Delimited Text file with your desired filename:
ChDir "C:\YourPath"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\guawd1\Desktop\JUNKjunkJUNK.txt", FileFormat:= _
xlText, CreateBackup:=False
Hope this helps a little ... I'll check back later.
Re: Excel sheets to text files?
Code:
Sub Macro1()
Dim wksht As Worksheet
Dim i As Long
Dim sFilename As String, sData as string
Dim wkshtnames() 'This is an array definition
Dim lngX as Long, lngY as Long
i = 0
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
strFilename = "C:\" & wkshtnames(i) & ".txt"
Close #1
Open strFilename For Output As #1
for lngX = 1 to 10
sData = ""
for lngY = 1 to 10
sData = sData & iif(lngY>1,vbtab,"") & wksht.cells(lngY,lngX).value
next
print #1,sData
next
Close #1
Next wksht
For i = LBound(wkshtnames) To UBound(wkshtnames)
MsgBox wkshtnames(i)
Next i
End Sub
It would be something like this, but you'd have to specify the max rows and max cols (instead of 10)
Re: Excel sheets to text files?
This is my latest working version!!
Thanks
Sub export()
Dim FName As String
Dim wksht As Worksheet
Dim i As Long
Dim wkshtnames() 'This is an array definition
Application.ScreenUpdating = False
On Error GoTo EndMacro:
i = 0
For Each wksht In ActiveWorkbook.Worksheets
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
FName = "C:\temp\" & wkshtnames(i) & ".txt"
wksht.Activate
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:= _
xlText, CreateBackup:=False
Next wksht
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
End Sub
Re: Excel sheets to text files?
Ken:
(using SQUARE brackets [], not {} the curly braces shown)
When you post code, prefix your code with the text {code}, and put the text{/code} at the end. That way your code will be posted in "pretty" format.
(Hey, does anybody know how to trivially "UNTAG" the code and /code tags in the body of a message?)
Re: Excel sheets to text files?
Kenny:
As far as I know, you can NOT SaveAs a workbook containing multiple sheets as an xlText file (I may be wrong). That's why I suggested doing an Edit Copy the sheet to a new book first, because the new book will only contain the copied sheet and no other(s).