Excel | VBA Macro | Method SaveAs - Strange Behaviour

[bernardosalvador]

Hi folks,

I have a VBA Macro that is intended to do the following steps:

1) Transform data from the CurrentWorkbook where VBA Code is (let's say Book1.xlsm) and save in a multidimensional array
2) Open an excel file in old format (lets say Book2.xls) and paste multidimensional array content in specific sheet (let's say Book2Sheet1)
3) Copy Sheet1 to a new workbook - by using the worksheet.Copy method I think it assumes the standard .xlsx format, so lets say Book3.xlsx is created with Book3Sheet1
4) SaveAs Book3.xlsx (which until now is temporary and not saved anywhere) as Book4.xls (yes, .xls file) and in a specific FilePath of a Sharepoint (https://XXXX.sharepoint.com/sites/XXXX/Shared Documents/Folder/Folder/Folder/..../)

Now, the strang behaviour is coming from step 4), because I can run the code successfully in my computer (let's say User A), but it's returning Error 1004 in the SaveAs line to User B.

Debugs already done:
- User B as complete access to Sharepoint
- User B is able to run the macro successfully in next situations so far:

- if there isn't any file in the destination folder with the same name + extension (However, User A can run the macro 10 times in one minute and it's always replacing the file with success)​

- if VBE (Visual Basic Editor) is opened and Macro is ran directly in there​

- if Macro is ran in the sheet, after the 1004 error, going to debug and continuing the execution​

Here is the piece of code that is being used and it's returning error in User B but it's being used without any restrictions in User A:

Dim templateWorkbook As Workbook: Set templateWorkbook = Workbooks.Open(rawDataFilePath)
Dim templateWorksheet As Worksheet: Set templateWorksheet = templateWorkbook.Sheets(templateSheetCode)
Dim newBook As Workbook
Set newBook = Workbooks.Add
Dim newSheet As Worksheet
templateWorksheet.Copy before:=newBook.Sheets(1)
templateWorkbook.Close
Set newSheet = newBook.ActiveSheet
newSheet.Cells(templateInitialRowReference, templateInitialColumnReference).Resize(UBound(templateDataArray, 2) - 1, templateNumberOfColumns) = WorksheetFunction.Transpose(templateDataArray)
newBook.Sheets(2).Delete
newBook.SaveAs Filename:=finalTemplateFolderPath & finalTemplateFilename, FileFormat:=xlExcel8

Now, if we duplicate SaveAs command with this, it will work successfully:

newBook.SaveAs Filename:=finalTemplateFolderPath & finalTemplateFilename
newBook.SaveAs Filename:=finalTemplateFolderPath & finalTemplateFilename, FileFormat:=xlExcel8

But with this code it's creating a .xlsx file and a .xls file.

Any advise?

Thanks a lot,
Bernardo