Converting xlsx to xl

[Patel]

Good morning,

I'm hoping someone has already ran into this problem, and I'm sure it
will continue until more businesses switch over to 2007 excel xlsx.
Here's what I'm trying to do: I have a file that that I'm taking some
information from, copying it, starting a new workbook, pasting the
information, and then generating an e-mail. The problem is that even
if my original workbook was saved as a 97-2003 excel workbook, now
that I have 2007 installed, any new workbook automatically becomes an
xlsx. I would like to make this code still work but without having the
user have to do a "save as", change the file type, etc.

Any help would be appreciated.

Sheets("Log").Select
Range("A1").Select
ActiveSheet.UsedRange.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteColumnWidths,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Range("A1").Select
Application.Dialogs(xlDialogSendMail).Show



Thank you,
Stephen

 

[sbitaxi]

Hello,

Visit this link, it may help for setting the default save format.

http://www.howtogeek.com/howto/microsoft-office/make-excel-2007-always-save-in-excel-2003-format/


Regards,

Steven

 

[Orion Cochrane]

Hi.
You can change the options by clicking on the Office Logo button (top-left
corner of screen). Go to Excel Options (located on the bottom-right corner of
the menu before Exit Excel). Click the Save option and select the Excel
97-2003 Workbook (*.xls) option in the Save As Type drop-down.

 

[Patel]

Thanks for the posts. That is a helpful thing to know, but I'm still
wondering if I can do it through visual basic instead of changing the
settings. I am creating this for others to use, so if that's the only
way to change it, then I will have to have all of them change their
excel options. Is there any way to do this through code for just this
macro?

Thanks again,
Stephen

 

[sbitaxi]

Thanks for the posts. That is a helpful thing to know, but I'm still
wondering if I can do it through visual basic instead of changing the
settings. I am creating this for others to use, so if that's the only
way to change it, then I will have to have all of them change their
excel options. Is there any way to do this through code for just this
macro?

Thanks again,
Stephen

Hi Stephen,

You can try toying with this snippet I got a while back from Ron De
Bruin

'Determine the Excel version and file extension/format
If Val(Application.VERSION) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
If SrcBk.Parent.FileFormat = 56 Then
FileExtStr = ".xls": FileFormatNum = 56
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If


File format is the ID number of the extension string.


S