Solved

From Access, "save as" an open Excel workbook with a new file path and name.

Posted on 2014-03-27
7
3,057 Views
Last Modified: 2014-04-02
I have used VBA in Access to open an Excel 2010 workbook, "FileName",  and I worked with the Excel workbook.

Then I switch to my Access 2010 window and want to click on a button which will then save the Excel file with a new uniquely coded name "NewFilePath", which Access has calculated automatically.

As mentioned above, in my case the present name of the Excel file is known as a variable "FileName" in the Access code.

1) What is the code to save this file "FileName" as "NewFilePath"?
0
Comment
Question by:Fritz Paul
7 Comments
 
LVL 10

Expert Comment

by:Anthony Berenguel
Comment Utility
The following links should help you get started.

Workbook.SaveAs method

http://www.vb-helper.com/howto_excel_get_save_as.html

Open in new window

0
 

Author Comment

by:Fritz Paul
Comment Utility
I am sorry, but the code referred to did not help me. I tried various things.

I spent so much time trying this in various ways that I feel disappointed. I feel that I put a lot of effort into formulating my question and I got someone else's first stab at a google search back. I am a subscriber to experts-exchange, because I usually find that different from the regular forums, I get complete answers back.  I always do a general rearch for my problem first and then before I spend hours trying all the possible answers, I turn to faithful old experts-exchange.

Below is the result of one effort:
I tried referred to code (pasted below), but that resulted in the following Error code.

Private Sub cmdGetSaveAsName_Click()
Dim file_name As Variant

    ' Get the file name.
    file_name = Application.GetSaveAsFilename( _
        FileFilter:="Excel Files,*.xls,All Files,*.*", _
        Title:="Save As File Name")

    ' See if the user canceled.
    If file_name = False Then Exit Sub

    ' Save the file with the new name.
    If LCase$(Right$(file_name, 4)) <> ".xls" Then
        file_name = file_name & ".xls"
    End If
    ActiveWorkbook.SaveAs Filename:=file_name
End Sub

Open in new window


Error message
My Access references are below.

My access references
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
I'm assuming your Excel files are still "xls" extension ??   Your code refers to "xls" not the new "xlsx" extension.

Scott C
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Fritz Paul
Comment Utility
Thanks. In the specific example that I gave above the error message was actuallly about the "GetSaveAsFilename".

However I think you are probably right that that the ".xls" was also a problem.

What I want to do is address a specific open Excel workbook and save it under another path.

Here is my code that does not want to work. Please how do I fix this.

Dim xlApp As excel.Application
Dim xlWB As excel.Workbook
Dim xlSheet As excel.Worksheet
Dim file_name As String

    xlWB = "Outline.xls" 'This workbook is already open among others.


    file_name = "c:\zMyfile1.xlsx" ' This is the new path that I want to save it to.
   
    xlWB.SaveAs Filename:=file_name


'Set xlWB = Nothing
'Set xlApp = Nothing
0
 

Accepted Solution

by:
Fritz Paul earned 0 total points
Comment Utility
This code does exactly what I wanted.

Private Sub cmdSaveAsName_Click()
    CopyFile "C:\Excel\Experiments\Outline.xlsx", "c:\TEMP\zMyfile1.xlsx"
End Sub


Public Sub CopyFile(ByVal strSourceFile As String, strDestinationFile As String)
    FileCopy strSourceFile, strDestinationFile ' Copy source to target.
End Sub
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
This code works for me (Office 2010):
         strTitle = "Publication Data for " & Format(GetProperty("FirstDayOfRange", ""), _
            "m-d-yyyy") & " to " & Format(GetProperty("LastDayOfRange", ""), "m-d-yyyy")
         strSaveName = strExcelWorkbooksPath & "\" & strTitle & ".xlsx"
         Debug.Print "Save name: " & strSaveName
         wkb.SaveAs FileName:=strSaveName, _
            FileFormat:=xlOpenXMLWorkbook

Open in new window


You may have to fiddle with FileFormat arguments to get the right one.  Also, if it is a macro-enabled workbook, the extension is .xlsm.  The extension has to match the FileFormat -- .xlsx or .xlsm for one of the newer formats, .xls for an older format.  See the XLFileFormat enum for all of them.
XLFileFormat enum
0
 

Author Closing Comment

by:Fritz Paul
Comment Utility
The code solves my problem, even on a much wider scale as what I required at the moment.

I just saves the file first and then makes a copy.
 
Thanks for everyone who tried to help or who gave it a thought.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

10 Experts available now in Live!

Get 1:1 Help Now