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