Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFilename X & ActiveWorkbook.SaveAs X
Thank you to the kind and intelligent for any help/suggestion.
When you do a Save &As... you get about six option to pick from. 1 Excel Workbook (2007) 2 Excel Macro-enabled Workbook (2007) 3 Excel Binary Workbook (2007) 4 Excel 97-2003 workbook 5 Find add-ins for other file formats 6 other formats When I use the GetSaveAsFilename to retrieve file name & location. Problem: 1) The Save as type: only as All Files (*.*). When you use SaveAs normal you have a wide selection to pick from and when you pick one of the selections it changes the file name accordingly. So, basically I want to make the GetSaveAsFilename to have the functionality of a the SaveAs file type list. 2) With the following code if you save a file name Test Book.xls. Using the saveas in 2007 format but you will see the name as Test Book.xls, so I changed it to Test Book3.xlsx. When you try to open the file via a double click from Explore you will get the follow msg: Excel cannot open the file 'Test Book3.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. My conclustion is that when I manally changed the extension the file didn't upgrade to the desired format. Another differant from the normal SaveAs. I have my code in VBAProject (PERSONAL.XLS) My code: Option Explicit Dim MyFullName Dim bInternalSave Dim closing Public WithEvents xl As Excel.Application Private Sub Class_Terminate() Set xl = Nothing End Sub Private Sub Class_Initialize() On Error Resume Next Set xl = Excel.Application shCount = 0 Exit Sub Resume Next End Sub Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RC Dim NewFullName As String RC = "update" If closing = ActiveWorkbook.Name Then If ActiveWorkbook.saved = True Then Else testFooter (RC) ActiveWorkbook.saved = True End If ActiveWorkbook.Save Else If bInternalSave Then bInternalSave = False: Exit Sub Cancel = True '-- cancel original save MyFullName = ActiveWorkbook.FullName If SaveAsUI Then NewFullName = Application.GetSaveAsFilename(MyFullName) If NewFullName < "False" Then bInternalSave = True On Error Resume Next '-- user may cancel on SaveAs testFooter (RC) ActiveWorkbook.SaveAs NewFullName '-- so causes this line fails If Err = 0 Then Else bInternalSave = False End If End If Else bInternalSave = True testFooter (RC) ActiveWorkbook.Save End If End If closing = "" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFilename X & ActiveWorkbook.SaveAs X
See example (Copy_ActiveSheet_2)
http://www.rondebruin.nl/saveas.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Still_learning" wrote in message ... Thank you to the kind and intelligent for any help/suggestion. When you do a Save &As... you get about six option to pick from. 1 Excel Workbook (2007) 2 Excel Macro-enabled Workbook (2007) 3 Excel Binary Workbook (2007) 4 Excel 97-2003 workbook 5 Find add-ins for other file formats 6 other formats When I use the GetSaveAsFilename to retrieve file name & location. Problem: 1) The Save as type: only as All Files (*.*). When you use SaveAs normal you have a wide selection to pick from and when you pick one of the selections it changes the file name accordingly. So, basically I want to make the GetSaveAsFilename to have the functionality of a the SaveAs file type list. 2) With the following code if you save a file name Test Book.xls. Using the saveas in 2007 format but you will see the name as Test Book.xls, so I changed it to Test Book3.xlsx. When you try to open the file via a double click from Explore you will get the follow msg: Excel cannot open the file 'Test Book3.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. My conclustion is that when I manally changed the extension the file didn't upgrade to the desired format. Another differant from the normal SaveAs. I have my code in VBAProject (PERSONAL.XLS) My code: Option Explicit Dim MyFullName Dim bInternalSave Dim closing Public WithEvents xl As Excel.Application Private Sub Class_Terminate() Set xl = Nothing End Sub Private Sub Class_Initialize() On Error Resume Next Set xl = Excel.Application shCount = 0 Exit Sub Resume Next End Sub Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RC Dim NewFullName As String RC = "update" If closing = ActiveWorkbook.Name Then If ActiveWorkbook.saved = True Then Else testFooter (RC) ActiveWorkbook.saved = True End If ActiveWorkbook.Save Else If bInternalSave Then bInternalSave = False: Exit Sub Cancel = True '-- cancel original save MyFullName = ActiveWorkbook.FullName If SaveAsUI Then NewFullName = Application.GetSaveAsFilename(MyFullName) If NewFullName < "False" Then bInternalSave = True On Error Resume Next '-- user may cancel on SaveAs testFooter (RC) ActiveWorkbook.SaveAs NewFullName '-- so causes this line fails If Err = 0 Then Else bInternalSave = False End If End If Else bInternalSave = True testFooter (RC) ActiveWorkbook.Save End If End If closing = "" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GetSaveAsFilename X & ActiveWorkbook.SaveAs X
Thank you with some trial and error I believe my macro is finished with the
exception of completion of testing upon many users. Thank you Ron de Bruin "Ron de Bruin" wrote: See example (Copy_ActiveSheet_2) http://www.rondebruin.nl/saveas.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Still_learning" wrote in message ... Thank you to the kind and intelligent for any help/suggestion. When you do a Save &As... you get about six option to pick from. 1 Excel Workbook (2007) 2 Excel Macro-enabled Workbook (2007) 3 Excel Binary Workbook (2007) 4 Excel 97-2003 workbook 5 Find add-ins for other file formats 6 other formats When I use the GetSaveAsFilename to retrieve file name & location. Problem: 1) The Save as type: only as All Files (*.*). When you use SaveAs normal you have a wide selection to pick from and when you pick one of the selections it changes the file name accordingly. So, basically I want to make the GetSaveAsFilename to have the functionality of a the SaveAs file type list. 2) With the following code if you save a file name Test Book.xls. Using the saveas in 2007 format but you will see the name as Test Book.xls, so I changed it to Test Book3.xlsx. When you try to open the file via a double click from Explore you will get the follow msg: Excel cannot open the file 'Test Book3.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file. My conclustion is that when I manally changed the extension the file didn't upgrade to the desired format. Another differant from the normal SaveAs. I have my code in VBAProject (PERSONAL.XLS) My code: Option Explicit Dim MyFullName Dim bInternalSave Dim closing Public WithEvents xl As Excel.Application Private Sub Class_Terminate() Set xl = Nothing End Sub Private Sub Class_Initialize() On Error Resume Next Set xl = Excel.Application shCount = 0 Exit Sub Resume Next End Sub Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim RC Dim NewFullName As String RC = "update" If closing = ActiveWorkbook.Name Then If ActiveWorkbook.saved = True Then Else testFooter (RC) ActiveWorkbook.saved = True End If ActiveWorkbook.Save Else If bInternalSave Then bInternalSave = False: Exit Sub Cancel = True '-- cancel original save MyFullName = ActiveWorkbook.FullName If SaveAsUI Then NewFullName = Application.GetSaveAsFilename(MyFullName) If NewFullName < "False" Then bInternalSave = True On Error Resume Next '-- user may cancel on SaveAs testFooter (RC) ActiveWorkbook.SaveAs NewFullName '-- so causes this line fails If Err = 0 Then Else bInternalSave = False End If End If Else bInternalSave = True testFooter (RC) ActiveWorkbook.Save End If End If closing = "" End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveWorkbook.SAVEAS for *.xls | Excel Programming | |||
saveas ActiveWorkbook.SaveAs Filename:=Range("A1").Value | Excel Programming | |||
'SaveAs Filename' versus 'GetSaveAsFilename' | Excel Programming | |||
Help with ActiveWorkbook.SaveAs | Excel Programming | |||
ActiveWorkbook.SaveAs | Excel Programming |