?
Solved

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

Posted on 2009-12-31
3
Medium Priority
?
483 Views
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
     
    
ErrExit:
    Set oXL = Nothing
    Exit Function
     
ErrHandle:
    oXL.Visible = False
    MsgBox Err.Description
    GoTo ErrExit
End Function

Open in new window

0
Comment
Question by:quizzer
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 26154439
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
0
 

Author Comment

by:quizzer
ID: 26154917
Thanks!!!
Where do i put this relative to the entire function?

Is pstrFile the same as sFullPath
0
 
LVL 28

Expert Comment

by:TextReport
ID: 26155204
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
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

807 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