Solved

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

Posted on 2014-03-27
7
3,458 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
ID: 39960110
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
ID: 39960825
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
ID: 39960869
I'm assuming your Excel files are still "xls" extension ??   Your code refers to "xls" not the new "xlsx" extension.

Scott C
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Fritz Paul
ID: 39961029
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
ID: 39961276
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
ID: 39962790
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
ID: 39971728
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

821 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