|
Excel 2007 and VBA
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 |
Excel 2007 and VBA
You use the wrong FileFormatNum
xlsx = 51 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 |
Excel 2007 and VBA
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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
Have you read my reply ???
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
Hi Ron,
Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
I see now that you use SaveCopyAs
what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
Is it a real template or a saved workbook that you call a template ?
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
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" wrote: Is it a real template or a saved workbook that you call a template ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: Is it a real template or a saved workbook that you call a template ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
Thank you Ron, I really appreciate your help. :)
"Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: Is it a real template or a saved workbook that you call a template ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... Hi Ron, Yes, I changed the version number, but I still receive the warning. Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in message ... 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 "Jon Peltier" wrote: 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 ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 |
Excel 2007 and VBA
On Jan 26, 5:55*pm, Jen wrote:
Thank you Ron, I really appreciate your help. *:) "Ron de Bruin" wrote: 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in ... It is a real template. *When saving as a template in 2003 it is saved as the exceltemplate with extension of .xlt. *In2007I save it as a macro enabled template - .xltm. "Ron de Bruin" wrote: Is it a real template or a saved workbook that you call a template ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in ... 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 Office2007. *Now when I update the template to an Office2007 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" wrote: I see now that you use SaveCopyAs what do you want to mail exactly ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in ... Hi Ron, Yes, I changed the version number, but I still receive the warning. * Thank you, Jen "Ron de Bruin" wrote: Have you read my reply ??? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Jen" wrote in ... 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 not2007.. Any other suggestions? Thank you, Jen "Jon Peltier" wrote: 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 ------- Jon Peltier, MicrosoftExcelMVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ "Jen" wrote in message ... 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 inExcel2003 and still does. *However if I save the template inExcel 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 useExcel97-2003 * * * *FileExtStr = ".xls": FileFormatNum = -4143 * *Else * * * *'You useExcel2007 * * * *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 in2007? Thank you, Jen- Hide quoted text - - 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 ... !!! |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com