Happy New Year everybody here on the Forum! At the beginning of this year I thought sharing a couple of questions which bother me more or less for a long time, but weren’t big enough to ask here.
1. Working with dates
When I type 7-1 today, this is recognized by Excel as 7-1-2018 (our way of displaying). In December I try to create some year planners for the new year. I always have to type the complete date, because at that moment 7-1 will be seen as 7-1-2017. Of course you can change the year after with search and replace and so, but I was wondering if there is another, easier way? (changing the date of the computer for a while isn’t also very good, so skip that answer).
2. Print settings
Excel, far more better than Word ‘remembers’ all kind of print settings, when you make and save your work on one computer with the standard printer. For instance colour settings, paper size etc.
But at an office, where I can choose between a copier where A3 format can be printed and a standard copier which can’t, I always have to change the printer first, although I made the file with that printer settings on A3.
Why can’t it be recognized as it does with the other files?
3. Filename without .xlsx
I very often use the page footer and place the filename somewhere there. Always this filename is displayed with the .xls or .xlsx extension. Most of the time this is too much, but when saving the file as .pdf this is extra stupid. Is there a possibility not to show the file extension (with an automatic file name of course)?
I have some more of these practical head breakers when working with Excel, but thought this would be enough for a good start. I’m very interested in your solutions or remarks!
All the best this year for everybody!
Frans
Hi Frans
I can only help with Q3. You can try changing the windows setting to hide the extension for known file types.
Hope this helps.
Sunny
Try this for Q1
It involves a macro
Sunny
Hi Frans
For Q2 I believe the reason is because it is not practical to save the printer's name. If the file is only used at your office then maybe it is ok to save the printer's name to each file. If you need to distribute the file to others that don't use your printer then it is just not practical.
You can give this code a try. It uses the Workbook BeforePrint event for a workbook. You will need to set it up for every one of your workbook.
Just change the printer's name to suit your needs.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.ActivePrinter = "KONICA MINOLTA 283SeriesPCL on Ne02:"
End Sub
Hope this helps.
Sunny
Hi Sunny. Amazing again what you do. First you said you only could help on Q3 and after that you come with solutions for both the other two questions as well! Thanks!
To start with Q3: never thought about that. I'll give that a try, but I think the disadvantage (not seeing the file extensions in the Explorer) is bigger than the advantage. But maybe toggle it on and off wil help enough sometimes.
I will examine the macro as well. I'm not so into macro's (taking a course at the friends of Excel Campus, but only advancing very slowly). But maybe for this kind of special occasions it will work for me also. Thanks anyway for your help and time in this.
The same for the code for Workbook before print. Am I right this is a macro as well, or do I have to place the code somewhere else? I think I embed it in the sheets where more people want to print it to the same different (A3) printer. Here also not sure if this is 'the' solution, but you found one and that is encouraging enough.
So thanks again. This marks a good start of the new year: three of my old questions solved!
Frans
Hi Frans
Q3 - You can right-click the folder's header and add a column TYPE to view the file extension.
I couldn't find any non-VBA solution to Q1, other than those you have quoted. I will normally only use VBA as a last resort.
For Q2, the codes must be stored in the workbook module.
Glad to know you are taking up VBA. It will be worth it.
Good luck.
Sunny
Hello Frans,
Q1: As a Swede my regional setting for a date is YYYY-MM-DD, so when I type in 16/8/22 or 16-8-22 in a cell that has number format Date I automatically get 2016-08-22. If I want a date within this year I only type in 8/22 or 8-22 to get 2018-08-22. Often this works fine even with cells having General as number format, but for some reason not always, at least what I have experienced.
Br,
Anders Sehlstedt
