I have an Excel application written in VBA that retrieves information from an external source.
Upon exporting this data to a new workbook and sheet, I encounter several errors when attempting to upload it into an application called Adfinity. I will add the errors I get from the Adfinity app for further clarification.
However, when I use the “Copy File” function by right-clicking on the data tab and selecting the new workbook, the same data is accepted in the Adfinity app without any issues.
The data in both files is identical, suggesting that the problem arises during the process of using VBA code to create a workbook and copy the data into it.
I have attached both Excel files along with my VBA code for reference.
' Set the Output worksheet
Set wsOutput = ThisWorkbook.Sheets("Output")
' Create a new workbook
Set newWB = Workbooks.Add
' Rename the new workbook to match the tablename
newWB.SaveAs folderPath & tableName & ".xlsx", fileFormat:=51 ' Specify the path where you want to save the new workbook
' Rename the default sheet to "Import"
newWB.Sheets(1).Name = "Import"
' Set the Import worksheet in the new workbook
Set wsImport = newWB.Sheets("Import")
' Copy data from Output to Import
wsOutput.UsedRange.Copy wsImport.Range("A1")
' wsImport.Columns(col).Hidden = True
wsImport.Columns.AutoFit
' Save and close the new workbook
newWB.Close SaveChanges:=True
' release the notebook
Set newWB = Nothing
Files can be found Here
I have tried different fileformat’s, comparing the Excels, searching for alternate methods to create a new Excel file, change columns, alignment, etc.
user24752674 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.