community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Run as command not working with VBScript when called from private Gallery: what next

ACE Emeritus
ACE Emeritus

Hello everyone. I wrote a vbscript that would take a given excel file, and split it into multiple csv files in a different folder directory. I then created a simple alterYX workflow with just one run command that would run this vbscript code. I can log into the server to run this code on the server Desktop installation of AtlerYX designer and it works, however if I publish an analytic app that uses this command line call it is not working. I am not receiving any kind of error from the system, the alteryx application is running and returning results with no error; however the vbscript simply isn't running. It's almost like when the vbscript is called from an application it is being skipped over.

 

We have our server set up to run unrestricted, so I am not sure what is causing this problem.

 

Does anyone have any ideas of what I should check next in order to get this to work? Thank you

 

Additional information: 

I have created a folder path on the server named

C:\datafiles\Work Folder

C:\dataFiles\Population CSV Files

 

I have an xlsb file in the work folder location above.  I then run the attached VBScript with the following command line option...

 

/c cscript.exe "C:\Data file Processing\BoA Sampling\ExceltoCSV parse JustTabName.vbs" "C:\Datafiles\Work Folder" "C:\Datafiles\Population CSV Files" 

 

This will run the vbscript and it will split the xlsb file in the "work folder" location into multiple CSV files in the "population csv files" location.  This works perfectly if run from the command line on the server, or even if run from Alteryx Desktop ON the server. However when run from an app in our private gallery it does not work. (nothing happens).

 

Let me know if you need any additional information from me to help debug this problem! :-)

P.S. I uploaded my vbscript as a txt since vbs is a restricted file type. make sure you convert it back to vbs if you want to use it in the alteryx workflow i have attached!

 

ACE Emeritus
ACE Emeritus

here is a very simple version of the alteryx workflow i'm trying to get to run.

 

Again this workflow works great from a desktop version, but not from an app published to our private server... I'm imaging it has something to do with permissions, but i can't see why.

 

Our analytic gallery runs under a service account that has full access and rights to all of the folder locations used by the vbscript..

Highlighted

This "fix" sounds crazy, but we ran into similar issues with vbs and this post worked. You would make a new blank folder called desktop in two spots the server:

 

"C:\Windows\SysWOW64\config\systemprofile\desktop"

"C:\Windows\System32\config\systemprofile\desktop"

 

No files need to be there, but it seems like the mere existence of that folder solved our issue. It seems to me like it had something to do with the fact that it's a service account running when you run it in the gallery. We had the same experience as you where we could log into the server and run an app but it would have problems when we ran it in the gallery. The above folders solved our issue.

 

If that doesn't work, I can certainly look more into your vbs, but your description is so similar to our experience that I thought I would try this first.

 

Patrick

ACE Emeritus
ACE Emeritus

Hello Patrick.  So interestingly your suggestion made the VBscript go from "failing" in one location to "failing" in another (further down the path).

 

So I put some error handling in my file and discovered that before your fix, it was "failing" at the following line of code (it's not failing iwth an error, simply not running this section..)

 

 

Set objWB = objExcel.Workbooks.Open(strFilename)

 

After making the change that you noted below this part is now working, however it doesn't seem to work at the next part...

 

For Each objws In objWB.Worksheets  

 

 

So my vbscript below doesn't work....

 

 

Dim strFilename
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")


Set objFolder = objFSO.GetFolder(WScript.Arguments(0))

Set colFiles = objFolder.Files


For Each objFile in colFiles
FileName=objFile.Name

strFilename = WScript.Arguments(0) & "\" & FileName

i=InStrRev(FileName,".")
if (i>0) then
results=Mid(FileName ,1,i-1)
End If


If objFSO.fileexists(strFilename) Then
Call Writefile(strFilename)
Else
wscript.echo "no such file!"
End If

 


Next

 

Set objFSO = Nothing


Sub Writefile(ByVal strFilename)
Dim objExcel
Dim objWB
Dim objws


Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open(strFilename)

For Each objws In objWB.Worksheets
if objws.visible<>2 Then
objws.visible = True
objws.Copy
objExcel.ActiveWorkbook.SaveAs WScript.Arguments(1) & "\" & objws.Name & ".csv", 6
objExcel.ActiveWorkbook.Close False
End If
Next


Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("C:\Data File Processing\BoA Sampling\testfile.txt", True)
MyFile.WriteLine(strFilename)
MyFile.Close

objWB.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub

 

But a very similar one that simply saves the file as another excel file (instead of looping over the sheets and creating csv files) does work.. So the code below DOES WORK.... 

 

 

Dim strFilename
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")


Set objFolder = objFSO.GetFolder(WScript.Arguments(0))

Set colFiles = objFolder.Files


For Each objFile in colFiles
FileName=objFile.Name

strFilename = WScript.Arguments(0) & "\" & FileName

i=InStrRev(FileName,".")
if (i>0) then
results=Mid(FileName ,1,i-1)
End If


If objFSO.fileexists(strFilename) Then
Call Writefile(strFilename)
Else
wscript.echo "no such file!"
End If

 


Next

 

Set objFSO = Nothing


Sub Writefile(ByVal strFilename)
Dim objExcel
Dim objWB
Dim objws


Set objExcel = CreateObject("Excel.Application")

Set objWB = objExcel.Workbooks.Open(strFilename)

objExcel.ActiveWorkbook.SaveAs "C:\Data File Processing\workplz.xlsb", 50


Dim fso, MyFile
Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("C:\Data File Processing\testfile.txt", True)
MyFile.WriteLine(strFilename)
MyFile.Close

objWB.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub

 

 

 

 

This seems to imply that for whatever reason an application that is run from the gallery handles vbscript differently than just running that same code from the desktop.... Not sure why.

 

Do you have any additional ideas as to why the sheets method would be failing?

 

Also thanks very much for that folder suggestion, it at least allows me to open and save excel files through vbscript now!

ACE Emeritus
ACE Emeritus

Hello again Patrick! It turns out that after makign this fix and then re-setting the server this DID fix my vbscript problem.

 

What a crazy solution as you noted, but it did work.  Thank you so much for your help! :-)

Atom

Just wanted to check in and mention that we were having the exact same problem - Patrick's fix worked. Thanks!

Asteroid

Just wanted to add to this that, if you use the Run As option in your server/scheduler, you will need to make sure that the person/functional id set as the run-as also has access to the folders. I had created the folders, but the run-as is set as my manager's ID and we log into the machine with a functional ID. Only the functional ID was given access when I created the folders and applied my own Admin password. I logged into the computer with my credentials and changed the security on the folders to include my manager's run-as ID, and that finally worked. Whew. 

In your Workflow "show forums.yxzp" you Don't need to make your own Dynamic Input use the included "Dynamic Input" tool built into Alteryx.

 

Dynamic Input Tool.png

Labels