Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
Application.DisplayAlerts = False
....your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
Hi JL
Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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!!! "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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 "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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. "romelsb" wrote: 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!!! "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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 a 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. "Brian Young" wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
Hi
"JLatham" wrote: 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 a 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 ? "Brian Young" wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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. "Brian Young" wrote: Hi "JLatham" wrote: 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 a 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 ? "Brian Young" wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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 On Sat, 21 Oct 2006 04:57:02 -0700, Brian Young wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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. "Gord Dibben" wrote: 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 On Sat, 21 Oct 2006 04:57:02 -0700, Brian Young wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
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. "Gord Dibben" wrote: 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 On Sat, 21 Oct 2006 04:57:02 -0700, Brian Young wrote: 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 ?!?! "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SAveAs worksheet
Hello Gentlemen
Many thanks to JL as I now have a solution !!! your help is appreciated. Brian "Brian Young" wrote: 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 "JLatham" wrote: 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. "Brian Young" wrote: Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx "JLatham" wrote: Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True "Brian Young" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search one worksheet for values in another worksheet? | Excel Discussion (Misc queries) | |||
Worksheet not showing up in VBE | Excel Worksheet Functions | |||
Inserting Filtered RC cell information into other worksheets | Excel Discussion (Misc queries) | |||
Search/Match between 2 x separate Worksheets and populate result in third worksheet | Excel Discussion (Misc queries) | |||
Weekly Transaction Processing | Excel Worksheet Functions |