Fritz Paul
asked on
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"?
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"?
ASKER
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.
My Access references are below.
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
My Access references are below.
I'm assuming your Excel files are still "xls" extension ?? Your code refers to "xls" not the new "xlsx" extension.
Scott C
Scott C
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This code works for me (Office 2010):
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.
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
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.
ASKER
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.
I just saves the file first and then makes a copy.
Thanks for everyone who tried to help or who gave it a thought.
Workbook.SaveAs method
Open in new window