Weird problem with Call Shell

[oatsybob]

Hey guys, I'm having a weird problem with the call shell function of VBA. When the function executes, a command prompt pops up for a few seconds, then closes despite the '/k' in my code. When I double click the exe I'm calling it works fine. When I run it from within a command prompt it works fine.

Private Sub Command13_Click()

Dim strPath As String
Dim export As String
Dim i As Integer
Dim varitem As Variant

export = ""
strPath = "C:\Text\test3.csv"
i = 1

For Each varitem In Me.List0.ItemsSelected
    'iterates through items selected in list and for each non-blank item adds it to a comma separated string
    If (Me.List0.Column(7, varitem)) <> "" And i <> Me.List0.ItemsSelected.Count Then
        export = export & Me.List0.Column(7, varitem) & ","
        i = i + 1
    'ensures the last number isn't followed with a comma
    ElseIf (Me.List0.Column(7, varitem)) <> "" And i = Me.List0.ItemsSelected.Count Then
        export = export & Me.List0.Column(7, varitem)
        i = i + 1
    'progresses iteration through list if item is blank without messing up formatting of the string
    Else
        i = i + 1
    End If
Next varitem

'deletes old file if it exists
If Dir(strPath) <> "" Then
    Kill (strPath)
End If

Dim fs As Object
Dim a As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(strPath)
a.writeline (export)
Call Shell("C:\Text\text.exe /k", vbNormalFocus)
End Sub

The code writes info to a csv file, then calls an exe which accesses the exe file. Any thoughts?

[oatsybob]

the exe file accesses the csv file, not exe. Typo!

[ssanfu]

Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(strPath)
a.writeline (export)
'close the CSV file
Call Shell("C:\Text\text.exe /k", vbNormalFocus)

IFRC, the file needs to be saved/closed before it can be accessed. Could be wrong...., but I always close a txt/csv file before trying to Shell out and open it. Closing forces the buffers to be flushed and all lines written.

[oatsybob]

Good call. I need to get into that habit. When I tried it, unfortunately had the same problem. In fact, I tried commenting out everything but the call shell function and had the same problem! I did a screen capture to see the cmd error message, and it seems the python script that I compiled into a Windows executable isn't finding the 'test3.csv' even though it's clearly in the same directory as the executable. The executable finds the csv file without issue when it isn't called from access. My only thought is that for some reason running an python based executable from vba changes the directory where the script looks for dependent files. By default it looks in the directly where it is located. Any thoughts on how to change this?

[ssanfu]

the python script that I compiled into a Windows executable

Since you wrote the exe, modify it to print/display the current path.
Execute TEXT.EXE from windows to see what path is displayed, then execute TEXT.EXE from within Access.

[oatsybob]

Did the trick! I hard coded the correct directory into the python script and now it's working like a charm.

[ssanfu]

You obviously have the python script reading the text file name from Access. Instead of hard coding the path, can't you have a default path for when you execute the script from Windows and when executed from within Access, use the path passed to the script?

I just hate to hard code paths, in case I move the executable.

Anyhoo, glad you got it working.

[oatsybob]

I am not sure I understand your meaning. Are you saying hard code the path conditionally if opened from access, but rely on the default otherwise?

[ssanfu]

Never mind.

I re-read the posts and I seem to have gone off on a different road.

I was thinking that instead of hard coding the path, to either use a default path or a path passed as an argument.

I must have been sleep typing....