Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
I have a User Form with a Control Button for assigning the name & saving the
Workbook. The Problem is when the Save As Dialog Box pops up the File name is assigned, but you can not save as anything other than "All Files". The different Excel file types are not even there to pick from. At that point user hits the canel to close without saving, but it saves the file anyway as a file named "FALSE.xlsm". What is supposed to happen is the user clicks on the Control Button and the Save As Dialog Box pops up with the File Name already assigned. Then the user can go to the directory they want to save the Workbook in and save it. If not the User Hits Cancel and the Save Dialog Box close's and the Message "The Save Method Failed". Here is my code. What did I do wrong? Where is it getting the name "FALSE" form? ' Save Eng Spec 11 Control Button Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook Set bk = ActiveWorkbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) If FileToSave < False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
I don't like combining instructions together like the one below bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) If make debugging harder and makes it harder to understand. In this case it didn't give you the results you wanted. I split this instruction up into pieces below and also added "*.xls" to the strFile so you can see all the files. ' Save Eng Spec 11 Control Button Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook Set bk = ActiveWorkbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value _ & "*.xls" FName = Application.GetSaveAsFilename(strFile) If FileToSave < False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If bk.SaveAs Filename:=FName End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
It's doing the same thing as before, only now there is no preassigned file
name. It is still saving the workbook as "False' when you try to canel it. "joel" wrote: I don't like combining instructions together like the one below bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) If make debugging harder and makes it harder to understand. In this case it didn't give you the results you wanted. I split this instruction up into pieces below and also added "*.xls" to the strFile so you can see all the files. ' Save Eng Spec 11 Control Button Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook Set bk = ActiveWorkbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value _ & "*.xls" FName = Application.GetSaveAsFilename(strFile) If FileToSave < False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If bk.SaveAs Filename:=FName End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
Brian, when you get some time, take a look through Chips page on debugging
VBA code. It could help you to resolve some of your problems on your own. "Brian" wrote in message ... It's doing the same thing as before, only now there is no preassigned file name. It is still saving the workbook as "False' when you try to canel it. "joel" wrote: I don't like combining instructions together like the one below bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) If make debugging harder and makes it harder to understand. In this case it didn't give you the results you wanted. I split this instruction up into pieces below and also added "*.xls" to the strFile so you can see all the files. ' Save Eng Spec 11 Control Button Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook Set bk = ActiveWorkbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value _ & "*.xls" FName = Application.GetSaveAsFilename(strFile) If FileToSave < False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If bk.SaveAs Filename:=FName End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
the sign was wrong From If FileToSave < False Then to If FileToSave = False Then -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
Sorry it took so long to respond back. i was reading everything I could on
Saving Workbooks, sheets, etc... One thing I noticed was there was no mention of anything close to what I am trying to do, so there for there are no answers. I am really curious though as to why when you click on the cancel button it saved a file named called "FALSE". Where did it get the name FALSE from? "joel" wrote: the sign was wrong From If FileToSave < False Then to If FileToSave = False Then -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
Application.GetSaveAsFilename returns False when you hit cancel and returns a filename when you select a file. Nothing prevents a file called FALSE to be created in windows. when you use Application.GetSaveAsFilename you don't want to select a file False because the VBA code can't tell the difference. Your original code contained the following bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) So the FALSE returned when a Cancel was selected became the filename. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
This works like it is supposed to, except for when you go to save it. The
Save As Dialog Box does not let you select what file type under save as (XLS,XLSM,XLST, etc....). The only choice is "All Files". What did I do wrong? I have been adjusting this for 2 days now and this is the best I can get it. I need to be able to select the save as file Type. ' Save Engineering Spec 11 Control Button Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value strFile = Application.GetSaveAsFilename(strFile) If FileToSave = False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If End Sub "joel" wrote: Application.GetSaveAsFilename returns False when you hit cancel and returns a filename when you select a file. Nothing prevents a file called FALSE to be created in windows. when you use Application.GetSaveAsFilename you don't want to select a file False because the VBA code can't tell the difference. Your original code contained the following bk.SaveAs Filename:=Application.GetSaveAsFilename(strFile) So the FALSE returned when a Cancel was selected became the filename. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value strFile = Application.GetSaveAsFilename( _ InitialFileName:=strFile, _ fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _ "*.xls;*.xlsm;*.xlst") If FileToSave = False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
ABSOLUTLY PERFECT!!!!!!!!
Thank you so much for sticking with me on this problem. I am learning alot about how and why it works this way. I have a question about progress bars if your interested. But again, Thank you so much for all your help!! "joel" wrote: Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value strFile = Application.GetSaveAsFilename( _ InitialFileName:=strFile, _ fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _ "*.xls;*.xlsm;*.xlst") If FileToSave = False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving a Workbook cause's Error's
Opp's I spoke to soon it worked all but nothing saved.
"joel" wrote: Private Sub Save_Eng_Spec_11_Click() Dim strFile As String Dim bk As Workbook strFile = "SPEC " & TEO_No_1.Value _ & Space(1) & CLLI_Code_1.Value _ & Space(1) & CES_No_1.Value _ & Space(1) & TEO_Appx_No_2.Value strFile = Application.GetSaveAsFilename( _ InitialFileName:=strFile, _ fileFilter:="Excel Files (*.xls;*.xlsm;*.xlst), " & _ "*.xls;*.xlsm;*.xlst") If FileToSave = False Then MsgBox "The Save Method Failed, Eng Spec was not Saved", , "C.E.S." Exit Sub End If End Sub -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165872 Microsoft Office Help . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
saving 3 worksheets in a workbook to a separte workbook | Excel Programming | |||
Color Changes When Saving 2007 Workbook as 97 - 2003 Workbook | Excel Discussion (Misc queries) | |||
Saving a sheet in a workbook as .csv but not changing workbook name | Excel Programming | |||
Saving a Workbook: Forcing User to Rename before Saving | Excel Programming | |||
Saving workbook from vba | Excel Programming |