SAveAs worksheet

[Guest]

I have a macro that reads in, processes data & places it onto a presentation
worksheet. The following line allows ONLY the worksheet to be saved which is
what I want. My problem is that I get the 2 messages appearing & I want to
switch them off so that filing of the worksheet is automated.

Any suggestions ??

SaveAs Filename:="c:\ptp\results.mea", FileFormat:=xlText

Message 1 :"Do you want to save changes you made to results.mea"
&
Message 2 :"The file Results.mea already exists. Do you want to overwrite ?

Using - Application.DisplayAlerts = False does not work.

Brian

 

[Guest]

Application.DisplayAlerts = False
....your save as code here
Application.DisplayAlerts = True

 

[Guest]

Hi JL

Unfortunately I had already tried this but with no success. Any other
suggestions ??? thx

 

[Guest]

Excuse me JL...hey Bryan...Are u trying to say is to save changes on a single
worksheet within a workbook containing "n" numbers of worksheets ? Ill try if
u reply!!!

 

[Guest]

Brian,
No, it works for me and I've tried it while just using the filename without
the FileFormat specified and with it specified as xlText

I do get the alert after that when closing the file, but that could be done
away with by doing the same thing with it as

Application.DisplayAlerts=False
ActiveWorkbook.SaveAs Filename:="c:\ptp\results.mea", FileFormat:=xlText
ActiveWorkbook.Close

So I'm not really understanding why you're getting the alert.
I notice in your code example you only used "SaveAs" - are you prefacing
that with ActiveWorkbook.SaveAs or ActiveSheet.SaveAs
??

Although it works both ways for me, again without prompts.

 

[Guest]

Hi

Thanks for the support but ....

Nope, not working for me. I did not have a preface to teh SaveAs. But even
when I do use ActiveWorksheet.SaveAs it still returns the same messages. I am
only interested in saving teh current worksheet to the text file format.
Maybe there is nother approach I can't think of ? or a way to configure th
emessages in general not to appear ?!?!

 

[Guest]

Hello JL

When I remove the xlText format it works, however, when the text file is
opened is contains lots of unwanted characters so I need to format the output
file. As soon as a FileFormat is added then the questions are asked. The
output file has each line formatted to 80 characters so that it can then be
imported into another application.

Any suggestions how to get around this ?

Cheers

 

[Guest]

Yes, that's what he is doing/trying to do. Notice that he is saving as file
type xlText, not as a regular .xls file. When saving as .txt, .csv and
probably a couple of other types, only one sheet from the book can be saved
at a time anyhow. So singling out a specific sheet shouldn't be the issue -
the active sheet is the one by default.

Problem right now is that he cannot stop the alerts from popping up during
the save or SaveAs process. Go back to his first post. He says he's even
getting the "Do you want to save changes you made to results.mea" - which
shouldn't even come up since he is (should be) already in the middle of a
save operation. Then he is getting the usual "file exists.. overwrite?"
prompt, which should be suppressed with the
Application.DisplayAlerts=False
statement, but isn't being for him.

The first prompt should only be coming up if he's trying to close the
workbook.

His response below is asking for any other way to suppress those messages,
and right this minute I cannot think of one.

 

[Guest]

Brian,
Right now I'm at a loss. I've never had the .DisplayAlerts=False not work
for me, all the way from Excel 2003 back to Excel 97.

Let me ask a few basic questions and see if maybe an answer to one of them
doesn't ring a bell or turn on a light:
Which version of Excel are you using?
Have you tried using another machine to process the file?
Where within the workbook is the code located (general code module,
worksheet code, workbook code, user form code)?

My own answers to those are: 2003, no [no need], general purpose code module.
Also, I have Windows configured to use the Classic view and have things like
the Clipit office helper and balloons turned off.

 

[Guest]

Hi

Brian,
Right now I'm at a loss. I've never had the .DisplayAlerts=False not work
for me, all the way from Excel 2003 back to Excel 97.

Let me ask a few basic questions and see if maybe an answer to one of them
doesn't ring a bell or turn on a light:
Which version of Excel are you using? Excel 2000
Have you tried using another machine to process the file? No - but I will
Where within the workbook is the code located (general code module,
worksheet code, workbook code, user form code)? ThisWorkbook

My own answers to those are: 2003, no [no need], general purpose code module.
Also, I have Windows configured to use the Classic view and have things like
the Clipit office helper and balloons turned off.

I always run in classic view. All the pop up helpers are turned off.


Could it be to do with the SAve As command as opposed to Save ? If this is
the case then the ability to SAve only the worksheet does not appear to be
available.

Also, as an alternative, would it be possible to export the data somehow as
a workaround ?

 

[Guest]

Can you send me 2 files? a .xls workbook with sample page of data to be
exported and a .txt (or .mea) file with that data in the way you want it to
look in text format? If the information is confidential, dummy data of
similar format, length, content would do fine. HelpFrom @ jlathamsite.com
(remove spaces) is a good email address for that.

I presume since you've said that the output is set up for 80 character
row/record widths that it is either in a single column, or each column of
info in a row probably needs to start/end in specific locations within those
80 characters? If so, I'd need a description of those field start/length
values. I already have a general purpose text file creator to do that type
of thing - with that information I should be able to create a custom macro
for you to handle the task.

Still frustrates me that I can get this to work here on my system but you
cannot.

Hi

Brian,
Right now I'm at a loss. I've never had the .DisplayAlerts=False not work
for me, all the way from Excel 2003 back to Excel 97.

Let me ask a few basic questions and see if maybe an answer to one of them
doesn't ring a bell or turn on a light:
Which version of Excel are you using? Excel 2000
Have you tried using another machine to process the file? No - but I will
Where within the workbook is the code located (general code module,
worksheet code, workbook code, user form code)? ThisWorkbook

My own answers to those are: 2003, no [no need], general purpose code module.
Also, I have Windows configured to use the Classic view and have things like
the Clipit office helper and balloons turned off.

I always run in classic view. All the pop up helpers are turned off.


Could it be to do with the SAve As command as opposed to Save ? If this is
the case then the ability to SAve only the worksheet does not appear to be
available.

Also, as an alternative, would it be possible to export the data somehow as
a workaround ?

 

[Gord Dibben]

Brian

Sub Make_New_File()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set w = ActiveSheet
w.Copy
ActiveWorkbook.SaveAs Filename:="c:\ptp\results.mea", FileFormat:=xlText

ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP

 

[Guest]

Gord,
Seems to be another case of us not having quite the full picture.
Besides the workbook in question with the sheet to be exported, there is
another workbook that must be opened before it is that is in the picture.
I'm not certain of the relationship between the two just yet. I believe that
he fact that two workbooks were involved is the cause of the failure of the
..DisplayAlerts = False to have the desired or expected result.

The worksheet in question actually concatenates values from 2 other sheets
into the single row entries as they need to be in the text format .mea file,
so it was a very easy task just to put together a short routine using trusty
old
Open "c:\PTP\results.mea" for Output as #...
and loop through the used cells in column A, writing them one at a time.
Less than 20 rows to write - happens almost instantly.

 

[Guest]

I went ahead and examined this a little deeper. It's not so much the other
workbook that is affecting things - it is the fact that his code to SaveAs
and change file type to xlText is within the workbook's _Open() event
handler. I've never tried that in the Open() event. But stepping through it
in debug mode causes the line of code to generate an error. He's already
done a save of the workbook within the code prior to attempting to save as
text type, so it appears that the attempt to change the format of the output
file within the _Open() event causes the problem.

From looking at things and the discussion he provided in this thread, the
intent was to make this as painless and foolproof as possible for the end
user - pretty much "open Excel, open file1.xls, open file2.xls" and go home.
Because at the end of the _Open() event, it even closes Excel.

 

[Guest]

Hello Gentlemen

Many thanks to JL as I now have a solution !!! your help is appreciated.


Brian