VBA: ThisWorkbook.SaveCopyAs will not allow xlsx file format.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Hello Excel Experts,

I have a macro with me that will copy the existing workbook to a user defined path and name which will be provided in cell D8. Good thing about it is that this process happens in the background. But I want to get rid of the macros which were copied along with the workbook. To do this I found the following code in the forum from Well-known Member: Norie

VBA Code:
ThisWorkbook.Sheets.Copy

Set wbNew = ActiveWorkbook

wbNew.SaveAs "NewName", FileFormat:=xlOpenXMLWorkbook

and the one that I am currently using is

VBA Code:
Sub copy_workbook()
Dim lFso As Boolean, mySplit, FnPath As String
Set myFSO = CreateObject("Scripting.FileSystemObject")
FnPath = ThisWorkbook.Sheets("HSheet").Range("D8").Value
mySplit = Split(FnPath, "\", , vbTextCompare)
If myFSO.FolderExists(Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)) Then
    Debug.Print "OK---> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
Else
    MkDir Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
    Debug.Print "New--> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
End If
Set myFSO = Nothing
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("D8").Value ':Save Copy
End Sub

D8 =
D:\Users\Admin\Dropbox\Business Folder\STOCK REPORT\2021\03-2021.xlsb

The second macro or the one I am using right now will copy the excel file smoothly, however, it will not be a macro free workbook.
1. It will not give any prompts at all.
2. It will overwrite if file already exist.
3. It will create a directory in windows explorer for the year only if it doesn't already exist. (example: 2020, 2021, 2022 etc)

My question is how do I perform these three points with the first macro given in this post, so that it will be saved as a macro-free workbook.

Many thanks and will appreciate.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
442
Office Version
  1. 365
Platform
  1. Windows
Norie's code produces a copy of the sheets of ThisWorkbook which, in effect, produces a new workbook, which is then assigned to the variable wbNew. Then, the method SaveAs is used to save it as a XLSX file (xlOpenXMLWorkbook).

To reproduce this process in your code, you need to create a separate workbook containing the sheets to be saved. At present, you are attempting to save a copy of your workbook that contains the very code being used to run this save subroutine. SaveCopyAs (in your code) is different to SaveAs (Norie's code) because SaveCopyAs requires the new workbook to be the same file format as the source workbook. I would suggest following Norie's approach - so the last line of your routine should be amended to:

VBA Code:
Dim NewWB As Workbook, NewFilename As String
NewFilename = ThisWorkbook.Sheets("HSheet").Range("D8").Value
ThisWorkbook.Sheets.Copy
Set NewWB = ActiveWorkbook
NewWB.SaveAs Filename:=NewFilename, FileFormat:=xlOpenXMLWorkbook

One final point:- in order for this to work, you will need to make sure the filename for the new workbook (i.e., cell D8) ends with the extension XLSX, and not XLSB. XLSB is a format that is: (a) inconsistent with the code above, and (b) capable of retaining VBA code. By saving the workbook as XLSX, you will effectively strip it of any macros, which is what I understand you were wanting to happen.

Let me know if that works.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
Norie's code produces a copy of the sheets of ThisWorkbook which, in effect, produces a new workbook, which is then assigned to the variable wbNew. Then, the method SaveAs is used to save it as a XLSX file (xlOpenXMLWorkbook).

To reproduce this process in your code, you need to create a separate workbook containing the sheets to be saved. At present, you are attempting to save a copy of your workbook that contains the very code being used to run this save subroutine. SaveCopyAs (in your code) is different to SaveAs (Norie's code) because SaveCopyAs requires the new workbook to be the same file format as the source workbook. I would suggest following Norie's approach - so the last line of your routine should be amended to:

VBA Code:
Dim NewWB As Workbook, NewFilename As String
NewFilename = ThisWorkbook.Sheets("HSheet").Range("D8").Value
ThisWorkbook.Sheets.Copy
Set NewWB = ActiveWorkbook
NewWB.SaveAs Filename:=NewFilename, FileFormat:=xlOpenXMLWorkbook

One final point:- in order for this to work, you will need to make sure the filename for the new workbook (i.e., cell D8) ends with the extension XLSX, and not XLSB. XLSB is a format that is: (a) inconsistent with the code above, and (b) capable of retaining VBA code. By saving the workbook as XLSX, you will effectively strip it of any macros, which is what I understand you were wanting to happen.

Let me know if that works.

Using the code you've posted and having followed your directions on making it an XLSX extension in cell D8, the file will be created as per my requirement except for the three points which are very important as well.

1. It will not give any prompts at all.
2. It will overwrite if file already exist.
3. It will create a directory in windows explorer for the year only if it doesn't already exist. (example: 2020, 2021, 2022 etc)

I currently get two prompts and one error on saving the file.

1. It prompts me to whether I'd like this to be saved as a macro-enable workbook or not.
It should be macro-disabled as per my requirement.

2. It will ask me to overwrite the file if it already exist.
It must overwrite the existing file.

3. If folder doesn't exist then this will display an error 1004. example , "The filename or path does not exist".
It must create the folder for the year at least if it doesn't exist.

Appreciate your help thus far. Looking forward to your precious advice.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
442
Office Version
  1. 365
Platform
  1. Windows
1. It prompts me to whether I'd like this to be saved as a macro-enable workbook or not.
It should be macro-disabled as per my requirement.

2. It will ask me to overwrite the file if it already exist.
It must overwrite the existing file.
The following amended code should accomplish both one and two.

VBA Code:
Dim NewWB As Workbook, NewFilename As String
NewFilename = ThisWorkbook.Sheets("HSheet").Range("D8").Value
ThisWorkbook.Sheets.Copy
Set NewWB = ActiveWorkbook
Application.DisplayAlerts = False
NewWB.SaveAs Filename:=NewFilename, FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = True

As regards point 3, I'm confused - my earlier reply said "so the last line of your routine should be amended to:" - meaning that you should still execute the preceding part of your script. This is because, on a quick skim, it appears to already do what you require. Does it not?
 
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Many Thanks Dan, all my requirements are fulfilled. I read your post more than once to realize what the code actually does for point 3.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
One last question please. How do I copy all sheets in my workbook except for two sheets namely "HSheet" and "Listing" when this line ThisWorkbook.Sheets.Copy executes? The two sheets are also the hidden sheets, while the rest are all visible. Thanks again.
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
2,018
Office Version
  1. 2019
Platform
  1. Windows
I've selected an Array with recording a macro and copy the sheets I needed to a new workbook. It does the job of copying, and is fast. I extracted that line of code.
I think its all good now.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
442
Office Version
  1. 365
Platform
  1. Windows
Great. Glad that it's worked!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,295
Messages
5,641,398
Members
417,208
Latest member
wendy823

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top