Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming multiple files from *.csv to *.txt
Notwithstanding KeepItCools offering, this is an amended version of your
code. You might want to add some error checking on the file type Sub OpenFilesWin98() ' Macro copied 07-12-2001 from Google Newsgroup - microsoft.public.Excel ' Written by Robert Rosenberg, MVP Excel http:\\ntware.com 'Updated to rename css files to txt by Bob Phillips, 18th April 2005 ' Shows Open File dialogue & opens selected CSV files Dim ScrMode As Boolean Dim lCount As Long Dim szNames As Variant ScrMode = Application.ScreenUpdating lCount = 0 ChDir "C:\Documents and Settings\Owner\My Documents\DATA\" & Left(sMth, 3) szNames = Application.GetOpenFilename(MultiSelect:=True) If IsArray(szNames) Then ' At least one file selected Application.ScreenUpdating = False ' Loop through the array of files selected For lCount = LBound(szNames) To UBound(szNames) strnewname = Left(szNames(lCount), Len(szNames(lCount)) - 3) & "txt" Name szNames(lCount) As strnewname Workbooks.Open Filename:=strnewname, UpdateLinks:=False, ReadOnly:=True, Format:=2 Next lCount End If Application.ScreenUpdating = ScrMode End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Richard Fuller" wrote in message m... Unlike Excel 2000, which I used until the recent demise of my "old" computer, Excel 2003 (WinXP) scrambles dates in *.csv files opened with a VBA macro. The simple cure is to rename the *.csv files to *.text before opening them with the Workbooks.OpenText method and stipulate how the date string should be handled. This is easy enough with only one file, but how can it be incorporated with the GetOpenFilename(MultiSelect:=True)method? What I'd like to do is have each selected file renamed to *.txt within the loop before it is opened. I'd be forever grateful for some sample code to point me in the right direction. Here's the code I've been using for some years (called by the main routine): Sub OpenFilesWin98() ' Macro copied 07-12-2001 from Google Newsgroup - microsoft.public.excel ' Written by Robert Rosenberg, MVP Excel http:\\ntware.com ' Shows Open File dialogue & opens selected CSV files Dim ScrMode As Boolean Dim lCount As Long Dim szNames As Variant ScrMode = Application.ScreenUpdating lCount = 0 ChDir "C:\Documents and Settings\Owner\My Documents\DATA\" & Left(sMth, 3) szNames = Application.GetOpenFilename(MultiSelect:=True) If IsArray(szNames) Then ' At least one file selected Application.ScreenUpdating = False ' Loop through the array of files selected For lCount = LBound(szNames) To UBound(szNames) Workbooks.Open Filename:=szNames(lCount), UpdateLinks:=False, ReadOnly:=True, Format:=2 Next lCount End If Application.ScreenUpdating = ScrMode End Sub TIA Richard Fuller Auckland, NZ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming multiple files from *.csv to *.txt
Bob,
Very many thanks indeed - just the neat solution I was looking for. By the way, I'm thrilled to find I'm not the last old fuddy-duddy on earth to insist upon writing dates in the old style of 18th April 2005, ie. correctly. I suppose you wouldn't have a macro that applies ordinals to dates in Word? I did this many years ago with WordPerfect 5.1, but have no idea if it's possible with Word. All the best, Richard Fuller |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Renaming multiple files from *.csv to *.txt
Oy, who are you calling an old fuddy-duddy <g
By applies ordinals to dates, do you mean convert a date in a word doc like 03/05/205 to 3rd April 2005? Regards Bob "Richard Fuller" wrote in message om... Bob, Very many thanks indeed - just the neat solution I was looking for. By the way, I'm thrilled to find I'm not the last old fuddy-duddy on earth to insist upon writing dates in the old style of 18th April 2005, ie. correctly. I suppose you wouldn't have a macro that applies ordinals to dates in Word? I did this many years ago with WordPerfect 5.1, but have no idea if it's possible with Word. All the best, Richard Fuller |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Renaming Files | Excel Discussion (Misc queries) | |||
Renaming multiple files from *.csv to *.txt | Excel Programming | |||
Renaming multiple files from *.csv to *.txt | Excel Programming | |||
Renaming Files | Excel Programming | |||
Renaming files | Excel Programming |