Getting around VBA type mismatch for .Bat generated in Excel

Question

I have a spreadsheet that I enter data into. Filenames, and times and lots of info that in the end, generates a windows batch file.

Image of end result of my excel formulas Text representation of end result of my excel formulas

Column-I                Column-J                        Column-K
echo f | xcopy /f /y    \\server123\data\3838383.398    %userprofile%\Desktop\VT\3838383.398
echo f | xcopy /f /y    \\server123\data\3838384.398    %userprofile%\Desktop\VT\3838384.398
echo f | xcopy /f /y    \\server123\data\3838385.398    %userprofile%\Desktop\VT\3838385.398

With that said, I have about 30 lines I enter info into. Sometimes I only need 1 line. When I do that, the remaining 29 lines are filled with #VALUE!. So it looks like this (below) when I only have 1 line of data put into the spreadsheet.

Column-I                   Column-J                    Column-K
echo f | xcopy /f /y    \\server123\data\3838383.398   %userprofile%\Desktop\VT\3838383.398
echo f | xcopy /f /y       #VALUE!                      #VALUE!
echo f | xcopy /f /y       #VALUE!                      #VALUE!
echo f | xcopy /f /y       #VALUE!                      #VALUE!

I then use this formula to concatenate the columns I, J, K into column Z =CONCATENATE(I5,J5," ",K5)

This is where the magic happens, I have a macro that converts whatever is found in column Z into a windows .bat file. Here is the code for that:

Sub Send2Bat()

Dim ColumnNum: ColumnNum = 26   ' Column Z - I have the I J and K Columns concatenated there.
Dim RowNum: RowNum = 0
Dim objFSO, objFile
Dim openBat As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile("C:\Users\UserProfile\Desktop\VT\Batch Files\Convert.bat")    'Output Path
aFile = "C:\Users\UserProfile\Desktop\VT\Batch Files\"

Dim OutputString: OutputString = ""
Dim targetSheet As Worksheet
Set targetSheet = Application.Worksheets("ForBatchFile")

Dim LastRow: LastRow = targetSheet.Cells(targetSheet.Rows.Count, ColumnNum).End(xlUp).Row

Do

    RowNum = RowNum + 1

    If Not (IsEmpty(targetSheet.Cells(RowNum, ColumnNum).Value)) Then

        OutputString = OutputString & Replace(targetSheet.Cells(RowNum, ColumnNum).Value, Chr(10), vbNewLine) & vbNewLine

    End If

Loop Until RowNum = LastRow

objFile.Write (OutputString)

  Set openBat = CreateObject("Shell.Application")
  openBat.Open (aFile)

Set objFile = Nothing
Set objFSO = Nothing



End Sub

When I have all 30 of my lines filled out, everything works perfectly. My problem is when I do not and some of the cells contain the #VALUE! in them, it does not output the .bat file. I get a "Run-time error '13': type mismatch" error. This is somewhat expected because it's the spreadsheet concatenates all kinds of stuff with dates, text, and general formats. What I would like to know is... how can I change my macro so that it only processes the lines I need without erroring out. I've tried making my variables variant types to see if that helped, of course it did not. Furthermore, how could I handle the errors and just skip lines where the type mismatch is detected? I hope this is enough details, thanks to anybody who looks at this.

Answer 1

You can check for errors and only use the "good" rows:

Dim v As Variant

'....
'....

'when you already know the start and end values, use For...Next
for rownum = 1 to LastRow

    v = targetSheet.Cells(RowNum, ColumnNum).Value

    If Not IsError(v) Then  '<< skip any errors
        If Len(v) > 0 Then  '<< skip empty values
            OutputString = OutputString & Replace(v, Chr(10), vbNewLine) & vbNewLine
        End If
    End If

Next RowNum