Saving a Workbook cause's Error's

[Brian]

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

 

[John Bundy]

Check this link out, it will show you how to use the save method, pay close
attention to where in the code you are saving.

http://www.pcreview.co.uk/forums/thread-947075.php

 

[joel]

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 thi
case it didn't give you the results you wanted. I split thi
instruction up into pieces below and also added "*.xls" to the strFil
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 Su

 

[Brian]

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.

 

[JLGWhiz]

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.

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.

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/showthread.php?t=165872

Microsoft Office Help

.

 

[joel]

the sign was wrong

From

If FileToSave <> False Then

to

If FileToSave = False The

 

[Brian]

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]

Application.GetSaveAsFilename returns False when you hit cancel an
returns a filename when you select a file. Nothing prevents a fil
called FALSE to be created in windows. when you us
Application.GetSaveAsFilename you don't want to select a file Fals
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

 

[Brian]

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]

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

 

[Brian]

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!!

 

[Brian]

Opp's I spoke to soon it worked all but nothing saved.