Excel | VBA Macro | Method SaveAs - Strange Behaviour

bernardosalvador

New Member
Joined
Aug 22, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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:
VBA Code:
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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
Back
Top