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.

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.

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.

Eileen,

While you could indeed invoke the standard Windows File Save As dialog
through an API call, it is quite a piece of code (you can find it ready
on the net, but that doesn't make it any shorter...) to copy from file
to file. Also, if the just-change-the-file-type-and-extension approach
works for you, then why introduce unnecessary manual steps? Here's all
you need:

strFileName = ActiveWorkbook.FullName
strFileName = Left(strFileName, Len(strFileName) - 3) & "xls"
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlNormal

HTH,
Nikos

Nikos,

Thanks, that bit of code solved my problem. However I'm now coming across
another problem that I didn't have before, and I'm not sure if it's related.

The next macro to run returns the filepath, worksheet name and sheet name of
the file:

Application.FindFile
AIRCell = "=cell(""filename"",a1)"
Range("q1") = AIRCell
AIRTab = "=MID(q1,FIND(""]"",q1)+1,LEN(q1)-FIND(""]"",q1))"
Range("q4") = AIRTab
AIR_Sheet = "=MID(q1,FIND(""["",q1)+1,FIND(""]"",q1)-FIND(""["",q1)-1)"
Range("q3") = AIR_Sheet
AIR_Results = "=LEFT(q1,FIND(""["",q1)-1)&q3"
Range("q2") = AIR_Results

Previously, the "cell(""filename"",a1)" command returned something along the
lines of

S:\filepath1\filepath2\[filename.xls]

But now it returns:

S:\filepath1\filepath2\filename.xls

without the square brackets, which messes up the formulas. I had this
problem previously, but it was solved by ensuring the filename had no spaces
in it, but that's not what's causing the problem this time. Any ideas?

Thanks,
Eileen.


"Nikos Yannacopoulos" wrote:

Eileen,

While you could indeed invoke the standard Windows File Save As dialog
through an API call, it is quite a piece of code (you can find it ready
on the net, but that doesn't make it any shorter...) to copy from file
to file. Also, if the just-change-the-file-type-and-extension approach
works for you, then why introduce unnecessary manual steps? Here's all
you need:

strFileName = ActiveWorkbook.FullName
strFileName = Left(strFileName, Len(strFileName) - 3) & "xls"
ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlNormal

HTH,
Nikos

Eileen,

I'm afraid I don't fully understand this code... are you trying to
separate the workbook and path? If yes, these will return the
corresponding stings:

ActiveWorkbook.Name
ActiveWorkbook.Path

while this will return them both as a whole:

ActiveWorkbook.FullName

Does this help?
Nikos

That did the trick! Thank you!

A colleague of mine (who is now off on maternity leave) wrote the code in
the previous post, so although I understood the gist of what it was doing, I
didn't understand it well enough to amend it. Your code is much simpler
though.

Thank you both, very much, for your help.

Eileen.

"Nikos Yannacopoulos" wrote:

Eileen,

I'm afraid I don't fully understand this code... are you trying to
separate the workbook and path? If yes, these will return the
corresponding stings:

ActiveWorkbook.Name
ActiveWorkbook.Path

while this will return them both as a whole:

ActiveWorkbook.FullName

Does this help?
Nikos