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

[omairhe]

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

ThisWorkbook.Sheets.Copy

Set wbNew = ActiveWorkbook

wbNew.SaveAs "NewName", FileFormat:=xlOpenXMLWorkbook

and the one that I am currently using is

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.

 

[Dan_W]

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:

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]

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:

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]

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.

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?

 

[omairhe]

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]

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]

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]

Great. Glad that it's worked!