Renaming multiple files from *.csv to *.txt

[Richard Fuller]

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

 

[keepITcool]

Richard,

Have a look at the LOCAL argument on the OpenText method

try your files with local:=true and local:=false

introduced with ExcelXP.. and poorly documented, it may be what you're
looking for.






--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Richard Fuller wrote :

 

[keepITcool]

further to my earlier post:, some notes on OpenText see:

http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Richard Fuller wrote :

 

[Bob Phillips]

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]

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

 

[Richard Fuller]

Hi keepITcool,

Many thanks for your response, but the problem centred around a neat
means to rename *.csv files as text files - now cunningly solved by
Bob Phillips. However, I'm always glad to have more links to Excel
resources, so thanks for those, too.

All the best,

Richard Fuller

 

[Bob Phillips]

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