Solved

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

Posted on 2014-03-27
7
3,650 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

752 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