Issue with .xls having a ' (single quote in front of all data)

[MJohnson10]

Having an issue when doing an output to .xls



Each column has that single quote ' and it is hidden as you see it can only be seen in the highlighted area but not in the data cell. and i need the file format to be .xls but this hidden character has been failing to upload due to this character. any help would be greatly appreciated!

I am at a loss and any help would be appreciated!

- Michael Johnson

[JoeS]

HI @MJohnson10 

No problem, I have built you an example in VBS (as then you don't need to use Excel macros/VBA).

The script is:

Set objExcel = CreateObject("Excel.Application")
fileName = WScript.Arguments.Item(0)
Set objWorkbook = objExcel.Workbooks.Open(fileName)

objExcel.Application.Visible = False
objExcel.Calculate

objWorkbook.SaveAs Left(fileName,Len(fileName)-1),56

objExcel.quit

You then need to run it using Alteryx, which you can do using the run command tool.

This can also output your data as well.

So first step is output the Excel file to .xlsx. Then you need to run wscript.exe, you then need to run my script and lastly my script then needs to be told the location of the .xlsx file.

The script will name the xls file the same as the xlsx file.

You'll need to update the paths to work for your locations, but then you should be good to go, no manual step!

PS in order to upload the script to the forum I had to give it a .txt extension, please rename it to .vbs