 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2006SAveAs 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 Application.DisplayAlerts = False ...your save as code here Application.DisplayAlerts = True > 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 [quoted text clipped - 12 lines] > > Brian Hi JL Unfortunately I had already tried this but with no success. Any other suggestions ??? thx > Application.DisplayAlerts = False > ...your save as code here [quoted text clipped - 16 lines] > > > > Brian 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!!! > Hi JL > [quoted text clipped - 21 lines] > > > > > > Brian 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. > 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 [quoted text clipped - 25 lines] > > > > > > > > Brian 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. > Hi JL > [quoted text clipped - 21 lines] > > > > > > Brian 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 ?!?! > Brian, > No, it works for me and I've tried it while just using the filename without [quoted text clipped - 39 lines] > > > > > > > > Brian 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. > Hi > [quoted text clipped - 49 lines] > > > > > > > > > > Brian Hi > Brian, > Right now I'm at a loss. I've never had the .DisplayAlerts=False not work [quoted text clipped - 10 lines] > 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 ? > > Hi > > [quoted text clipped - 49 lines] > > > > > > > > > > > > Brian 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 > [quoted text clipped - 74 lines] > > > > > > > > > > > > > > Brian 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 >Hi > [quoted text clipped - 49 lines] >> > > > >> > > > Brian 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. > Brian > [quoted text clipped - 68 lines] > >> > > > > >> > > > Brian 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. > Brian > [quoted text clipped - 68 lines] > >> > > > > >> > > > Brian 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 > Brian, > No, it works for me and I've tried it while just using the filename without [quoted text clipped - 39 lines] > > > > > > > > Brian Hello Gentlemen Many thanks to JL as I now have a solution !!! your help is appreciated. Brian > Hello JL > [quoted text clipped - 51 lines] > > > > > > > > > > Brian |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.