Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4422
  • Last Modified:

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

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
Fritz Paul
Asked:
Fritz Paul
1 Solution
 
Anthony BerenguelCommented:
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
 
Fritz PaulAuthor Commented:
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
 
clarkscottCommented:
I'm assuming your Excel files are still "xls" extension ??   Your code refers to "xls" not the new "xlsx" extension.

Scott C
0
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!

 
Fritz PaulAuthor Commented:
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
 
Fritz PaulAuthor Commented:
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
 
Helen FeddemaCommented:
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
 
Fritz PaulAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now