Solved

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

Posted on 2014-03-27
7
3,867 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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