Save Data Pipe delimited keep adding new files with different names

excel 2003 vba
I have data in a workbook  "MyWork.xls"

All my important information is on "Sheet1"
Column Headers  A1:AU1
I need a vba sub routine that from a command button.

1. Will save the sheet to txt pipe delimited file to a folder called

C:\Program Files\MySavedData

2. a NEW  txt file needs to be created each time a person clicks the button.

"<nameofworkbook & DateTime>.txt"

So at the end of the day , if a person clicked the button 6 times...the folder would contain 6 txt files...

3. incude empty rows and empty cells in range

Thanks
fordraiders







LVL 3
FordraidersAsked:
Who is Participating?
 
Aland CoonsSystems EngineerCommented:
Here's what I found.  I'll let you put it all together since it's your macro and you know all the paths and file names you would want to use ..

How to save a file pipe delimited:
http://www.ehow.com/how_5900012_save-excel-spreadsheet-pipe-delimited.html

Summary:  This seems to be a System setting rather than an Excel setting.  Under Control Panel and Regional and Language Options you would customize you List Separator to use pipe symbol.

Export range of cells as a text file:
http://forums.devshed.com/visual-basic-programming-52/how-do-i-export-a-range-of-excel-cells-as-496507.html

Example Code:
Worksheets(1).Copy
Activeworkbook.SaveAs FileName:="file1.txt", FileFormat:=xlTextMSDOS
Activeworkbook.close false

From another site I found how to append the date/time to the file name:
http://www.ozgrid.com/forum/showthread.php?t=65552&page=1

Code Excerpt:
Sub filesave()
ActiveWorkbook.SaveAs Filename:="C:\nashfinch\NFC-ORDER " & _
  Format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
  WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

0
 
FordraidersAuthor Commented:
Thanks and chopped this together also fyi..
Sub subExportPipe()
    Dim v
     v = ActiveSheet.UsedRange.Value
    Dim strTemp As String
    Dim i As Integer, j As Integer
    Dim f As String
    Dim strFilename As String
   
    strFilename = "C:\Program Files\Crs\Final\FinalBackups\" & ActiveWorkbook.Name & "_" & Format(Date, "YYYYMMDD") & " " & Format(Time, "hh.mm.ss") & ".txt"
   
    f = strFilename  '  this is the file name
    Open f For Output As #1

    For i = 1 To UBound(v, 1)
        strTemp = ""
        For j = 1 To UBound(v, 2)
            strTemp = strTemp & v(i, j) & Chr(124)
        Next j
        Print #1, Left(strTemp, Len(strTemp) - 1)
    Next
    Close #1
End Sub
0
 
FordraidersAuthor Commented:
Thanks !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.