open Excel formatted differently than the default format

C

Chip

I have a series of CSV files that ned to be converted to XLS and reformatted
at the same time. Is there any way to click on the CSV file and have it open
into a preformatted XLS spread sheet that can then be saved in the XLS file
type?
 
D

Dave Peterson

No, but you could create a macro that would open each of your .csv files, format
it the way you want, save it (and repeat).

Start a new workbook and record a macro when you open one of the CSV files.
Continue recording when you format it.

Then you can tweak the macro to look more like:

Option Explicit
Sub testme()

Dim myFileNames As Variant
Dim iCtr As Long
Dim wkbk As Workbook
Dim NewFileName As String

myFileNames = Application.GetOpenFilename _
("CSV Files, *.csv", MultiSelect:=True)

If IsArray(myFileNames) = False Then
Exit Sub 'user hit cancel
End If

For iCtr = LBound(myFileNames) To UBound(myFileNames)
NewFileName _
= Left(myFileNames(iCtr), Len(myFileNames(iCtr)) - 4) & ".xls"
Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr))
'do your formatting

'save the workbook
Application.DisplayAlerts = False
wkbk.SaveAs Filename:=NewFileName
Application.DisplayAlerts = True
wkbk.Close savechanges:=False
Next iCtr
End Sub

The multiselect stuff allows you to click on the first and ctrl-click on
subsequent in the File|open dialog. That way you just select the ones you want
to convert.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top