VBA Read Text File into Excel

Read a text file into Excel using VBA.  Bring the text from the textfile into excel.  Update file path with your textfiles path.

 

Dim FileNum As Integer
Dim DataLine, DataString As String
FileNum = FreeFile()
Open "Z:\home\Downloads\JSON\JSON.json" For Input As #FileNum
While Not EOF(FileNum)
Line Input #FileNum, DataLine
DataString = DataString & DataLine
Wend
Close #FileNum
Cells(1, 1).Value = DataString

Nothing found.


VBA RegEx get Email Address from value

VBA to extract an email address from a string value.  Uses RegEx to find the email pattern following string@email.com format.  Will find any email that matches that pattern and extract the email address.

 

checkValue = "Dear Dylan, How are you?  dylan@gmail.com"
Dim varResults As Object
Dim varEach
Dim lng As Long
Set regEx = CreateObject("vbscript.RegExp")
regEx.Pattern = "(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|""(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*"")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])"
regEx.IgnoreCase = True 'True to ignore case
regEx.Global = True 'True matches all occurances, False matches the first occurance
If regEx.Test(checkValue) Then
Set varResults = regEx.Execute(checkValue)
For lng = 1 To varResults.Count
ExtractEmail = ExtractEmail & varResults.Item(lng - 1).Value & "|||"
Next
ExtractEmail = Left(ExtractEmail, Len(ExtractEmail) - Len("|||"))
ExtractEmail = Join(Split(ExtractEmail, "|||"), ", ")
End If
Cells(i, 2).Value = ExtractEmail
ExtractEmail = ""

VBA Loop Visible Cells

VBA to loop the visible cells only.  Used for when you filter a table and then want to check the visible cells only.

'Loop visible Rows

Set Rng = ActiveSheet.UsedRange
For Each cl In Rng.SpecialCells(xlCellTypeVisible).Rows
MsgBox Cells(cl.Row, cl.Column).Value & cl.Row
Next cl

 

'Loop all visible cells

Set Rng = ActiveSheet.UsedRange
For Each cl In Rng.SpecialCells(xlCellTypeVisible)
MsgBox Cells(cl.Row, cl.Column).Value & cl.Row
Next cl

VBA Kill Task

VBA to kill task in task manager by process name.  You need to lookup the process name in task manager so you can tell excel which task to kill.  In the below example Excel is killing task Max.exe process.

 

Function TaskKill(sTaskName)
TaskKill = CreateObject("WScript.Shell").Run("taskkill /f /im " & sTaskName, 0, True)
End Function
TaskKill "Max.exe"

VBA Change Default File Prompt Folder

VBA Change Default File Prompt Folder

Change the default folder that opens when VBA prompts user to select a file.  First you need to change to the Drive where the folder is stored. Below example is using X: drive location. Then change directory to the folder you want to open for the user.

ChDrive "X:\"
ChDir "X:\Data\Dev\Files"

How to Remove Password on Locked Excel

How to Unlock a Locked Excel File

To remove the excel workbook or worksheet password requires opening the excel file as xml file and removing the password tags in the xml.

Unlock Password protected sheets/workbook

1)      Open excel file as zip in 7 Zip – open as archive

2)      Download Sheet.xml or workbook.xml

3)      Open xml file in text editor

4)      Delete the entire tag that begins with <sheetProtection password..../> or <workbookProtection..../>

5)      Transfer xml file back to 7 Zip archive folder

6)      Sheets or workbook should now be unlocked

How to crack VBA password

To remove the password locking the VBA of an excel file requires opening the excel in 7zip and editing one of the .bin files to recode the part that designates whether the file is password protected of not.  This has to be done in a hex editor.

Unlock VBA excel

1)      7 zip open xlsm file as archive

2)      Navigate xl folder

3)      Download vbaProject.bin file

4)      Open vbaProject.bin file in text editor to view hex code

5)      Find the hex code for DPB (use text to hex converter,  DPB hex = 5042)

6)      Update hex code 5042 (DPB) to 5078 (DPx)

  1. DPB = password protected vba
  2. DPx = No password on VBA

7)      Save new vbaProject.bin file

8)      Copy new vbaProject.bin file back to 7 zip xl folder

9)      Open excel file – will show DPx error message, click okay

10)   Go to VBA editor – Tools – VBA project properties – Protection – Disable lock project for viewing

11)   Save file

12)   Re open file – should now be unlocked


VBA to Apply Auto Filter

VBA to apply a filter on the table. Will hide rows that don't match the filter criteria.
Add <> in front of the FilterTerm to filter out that word.
Add * around FilterTerm to make wildcard search, filters if that word is contained within the cell.

Apply Auto Filter with 1 criteria

rws = ActiveSheet.UsedRange.Rows.Count
Set FilterRng = ActiveSheet.Range("A1:Z" & rws)
FilterRng.AutoFilter Field:=1, Criteria1:="FilterTerm"

Apply Auto Filter with multiple criteria

rws = ActiveSheet.UsedRange.Rows.Count
Set FilterRng = ActiveSheet.Range("A1:Z" & rws)
FilterRng.AutoFilter Field:=1, Criteria1:=Array( _
"FilterTerm1", "FilterTerm2"), Operator:=xlFilterValues

Apply Auto Filter with 1 criteria to show does not contain FilterTerm

rws = ActiveSheet.UsedRange.Rows.Count
Set FilterRng = ActiveSheet.Range("A1:Z" & rws)
FilterRng.AutoFilter Field:=1, Criteria1:="<>FilterTerm"


VBA Delete Visible Cells

VBA to delete the visible cells on the worksheet but will leave the top row header. Usually used after you have applied a filter and need to remove only the visible cells but not the hidden filtered rows.

ActiveSheet.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=x1Up

VBA to delete the visible cells on the worksheet.

ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=x1Up


VBA Text to Columns

VBA for Text to Columns function. Used when multiple columns are in 1 cell seperated by a delimiter. Common delimiter values are comma or pipe |. Text to columns separates columns based on the delimiter.

'Replace OtherChar:= "with the delimiter character"
 Selection.TextToColumns Destination:=ActiveCell.Offset(0, 1), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
 ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, _
 OtherChar:="]"

VBA Delete Empty Worksheets

VBA to loop all worksheets in active workbook and delete any empty sheets.

Application.DisplayAlerts = False
For Each sht In ActiveWorkbook.Worksheets
   If WorksheetFunction.CountA(sht.Cells) = 0 And ActiveWorkbook.Sheets.Count > 1 Then
      sht.Delete
   End If
Next sht
Application.DisplayAlerts = True