Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

need to rename a CSV to a TXT using excel

Posted on 2011-10-17
22
Medium Priority
?
427 Views
Last Modified: 2012-08-13
I am having trouble getting excel to rename a csv file to a txt in a macro.

Any suggestions ?
0
Comment
Question by:RedstoneIT
  • 12
  • 7
  • 2
  • +1
22 Comments
 
LVL 4

Expert Comment

by:sravi2208
ID: 36979651
Instead of renaming the file use "Save as" option and save it as txt file. (Save type as Txt)
0
 

Author Comment

by:RedstoneIT
ID: 36979661
This has to be done via macro as it is part of a 250 line macro
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36979669
Name "C:\blah.csv" as "C:\blah.txt"

Open in new window

for example.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RedstoneIT
ID: 36979740
Ok, lets try this another way. This is the code I currently have. I need it to save the file as a CSV file but also with the option to save as a text file.



Sub exportToLaborCSVTimberline()
Dim wkb As Workbook
Dim wks As Worksheet
Dim fileSaveName As Variant

       
    Set wkb = ActiveWorkbook
    Set wks = wkb.Sheets("Labor Totals Temp")
   
    fileSaveName = Application.GetSaveAsFilename(fileFilter:="CSV (Comma Delimited) (*.csv), *.csv", Title:="Please select a folder and specify filename to save CSV file for Timberline Export")
    If fileSaveName <> False Then
        Application.DisplayAlerts = False
        wks.Copy 'copies to a new workbook
        ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlCSV
        ActiveWorkbook.Close
        MsgBox "The Labor Totals Temp tab was successfully saved as " & fileSaveName
    End If
   
ActiveWorkbook.Sheets("Labor Totals Temp").Delete
ThisWorkbook.Worksheets("Controls").Cells(11, 8).Value = "Completed write to Labor Totals Timberline Import file."

Application.Goto reference:=Sheets("Controls").[a1]

Name fileSaveName As "C:\blah.txt"
End Sub
0
 

Author Comment

by:RedstoneIT
ID: 36979744
ignore the line Name fileSaveName As "C:\blah.txt"
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36979795
To give the choice of save formats:
    filesavename = Application.GetSaveAsFilename(fileFilter:="CSV (Comma Delimited) (*.csv), *.csv, Text Files (*.txt), *.txt", Title:="Please select a folder and specify filename to save CSV file for Timberline Export")
    If filesavename <> False Then
        Application.DisplayAlerts = False
        wks.Copy 'copies to a new workbook
        If LCase$(Right(filesavename, 3)) = "csv" Then
         ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlCSV
        Else
         ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlTextMSDOS
      End If
   End If

Open in new window

0
 

Author Comment

by:RedstoneIT
ID: 36979854
ok, It gives me the option, but the file, when saved as a txt, does not use comma seperators. Can that be done ?
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36979856
I do a ton of Sage Timberline Office interfaces to work with our Web Platform.   Does this need to be done inside Excel?  My guess is that you need to save it first as a text to get the format you want, and then in order to access it properly you need it to be called a CSV.  If that's the case you could use coding similar to above to save it as a text, and then use code to just rename the existing file.

Alternatively if you kick this off as a .VBS or a .PS you could do a simple rename proceedure using either of those scripting options.

-Script Addict.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36979867
If you want comma separators, then don't alter the format, just use:
    filesavename = Application.GetSaveAsFilename(fileFilter:="CSV (Comma Delimited) (*.csv), *.csv, Text Files (*.txt), *.txt", Title:="Please select a folder and specify filename to save CSV file for Timberline Export")
    If filesavename <> False Then
        Application.DisplayAlerts = False
        wks.Copy 'copies to a new workbook
         ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlCSV

   End If

Open in new window

0
 

Author Comment

by:RedstoneIT
ID: 36979873
Script You are correct that it is for Timberline. The problem I'm having is that the Labor totals sheet MUST be in a comma delimited .txt file, (unlike many others that can be imported as a .csv.

Any ideas ?
Regards,
Brian
0
 

Author Comment

by:RedstoneIT
ID: 36979915
Rorya,

If I must use a CSV file, so be it, but is there a way to allow the user to select a folder then rename the file from csv to .txt ?

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36979972
Check the code - it allows you to name it as txt, but saves it as comma-separated.
0
 

Author Comment

by:RedstoneIT
ID: 36980003
I did check Rorya, it is opening as a tab separated file, not a comma separated file
0
 

Author Comment

by:RedstoneIT
ID: 36980026
I used notepad to check with
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36980035
I tested before posting, and have just tested again, and it saves as comma-separated. What are you opening it with?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36980045
Are you sure you are opening the correct file? I do not see how that can be since the format is specified as csv in the code.
0
 

Author Comment

by:RedstoneIT
ID: 36980050
notepad. It will be opening with timberline. But if it opens in notepad with commas it will open in Timberline the same way.
0
 

Author Comment

by:RedstoneIT
ID: 36980076
here are the two files created 1 with the CSV setting, one with the TXT setting
testfortimberline.txt
test-for-timberline.csv
0
 

Author Comment

by:RedstoneIT
ID: 36980092
The funny thing is, I can use the command

Name fileSaveName As "c:\blah.txt" and it will open in notepad with the commas intact.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 36980102
And you create the txt file with the last code I posted, not the second version? In other words, you are using this:
    filesavename = Application.GetSaveAsFilename(fileFilter:="CSV (Comma Delimited) (*.csv), *.csv, Text Files (*.txt), *.txt", Title:="Please select a folder and specify filename to save CSV file for Timberline Export")
    If filesavename <> False Then
        Application.DisplayAlerts = False
        wks.Copy 'copies to a new workbook
         ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlCSV
   End If

Open in new window

0
 

Author Closing Comment

by:RedstoneIT
ID: 36980133
Thanks for the hard work Rorya !
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 36980338
Whups!  Looks like I had that backwards CSV saved and imported as Text.  I guess I've had that scripted for a LONG time ;)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question