Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Save As with extension (Excel 97)

    The following macro allows me to save if Yes is checked. However, the default for type of file is "All Files". The user still must enter the extension for the Excel file he wants to save as. How can I change this so the default Type of File is an Excel (XLS)?
    Sub Savefile()
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "Click Yes to save file before proceeding, No to Copy Display Area to Word without saving." ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "Do you want to Save before proceeding?" ' Define title.
    Help = "DEMO.HLP" ' Convert file?
    Ctxt = 1000 ' Define topic
    ' context.
    ' Display message.
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbYes Then
    Dim Filer As String
    Filer = Application.GetSaveAsFilename
    ActiveWorkbook.SaveAs FileName:=Filer
    ActiveSheet.Unprotect
    Application.Goto Reference:="resultsarea"
    Application.Goto Reference:="display"
    ActiveWindow.Zoom = 80
    Selection.Copy
    ' User chose Yes.
    MyString = "Yes" ' Perform some action.
    ElseIf Response = vbNo Then
    Application.Goto Reference:="resultsarea"
    Application.Goto Reference:="display"
    ActiveWindow.Zoom = 80
    Selection.Copy
    MyString = "No" ' Perform some action.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save As with extension (Excel 97)

    Use the FileFilter and the FilterIndex parameters of the GetSaveAsFilename Method. Place your cursor at the end of GetSaveAsFilename and press F1 for help on those parameters. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As with extension (Excel 97)

    I do not have the Visual Basic help file.

    Can you give me the line I need?

    Thanks.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As with extension (Excel 97)

    [edited]

    Here is the Help:

    expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

    And some sample code:

    Application.GetSaveAsFilename(InitialFilename:=<my stringvariable>, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save As with extension (Excel 97)

    > I do not have the Visual Basic help file
    Why not?

    Here is the 2000 Help file. I am not aware of any changes from 97.

    GetSaveAsFilename Method


    Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

    Syntax

    expression.GetSaveAsFilename(InitialFilename, FileFilter, FilterIndex, Title, ButtonText)

    expression Required. An expression that returns an Application object.

    InitialFilename Optional Variant. Specifies the suggested file name. If this argument is omitted, Microsoft Excel uses the active workbook's name.

    FileFilter Optional Variant. A string specifying file filtering criteria.

    This string consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. Each separate pair is listed in the Files of type drop-down list box. For example, the following string specifies two file filters, text and addin: "Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla".

    To use multiple MS-DOS wildcard expressions for a single file filter type, separate the wildcard expressions with semicolons; for example, "Visual Basic Files (*.bas; *.txt),*.bas;*.txt".

    If omitted, this argument defaults to "All Files (*.*),*.*".

    FilterIndex Optional Variant. Specifies the index number of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used.

    Title Optional Variant. Specifies the title of the dialog box. If this argument is omitted, the default title is used.

    ButtonText Optional Variant. Macintosh only.

    Remarks

    This method returns the selected file name or the name entered by the user. The returned name may include a path specification. Returns False if the user cancels the dialog box.

    This method may change the current drive or folder.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As with extension (Excel 97)

    O.K. so I'm not too smart.

    Here is what I did:
    Application.GetSaveAsFilename(InitialFilename:=<my stringvariable>, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    The error is over the > and says "Expected Expression". this is after I removed my original "Filer =" which caused a Syntax error.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As with extension (Excel 97)

    Sorry; replace "<mystringvariable>" with whatever name you want to suggest, using quotemarks only as demonstrated, not using <> signs, as in

    Application.GetSaveAsFilename(InitialFilename:="my wonderfulfile", _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

    or a string variable as in

    Dim strMyFileName as String
    strMyFileName= "mywonderfulfile"
    Application.GetSaveAsFilename(InitialFilename:=str MyFileName, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

    I explained the string variable route because it's handy if you are creating a file name such as

    strMyFileName= Application.UserName & " " & Date

    I have a version where I truncate the user-entered customer name and add the date, then use it as a suggested file name.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As with extension (Excel 97)

    I did get the post you made after this but for some reason was unable to connect using it.

    I can't type in specific file names because I don't know what the Save File Names will be. I just want to have the default file type XLS and give whomever is using this the ability to type in whatever name he chooses.

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As with extension (Excel 97)

    Oops, I overlooked one other thing. If you are testing for the result of the file save using GetSaveAsFilename (which you should do to see that the user actually saved the file), you need the parentheses as shown, as in:

    intResult = Application.GetSaveAsFilename(InitialFilename:= ...

    but if you run the cammand without testing, you would use the syntax

    Application.GetSaveAsFilename InitialFilename:=<mystringvariable>, _
    FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As with extension (Excel 97)

    Then I think you can entirely ignore the paramter, as in:

    Application.GetSaveAsFilename FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As with extension (Excel 97)

    Still no good. With or without the expression "Filer =" I get a syntax error or Expected end of Statement error with this.

    Filer = Application.GetSaveAsFilename FileFilter:="Microsoft Excel Workbook (*.xls), *.xls")

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save As with extension (Excel 97)

    As John said you need parentheses around the parameter(s) when it is used as a function: HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim strFiler As String
    strFiler = Application.GetSaveAsFilename _
    (FileFilter:="Excel Workbooks(*.xls),*.xls,All Files(*.*),*.*")
    If strFiler = CStr(False) Then
    MsgBox "Pressed Cancel"
    Else
    MsgBox strFiler
    End If
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Save As with extension (Excel 97)

    Still no good.

    Now there are no errors but although it brings me to the save file window and shows Microsoft Workbook as type of file, when I try to save, it says "From" and restates the location and file name of the new name but does not save it.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Save As with extension (Excel 97)

    That problem shouldn't result from just that line of code:

    Sub filesavetest()
    'GetSaveAsFilename will return the path & filename (FullName) as a String if saved, a Boolean False if not
    Dim varFSResult As Variant
    varFSResult = Application.GetSaveAsFilename(FileFilter:="Excel Workbooks(*.xls),*.xls")
    If TypeName(varFSResult) = "String" Then
    MsgBox "File Saved"
    Else
    MsgBox "File Not Saved"
    End If
    End Sub

    Can you post your entire sub as it now stands?
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Save As with extension (Excel 97)

    The GetSaveAsFileName method does what it says, it gets the filename. If you want to save the file, you use the SaveAs method of the Workbook object. If you record a macro of a SaveAs you get something like:
    <pre>Option Explicit

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 12/31/2002 by sbarrett
    '
    ActiveWorkbook.SaveAs Filename:= _
    "Cocuments and SettingssbarrettMy Documentswopr.xls", _
    FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
    ReadOnlyRecommended:=False, CreateBackup:=False
    End Sub</pre>


    Since you don't care about any of these parameters and they are optional, you only need
    <pre>ActiveWorkbook.SaveAs Filename:=strFiler</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •