Vba output to a batch file

Hi learner99,

The following macro will help you get started. It creates a .bat file using text from Column 'A' (starting at row 11) in the Active Sheet. You should be able to adapt it to your needs. Also see the attached sample file which implements the macro.

Option Explicit

Sub CreateDotBatFileFromDataInColumnA()

  Dim iFileNo As Long
  Dim iFirstRow As Long
  Dim iLastRow As Long
  Dim iRow As Long
  
  Dim bNeedMore As Long
  
  Dim sDataLine As String
  Dim sFileName As String
  Dim sFolder As String
  Dim sPathAndFileName As String
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Preparation for Export
  '''''''''''''''''''''''''''''''''''''''''''''
 
  'Create the Folder and File Name Combination from the Worksheet
  'Use the folder that contains the file that is running this code
  'Create an arbitrary file name
  sFolder = ThisWorkbook.Path
  sFileName = "abc.bat"
  
  'Make sure the Folder has a trailing BACKSLASH
  If Right(sFolder, 1) <> "\" Then
    sFolder = sFolder & "\"
  End If

  'Build the Path and File Name Combination
  sPathAndFileName = sFolder & sFileName
  
  'Verify that the file to be exported DOES NOT exist
  If LJMFileExists(sPathAndFileName) = True Then
    MsgBox "NOTHING DONE.  File EXISTS." & vbCrLf & _
           "Arbitrary rules DO NOT ALLOW file OVERRWRITE." & vbCrLf & _
           "Folder: '" & sFolder & "'" & vbCrLf & _
           "File: '" & sFileName & "'" & vbCrLf & _
           ""
    Exit Sub
  End If

  '''''''''''''''''''''''''''''''''''''''''''''
  'Export
  '''''''''''''''''''''''''''''''''''''''''''''
    
  'Allocate a file 'handle'
  iFileNo = FreeFile
  
  'Open the file
  Open sPathAndFileName For Output As #iFileNo
  
  'Create the starting and ending Row Numbers
  iFirstRow = 11
  iLastRow = ActiveSheet.Range("A:A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  
  For iRow = iFirstRow To iLastRow
    sDataLine = ActiveSheet.Cells(iRow, "A").Value
    Print #iFileNo, sDataLine
  Next iRow
  
  'Close the file
  Close #iFileNo
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  '''''''''''''''''''''''''''''''''''''''''''''
  
  MsgBox "Batch file creation completed." & vbCrLf & _
         "Folder: '" & sFolder & "'" & vbCrLf & _
         "File: '" & sFileName & "'" & vbCrLf & _
         ""
              
End Sub

Public Function LJMFileExists(sPathAndFullFileName As String) As Boolean
  'This returns TRUE if a file exists and FALSE if a file does NOT exist
  
  Dim iError As Integer
  Dim iFileAttributes As Integer

  On Error Resume Next
  iFileAttributes = GetAttr(sPathAndFullFileName)
     
  'Check the internal error  return
  iError = Err.Number
  Select Case iError
    Case Is = 0
        iFileAttributes = iFileAttributes And vbDirectory
        If iFileAttributes = 0 Then
          LJMFileExists = True
        Else
          LJMFileExists = False
        End If
    Case Else
        LJMFileExists = False
  End Select

  On Error GoTo 0

End Function

To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Lewis