Getting around VBA type mismatch for .Bat generated in Excel

by shadoe2020   Last Updated May 15, 2019 16:26 PM - source

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 one 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

        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.

Answers 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
Tim Williams
Tim Williams
May 15, 2019 16:21 PM

Related Questions

Median/average does not return the right values

Updated April 20, 2015 01:11 AM

How to make sums from a cell in another column?

Updated October 10, 2017 00:26 AM

Excel INDEX & MATCH using multiple criteria

Updated July 26, 2018 21:26 PM