| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel 2007 files not showing on screen when opened in Excel 2007 | Setting up and Configuration of Excel | |||
| Conflict in excel 2007 with Outlook 2007 attachment excel | Excel Discussion (Misc queries) | |||
| Labels: Unable to import all records Excel 2007 to Word 2007 Mailm | Excel Discussion (Misc queries) | |||
| Excel 2007, I write macros in 2003 is 2007 similar for VBA? Pros andcons please | Excel Programming | |||
| Excel 2007 Macro Help (Excel 2003 not working in 2007) | Excel Discussion (Misc queries) | |||
Hybrid Mode
