Making Excel File Save As Appear after opening Excel File from Access

Posted on 2009-12-31
Last Modified: 2013-11-27
I have a procedure that opens an Excel File in a Folder, that has been created from a query.  What I need is immediately after the Excel File is opened to prompt for a Save As
Right after this line
.workbooks.Open (sFullPath)

How can I do this?  Thanks
Function OpenSpecific_xlFile()

     '   Late Binding (Needs no reference set)

    Dim oXL As Object

    Dim oExcel As Object

    Dim sFullPath As String

    Dim sPath As String



     '   Create a new Excel instance

    Set oXL = CreateObject("Excel.Application")



     '   Only XL 97 supports UserControl Property

    On Error Resume Next

    oXL.UserControl = True

    On Error GoTo 0



     '   Full path of excel file to open

    On Error GoTo ErrHandle

    sFullPath = GetDBPath + "\Excel_LitScoop_Report\Excel_LitScoopReport.xls"



     '   Open it

    With oXL

        .Visible = True

        .workbooks.Open (sFullPath)


    End With




    Set oXL = Nothing

    Exit Function



    oXL.Visible = False

    MsgBox Err.Description

    GoTo ErrExit

End Function

Open in new window

Question by:quizzer
    LVL 28

    Accepted Solution

    Hi you can use the GetSaveAsFilename method of the excel application.
    Cheers, Andrew

            Set oXLSheet = .workbooks.Add(pstrFile)

            fileSaveName = .GetSaveAsFilename(initialfilename:=pstrFile, fileFilter:="Excel Files (*.xls*), *.xls*")
            If fileSaveName <> "False" Then
               oXLSheet.SaveAs fileSaveName
            End If

    Cheers, Andrew

    Author Comment

    Where do i put this relative to the entire function?

    Is pstrFile the same as sFullPath
    LVL 28

    Expert Comment

    Yes pstrFile is the same as sFullPath, I would use this as a function that I would pass the name of the file rather than explicitly setting the file name, it is then reusable.

    The code would go after the .Workbook.Open line

    Cheers, Andrew

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now