PDA

View Full Version : [SOLVED] how to save excel sheet in a text file?



JackkG
06-18-2015, 03:31 PM
Hi All,


I need someone to help me with the automation here.


I got daily data files stored in "D:\DailyDataBack" folder, daily excel files are named according to dates, for eg. dailydata06192015.xlsm. I got almost 50+ such files in that folder and many more other files not starting with name "dailydata".


I want to cycle through each and every excel file in that folder that begins with file name "dailydata" and in every excel file in its second sheet/tab named "RiskReport", I want to save this as tab-delimited text file with the same name as "dailydata" excel file.


Can anyone help me out in this one?


Thanks!

cross-posted here:
http://www.excelforum.com/excel-programming-vba-macros/1089088-how-to-save-the-sheet-in-text-file-through-vba.html#post4106737

SamT
06-18-2015, 06:05 PM
Option Explicit

Sub SamT_OpenBook_SaveSheet()
'Opens files selected by name and save a sheets a tab delimited file
'Assumes Workbs are named like dailydata*.xlsm
'Assumes Sheet to Save as tab deleimit is named "RiskReport"
'Edit Constant SaveFolderPath as needed

Dim Filename As String
Dim NameLength As Long
Dim FileNames As Variant
Dim Fn As Long 'Fn = Index number for FileNames
Dim Wkb As Workbook

Const OpenFolderPath As String = "D:\DailyDataBack\" '<<<<<<<<<< include ending \
Const SaveFolderPath As String = "C:\TestFolder\" '<<<<<<<<<< include ending \

'''' Put all the file names in the path into an Array
FileNames = Filter(Split(CreateObject("wscript.shell").exec("cmd /c Dir " & _
OpenFolderPath & "dailydata*.xlsm /b /s").stdout.readall, vbCrLf), ".")


'''' Open one file at a time
For Fn = 0 To UBound(FileNames)
Set Wkb = Workbooks.Open(FileNames(Fn), UpdateLinks:=False, ReadOnly:=True)
Wkb.Sheets("RiskReport").SaveAs Filename:=(SaveFolderPath & Left(Wkb.Name, 17) & ".txt"), FileFormat:=xlTextWindows
Wkb.Close
Next Fn
End Sub

JackkG
06-19-2015, 02:17 PM
Hi SamT,

Thanks for the code, it worked great! Thanks a lot!! :)

SamT
06-19-2015, 02:19 PM
:thumb: