Before opening your workbook, you will need to verify if the workbook exists or not, so that it won’t return error or the VBA will stop improperly.
If the workbook exists, it will return “TRUE“.
If the workbook doesn’t exist, it will return “FALSE“.
Function DoesWorkBookExist(ByVal wbPath As String, ByVal WBName As String) As Boolean
Dim i As Integer
Dim FileCheck As String
FileCheck = wbPath & “\” & WBName
If Dir(FileCheck) <> “” Then
‘MsgBox (“Workbook – ” & wbName & ” exists”)
DoesWorkBookExist = True
‘MsgBox (“Workbook – ” & wbName & ” doesn’t exist”)
DoesWorkBookExist = False
It is a subroutine and can be executed directly via the Macro list. The function is to modify delimited text into separate columns, so you can manipulate data better and easily.
objRange1 is the definition of the range within a worksheet, you can specify.
Destination:=Range(“A3”) is the location where the output will be appeared, you can specify.
Dim objRange1 As Range
‘Set up the ranges
Set objRange1 = Range(“A1:A20”)
‘Do the first parse
This is a function, to convert the Column in number into the Column Letter, in order to work with most Range, functions in VBA, the Column Letter will be very useful than the Column number.
e.g. Column “A” = Column number 1
Column “C” = Column number 3
Function ConvertToLetter(ByVal iCol As Integer) As String
If iCol <= 26 Then
‘ Columns A-Z
ConvertToLetter = Chr(iCol + 64)
If iCol > 26 Then
‘ Column label greater than Z, going into AA…
ConvertToLetter = Chr(Int((iCol – 1) / 26) + 64) & Chr(((iCol – 1) Mod 26) + 65)
This is a custom function in Excel VBA, where you can find out the path or location of your workbook; it returns the information as a text output and you can make use of this for further programming,
Function DetectPath(ByVal WBName As String) As String
DetectPath = Workbooks(WBName).Path
This is a custom function, to delete certain row(s) based on your specified Column and Row, then choose to delete the subsequent rows together in one go.
Function DeleteRows(ByVal validColumn As String, ByVal StartRow As Long, ByVal NumOfRows As Long)
Dim i As Long
Dim DelString As String
DelString = validColumn & StartRow & “:” & validColumn & NumOfRows
OpenResult = DeleteRows(“A”, 1, 9)