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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.