Excel 2007 and VBA

[Jen]

Hi there, I have a button on a spreadsheet that saves a copy of the
spreadsheet to the user's temp file, then emails this copy. This worked fine
in Excel 2003 and still does. However if I save the template in Excel 2007,
the mailing works, but when the other person receives the email they get a
warning message:

The file you are trying to open, 'file name', is in a different format than
specified by the file extension. Verify that the file is not corrupted and is
from a trusted source before opening the file. Do you want to open the file
now?

Here is the code that is creating the file that is emailed:

TempFilePath = Environ$("temp") & "\"

If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 52
End If

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb1 = ActiveWorkbook
TempFileName = ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy
h-mm")

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)


Does anyone know how to stop this in 2007?

Thank you,
Jen

 

[Ron de Bruin]

You use the wrong FileFormatNum

xlsx = 51

 

[Jon Peltier]

Looks like you're putting the timestamp after the file extension:

TempFileName = ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm")

MyFile.XLS becomes MyFile.XLS 22-Jan-09 15-50

Try something like this:

TempFileName = Left$(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") -
1)
TempFileName = TempFileName & " " & Format(Now, "dd-mmm-yy h-mm")
TempFileName = TempFileName & Mid$(ThisWorkbook.Name,
InStrRev(ThisWorkbook.Name, ".") )

Now the file extension will not change. MyFile.XLS becomes MyFile 22-Jan-09
15-50.XLS

- Jon

 

[Jen]

Hi there. Thank you both for the replies. Neither of them worked though.

I am not getting MyFile.xls 22-01-08 15-13.xls, I get MyFile 22-01-08
15-13.xls.

It works just fine if this is in a 2003 format, just not 2007.

Any other suggestions?

Thank you,
Jen

 

[Ron de Bruin]

Have you read my reply ???

 

[Jen]

Hi Ron,

Yes, I changed the version number, but I still receive the warning.

Thank you,
Jen

 

[Ron de Bruin]

I see now that you use SaveCopyAs

what do you want to mail exactly ?

 

[Jen]

Hi Ron,

This is a template in our office. The user opens the template, fills in the
information needed, and then the user clicks the 'email' button and the
filled in worksheet is emailed. So I create a copy of the workbook, save the
copy, email the copy and then delete the copy. Everything works great until
we went to Office 2007. Now when I update the template to an Office 2007
template, everything still works, but when the end user opens the emailed
attachment, they receive the format warning.

If I keep the template as an Office 2003 template, then there is no warning.

Thank you,
Jen

 

[Ron de Bruin]

Is it a real template or a saved workbook that you call a template ?

 

[Jen]

It is a real template. When saving as a template in 2003 it is saved as the
excel template with extension of .xlt. In 2007 I save it as a macro enabled
template - .xltm.

 

[Ron de Bruin]

SaveCopyAs can only save in the same format so you must use other code

Use SaveAS instead and use this

FileExtStr = ".xlsx": FileFormatNum = 51

Or

FileExtStr = ".xlsm": FileFormatNum = 52

 

[Jen]

Thank you Ron, I really appreciate your help.

 

[Carim]

Thank you Ron, I really appreciate your help.  








- Show quoted text -

Thanks Ron ... too ... since I had the exact same problem ...

But Excel 2007 is a true nightmare ...
just discovered Application.FileSearch has totally disappeared ...
What a bunch of smart people in Redmond ... !!!