Hi, thanks for the quick reply.

Sorry, I should have mentioned what the specific problem was. When I try to
use the .saveas command, it asks me if I want to save over the file that I
opened, which will just save it as a text file again, when I need to save it
as an excel file.

The code you provided would do the job for a stationary file, but the path
and filename is going to change every time this macro is run. Ideally I'd
like to bring up the dialogue box that you get when you go through File -
Save As, so that I can specify a different location, filename and file type
each time, but I'm not sure if that's possible.

If it helps, it will always be saved in the same directory as the file with
the macro being run (this file will just be copied across many locations),
and I'm happy to save the .xls file with the same name as the .txt file.

Thanks again.


"JLatham" wrote:

You didn't say exactly what the roadblock is. I suspect it's with the alerts
coming up when you try to .SaveAs? You can turn those off before the save as
command:

Application.DisplayAlerts = False
Activeworkbook.SaveAs Filename:= "s:\NewPath\Folder\filename.xls"
Application.DisplayAlerts = True
Activeworkbook.Close

You can always record a macro while using the SaveAs and look at the code it
generates if you need more details on any special options of the SaveAs
method that you may be using.

If you want to shut down Excel after closing the workbook, just add:
Application.Quit after the .Close statement.

"Eileen" wrote:

Hi.

I'm trying to write a macro that will open up a text file in excel, do a
bunch of formatting stuff, then save the file with a specified name (as in
the Save As dialogue box, doing it manually) as a .xls file. I've got the
first couple of bits sorted, but I've run up against a bit of a roadblock
with the saving part. Any help would be greatly appreciated.

My code so far looks like:

Workbooks.OpenText Filename:= _
"S:\International department\2006 Projects\Master Cat Pricing
Model\Adding AIR\Catrader Output.txt" _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1)), _
TrailingMinusNumbers:=True
ActiveWindow.ActivateNext
ActiveWindow.ActivateNext
ActiveCell.Rows("1:27").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Offset(26, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Event ID"
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveCell.FormulaR1C1 = "Loss"
Range("A1").Select

I want to save and close the file immadiately after this.

Thanks,
Eileen.