Write to a .BAT file then open it?

[GBalcom]

I need to write a .BAT file programmatically, then execute it. At some point I'll need to delete it programmatically as well. But perhaps it would be better to just override it next time?

I've been looking for 20 min for code that would write to a text file. I'm at home now but will get into specifics on this thread in the morning. At this point I'm hoping for just generic code to create and write to a text file (guess that's all I'll need to start). I've found some code that uses that wraps the shell command for running it.

Any tips welcome

 

[MarkK]

This link might answer some questions for you . . .
https://msdn.microsoft.com/en-us/library/dd439413(v=office.12).aspx

 

[GBalcom]

Thank you Mark! That thread actually came up in a previous search on this topic for me, but I dismissed it when I saw the word "macro", thinking it was access macro based, not VBA. That certainly does answer some questions. I'll see what I can make of it tomorrow.

 

[MarkK]

You can also write and run .vbs, or VBScript, files under Windows. VBScript is a lot like VBA so it's easier to program than .bat. For your consideration. You can open recordsets in VBScript, and create objects, and so on.

 

[GBalcom]

OK, I got pretty far, but I'm stuck...I'm creating the BAT file that I need, and able to run it. But, it's not acting correctly. If I run it by double clicking manually on it, it behaves differently than if I run it from code. Any idea on why?

Code below:

Private Sub cmdRunCutrite_Click()
    ' Status:  In Devlopment
    ' Comments:
    ' Params  :
    ' Created : 04/13/16 11:46 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd



    
    Dim strFileName As String
    Dim strFilePath As String

        strFileName = "CutriteAutoFile.BAT"
        strFilePath = "S:\Cut Rite_v90\"
        
    'create file
        CreateBatchFile strFilePath, strFileName, GetName()
        
    'run file
        Shell strFilePath & strFileName, vbNormalFocus
        
        
        
        



    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".cmdRunCutrite_Click"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Sub

Private Sub CreateBatchFile(strFilePath As String, strFileName As String, strJobName As String)
    ' Status:  In Devlopment
    ' Comments: Creates a Batch File for Cutrite to run the output from a Microvellum project automatically, and silently.  It will Import, optimize, print the default report, then send to saw.
    ' Params  :
    ' Created : 04/13/16 10:51 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

'*****************************************************************************************************
'       CREATE NEW FILE
'*****************************************************************************************************

    
    

    ' Declare a FileSystemObject.
    Dim fso As FileSystemObject

    ' Create a FileSystemObject.
    Set fso = New FileSystemObject


    ' Declare a TextStream.
    Dim stream As TextStream

    ' Create a TextStream.
    Set stream = fso.CreateTextFile(strFilePath & strFileName, True)


'*****************************************************************************************************
'       POPULATE FILE
'*****************************************************************************************************

    Dim Line1 As String
    Dim Line2 As String
    Dim Line3 As String
    Dim Line4 As String
    Dim Line5 As String
        'If the file location is ever changed, we will need the following lines inserted into the BAT file before the rest of them:
            'S:
            'cd "\Cut Rite_v90\"
            
            ' line one imports the file
        Line1 = "C:\V90\import.exe " & Chr(34) & strJobName & ".ptx" & Chr(34) & " /auto"
                Debug.Print Line1
            'line 2 optimizes it
        Line2 = "C:\V90\batch.exe " & Chr(34) & strJobName & Chr(34) & " /auto /optimise "
                Debug.Print Line2
            'line 3 prints it out
        Line3 = "C:\V90\output.exe /print"
            'line 4 pauses the program to make sure it has time to print....may not be needed
        Line4 = "" '"pause"
            'line 5 transfers it to the saw.
        Line5 = "C:\v90\sawlink /auto /1"
        

    'write to file
        stream.WriteLine Line1
        stream.WriteLine Line2
        stream.WriteLine Line3
        stream.WriteLine Line4
        stream.WriteLine Line5
        stream.Close
        






    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Sub

PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".CreateBatchFile"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Sub

Private Function GetName() As String
    ' Status:  In Devlopment
    ' Comments: Gets Name of Project for use in CutRite transfer, and copying name to microvellum work order.
    ' Params  :
    ' Returns : String
    ' Created : 04/13/16 11:12 GB
    ' Modified:
    
    'TVCodeTools ErrorEnablerStart
    On Error GoTo PROC_ERR
    'TVCodeTools ErrorEnablerEnd

    GetName = ""

Dim strWoName As String
    strWoName = Trim(Me.WoNbr & "-" & Me.JobDescr)
    strWoName = Replace(strWoName, "WH ", "")
    strWoName = Replace(strWoName, "-J", "J")
    strWoName = Replace(strWoName, " ", "_")
    strWoName = Replace(strWoName, "__", "_")
    strWoName = Replace(strWoName, "/", "-")
    
    'Cutrite Label will only hold 25 characters, truncate after 25
        strWoName = Left(strWoName, 25)
    
    
        Debug.Print strWoName


GetName = strWoName




    'TVCodeTools ErrorHandlerStart
PROC_EXIT:
    Exit Function

PROC_ERR:
    MsgBox Err.Description, vbCritical, Me.Name & ".GetName"
    Resume PROC_EXIT
    'TVCodeTools ErrorHandlerEnd

End Function

 

[MarkK]

If I run it by double clicking manually on it, it behaves differently than if I run it from code.

Can you elaborate on "behaves differently?"

I would tend to not create the file and run it in the same process.

    [COLOR="Green"]'create file[/COLOR]
    CreateBatchFile strFilePath, strFileName, GetName()
[COLOR="Green"]    'run file[/COLOR]
    Shell strFilePath & strFileName, vbNormalFocus

Give yourself a "create file" button, and then a separate "run file" button so there's a little time after your disk write. Disk operations are very delicate.

 

[GBalcom]

Mark,
I will try your suggestion and post back.

The issue is likely program specific (Cutrite our optimizing program). It gives the error, "No system parameter file in current directory, please press a key". It give the error a few times, then doesn't work at all.

When I execute manually (just by clicking on the file), it runs perfectly.

 

[GBalcom]

Mark,
I tried two separate buttons....same result. At this point, I've put a shortcut to the .bat file on the desktop to get us by. I'd still love to know why it works fine when I double click it directly, but I can't call it from my access program.

 

[GBalcom]

Mark,
I apologize for the delay. I've been moving, which is never fun. I have resolved this. The answer was in contents of the batch file. If you look at my code above, I've commented out some of it I didn't think I would need.

Because it appears that somehow manually executing the file runs the file from its folder. When ran from VBA, it doesn't think it runs from that folder. Therefore it needed a change in the file directory before running. That is the first two lines; S: and cd (new directory)

Hope this helps someone else!

 

[MarkK]

Congrats and thanks for posting back with the solution. Appreciate it.