WinkerWatson
04-02-2013, 02:39 AM
Hi there
This is a bit of a weird one, but hopefully someone will be able to help.
I receive emails from a client who, for reasons best known to themselves, occasionally sends the file in xlsx format, but with an xls file suffix.
This is pretty irritating as this obviously confuses things when I try to import into Access to work on the data. I have a nice macro all set up to save the attachment to the right place, but it falls over when I try to import and it's a xlsx file with an xls suffix.
The only solution I have at present is to try just opening the file and see if it comes out as garbage. If it does, I rename with xlsx file suffix, then save as xls format (I use Office 2002).
Other than coming up with some way of emailing a slap to the sender, it seems to me the best route would be to set up a vba macro to try opening the file, then if it works close it again, or if it doesn't, proceed with a bit of code to rename it as xlsx and then open it again and finally save as xls (which is what I ultimately want the file format to be).
Opening it is easy enough - lots of example around (can't post one as I don't have enough posts yet ).
The problem with Excel automation is that I can't control the error trapping - it opens Excel and I get the error there, rather than in the Outlook function, so I can't just do some 'if error then do this' type code to handle things.
Is there a way to open an Excel sheet without automation, or can anyone come up with a better scheme altogether? Needless to say trying to educate the client is not working - been trying to do it for a while and they have multiple people who send the file, so it's a losing battle.
Thanks!
This is a bit of a weird one, but hopefully someone will be able to help.
I receive emails from a client who, for reasons best known to themselves, occasionally sends the file in xlsx format, but with an xls file suffix.
This is pretty irritating as this obviously confuses things when I try to import into Access to work on the data. I have a nice macro all set up to save the attachment to the right place, but it falls over when I try to import and it's a xlsx file with an xls suffix.
The only solution I have at present is to try just opening the file and see if it comes out as garbage. If it does, I rename with xlsx file suffix, then save as xls format (I use Office 2002).
Other than coming up with some way of emailing a slap to the sender, it seems to me the best route would be to set up a vba macro to try opening the file, then if it works close it again, or if it doesn't, proceed with a bit of code to rename it as xlsx and then open it again and finally save as xls (which is what I ultimately want the file format to be).
Opening it is easy enough - lots of example around (can't post one as I don't have enough posts yet ).
The problem with Excel automation is that I can't control the error trapping - it opens Excel and I get the error there, rather than in the Outlook function, so I can't just do some 'if error then do this' type code to handle things.
Is there a way to open an Excel sheet without automation, or can anyone come up with a better scheme altogether? Needless to say trying to educate the client is not working - been trying to do it for a while and they have multiple people who send the file, so it's a losing battle.
Thanks!