Handling Notepad Using VBA from Excel

I am trying to send a cut-list from excel to a program called genesis/evolution on my CNC machine.  The file is to be read from a folder called "LSX". The file extension is ".LST".

The list will not open in Genesis if it is in excel ".csv" format.  However, I found that it will work if I:
1) Right click on the excel file (in .csv format) icon and "open with" Notepad (also appears with .csv extension)
2) Make some changes to the text in Notepad:
     a) The file appears as follows once opened in Notepad:
                    LST,1,,,,,,,,,,,,,,,,,                                                                                     (line1)
                    text1,text2,text3,text4,text5,text6,text7  etc.                                                (line2)    
                    text1,text2,text3,text4,text5,text6,text7  etc.                                                (line3) etc.
     b) I need to delete all of the extra commas at the end of the first line so I'm left with just:   LST,1,
     c) Finally I need to do a "Find" and "Replace" to change all of the remaining commas to semicolons.  The file will not open if it is delineated with commas.
3) "Save As" from Notepad with a ".txt" extension and close the open Notepad window.
4) Right click and "Rename" the file with the extension ".LST".
                           I can now open this file successfully from the above mentioned "LSX" folder
                           - note that if I save directly with an ".LST" extension and skip the ".txt" part at step #3, it does not work.

Could I please get some help in automating this sequence from excel using VBA?

I understand that it might not be possible to manipulate Notepad with VBA and an alternative approach may be necessary.
I am not  a programmer, and as much coding help as possible would be greatly appreciated.            
ssumbAsked:
Who is Participating?
 
vinnyd79Connect With a Mentor Commented:
Give this macro a try. adjust the necessary parts as noted:


Sub ExportToLST()
Dim ExportFile As String
Dim ff As Integer
Dim MaxColumns As Long
Dim MaxRows As Long
Dim strRow As String
Dim x As Long
Dim y As Long

' set your filename here. If it exists it will be overwritten
ExportFile = "C:\MyNewFile.LST"

ff = FreeFile
Open ExportFile For Output As #ff

' adjust sheet name here
Set ws = ThisWorkbook.Sheets("Sheet1")

' adjust cols and rows to check
MaxColumns = 24
MaxRows = 65000

' write first row
For y = 1 To MaxColumns
    If y = 1 Then
        If Trim$(ws.Cells(1, y)) <> "" Then strRow = ws.Cells(1, y)
    Else
        If Trim$(ws.Cells(1, y)) <> "" Then strRow = strRow & "," & ws.Cells(1, y)
    End If
Next y

' print to file
Print #ff, strRow

' get rest of rows & cols
For x = 2 To MaxRows
    For y = 1 To MaxColumns
        If y = 1 Then
            If Trim$(ws.Cells(x, y)) <> "" Then strRow = ws.Cells(x, y)
        Else
            If Trim$(ws.Cells(x, y)) <> "" Then strRow = strRow & ";" & ws.Cells(x, y)
        End If
    Next y
   
    ' print to file
    Print #ff, strRow
   
' get next row
Next x

Close #ff

MsgBox "File is Complete!", vbExclamation, "Process Finished"

End Sub
0
All Courses

From novice to tech pro — start learning today.