My cow-orker generally hasn’t really worked with comma delimited files, but she has to now. She just asked me about saving them. This is one of the many annoying things I experience with Excel. (I’d tell you the others, but I don’t have the time. )
You want to save a file as comma delimited, so you click Save as and choose Comma delimited.
‘This file already exists. Do you want to replace it?’
Yes.
The selected file type does not support multiple sheets. Do you want to save it anyway?’
Yes.
‘Oh, I forgot to tell you. Your file might (or might not) contain features that are not compatible with CSV. You, ah… Still want to save it?’
Yes.
Whew! That’s a lot of steps to go through just to save a file! Well, it’s done. I’ll just close Excel…
‘Do you want to save this?’
GAAAAHHHHH! :mad:
I’m used to this very silly thing. But now that the cow-orker has asked about it, is there any way to make saving a .csv file less silly? Something like, ‘Do you want to replace the existing file?’ Yes. Close. Done.
There may be an easier way, but you could create a workbook that gets loaded automatically whenever Excel opens. In that workbook, you could add a module and create this subroutine:
Option Explicit
Option Base 1
Sub MySaveAsAndClose()
Dim wb As Workbook
Set wb = ActiveWorkbook()
If MsgBox("Do you want to overwrite " & wb.Name & " assuming it exists?", _
vbOKCancel) = vbOK Then
Application.DisplayAlerts = False
Call wb.SaveAs(wb.Name, xlCSV)
wb.Close
Application.DisplayAlerts = True
End If
End Sub
Then, you could assign that macro to a button.
So, whenever Excel opens, it would auto-open the workbook with that macro. The button would stay assigned. Then, when the user is ready to save and close the CSV, press the button and voila! The code above has been really minimally tested, and it doesn’t check to see if the file actually exists first.
You could hide the main window of the auto-open workbook. I would have to dig up where to place the auto-open workbook so that it opens every time Excel does – it varies depending on which version of Excel and which operating system.
I just ran into this issue today. Really frustrating. Microsoft sometimes treats you like you are a six year old child… I’ll mention it to Bill the next time I see him.
Part of it is your own fault – if you are only using 1 sheet, why do you have multiple sheets defined? If you didn’t have those wasteful extra sheets there, you wouldn’t get the 2nd question. And you wouldn’t get the first question if you used a new name for a new file.
Also, you can short cut that process when you exit Excel – just say No, you don’t want to save, Hardly neccessary, you just saved the file.
There are lots of annoyances in Excel, but these seem minor to me. And I’ll gladly put up with a couple seconds more for them, just for the rare occasions where this confirmation will save me from losing a file after putting a whole lot of work into it.
I have Excel set up so that when I am making a new file, there is only one worksheet. (Also, font is 9 pt. Courier New.) The problem is that we get data in every imaginable way, and most companies send multiple sheets. Sometimes they need them, most times they don’t. Let’s say they send a file that has a sheet with the business names and addresses, and another sheet with their aging information. I need to make two files (a Customer file and an Aging file). It would be silly to open the file, delete one (or more) sheets, save it, and then open it again and repeat. It’s easier to save one sheet and then save the other without deleting anything. But if they do weird things with the data (e.g., pivot table or even just formulas) I’ll open a new book (which only has one sheet) and copy and paste-as-text the data into it.
That would result in thousands of files sitting in my folder that never get used again. Also, the files need specific names that don’t change, because my programs are looking for those names. I don’t mind the ‘Do you want to replace this file?’ question. If I mis-type the name, then I don’t get the alert and I know I need to check my spelling.
Yes, I click No. It’s just annoying that it asks if I want to save a file after I’ve just saved it.
As I said, I don’t mind the first question. Very rarely, it does keep me from overwriting something, and occasionally it alerts me to a typo. It’s the other questions that I think are silly and annoying.
As pointed out, you get the same kind of shit from Word with a TXT file. It’s extra steps to make sure you remember you are not saving an Excel/Word file. Honestly, it has saved me from time to time when I’ve imported a CSV, done a shitload of sorting, filtering, and other kinds of manipulations, and sort of half-forgot I wasn’t working with an XLS file.
“Features not compatible with … ooooooooooooh. Crap, yes, let’s rethink this and save it as XLS.”
Yes, I know it’s XLSX now.
A problem I have with Excel and CSV files is kind of the reverse. When I open a CSV file with Excel, Excel is sometimes too smart for it’s own good, and converts numerical text values to number format. I have large files where the Employee ID has leading zeroes (e.g. “003123456”), and Excel will strip those every time. Then I have to modify them to add the zeroes back in and convert it to text.
That’s another annoying thing. (Every so often I think I should start a thread about all of the annoying things Excel and Access do.) Microsoft must think its users are idiots, so Excel knows what the users really mean. It gets rid of leading zeros, which causes an extra step when account numbers or ZIP codes have them. If an account number is, say MAR99, it changes it to the date Mar-99. If you define it as text, it turns it into a 5-digit date code. If it’s something like 4E1234, it says ‘Obviously, this is scientific notation!’ If a phone number is 0000000000, it makes it 0. I don’t understand why they can’t default to opening a file with all cells being text. If the user wants to change an account number to a date, he can do it! At least there should be a box you can click that tells it to do that. I hate that it changes formats before you have a chance to tell it not to. (And I think Access should have a checkbox that tells it to import all fields as text without having to define columns individually on import.) I’ve started adding code to my Easytrieves that say 'If OUT-PHONE = [blank] or ‘0’, OUT-PHONE = ‘0000000000’. Fortunately, people who send us .csv files with all-numeric account numbers have always sent .csv files, so I don’t have to worry about leading zeros. I routinely format the ZIP codes as ZIP codes at the very beginning. I have to watch out for, say, Australian or some European postal codes, which don’t have leading zeros; and some of the ‘plus-4’ ZIPs don’t have dashes (nor do we want them), so I have to go back and format those as ‘000000000’, or format them as Text and add the leading zeros for Northeastern addresses.
From the answers so far, it sounds like we just have to put up with the condescension. I understand that the programmers have no way of knowing that a ZIP code field is a ZIP code field that needs leading zeros, and I understand that it’s impossible to write the program to say ‘If the cell format = ZIP codes AND the number of numeric characters is greater than 5 and less than 9, add leading zeros’. (Oh, wait… I can do that in Easytrieve.) But it would be better (for me) if the program did not assume it knows more than I do.
When importing text or CSV, isn’t there an option to set the number style of columns? I’m talking about if you copy the data and click the small arrow under paste, then “Text Import Wizard”. Or I think under Data, import text or something (don’t have Excel now to check).
If you’re copying the data, then you can ‘Paste special’ or choose the button that tells it to paste as text. For the stuff I work on, I just open the file that is provided in the email instead of saving it and then importing it.
If you go through the Open dialog box and select an CSV or other text file, it automatically spawns the Text Import wizard. At least mine does.
You can select at step 3 the column data format: General, Text, Date (with options), or Do Not Import. If you select General, you can choose under “Advanced” to modify the comma and decimal place configurations.
Excel 2007. I recognize that a lot may have changed.
Well, my solution will actually work, I think. If you can find someone with some knowledge of Excel programming, it may not be that hard for that person to figure out, if you’re not familiar with that side of Excel.
Sadly, I’m the one with the most knowledge of Excel in the office. My boss and coworker think I’m some sort of genius because I showed them how to get leading zeros on ZIP codes, and because I do magical things like using =CONCATENATE. But I’ve given up trying to make VLOOKUP work (I’ve made it work in one file, and then using the exact same code (with proper columns, of course) it fails in another file), and anything involving macros or Excel programming is beyond me. A large part of my job is to put Excel and other files into specific, fixed-position, text files. It’s easier for me to write an Easytrieve to do what needs to be done. (Easytrieve is a sequential program from, like, the '60s or '70s that bears a passing resemblance to COBOL or BASIC.)
So when I come upon something I need to do that I don’t know how to do. I turn to you guys. If it involves macros or Excel programming, I think I’ll just have to live with clicking 27 times to save and close a .csv file.
)