Excel Archives - Page 2 of 3 - Houston Computer Classes

This VBA code will loop though all the text files in a directory that user picks from an application box and print a list of them. This code can be modified to be used in many scenarios for any file type.

Sub LoopThroughAllTextFilesInFolder()

    'Declarations
        Dim wb As Workbook
        Dim DirPath As String
        Dim FileName As String
        Dim FileExt As String
        Dim UserFolderChoice As FileDialog
        Dim i As Integer
        Dim howmany As Integer
    'Declarations End

    'Regular Stuff
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
    'Regular Stuff End

    'User's Folder Choice
        Set UserFolderChoice = Application.FileDialog(msoFileDialogFolderPicker)

        With UserFolderChoice
          .Title = "Select A Target Folder"
          .AllowMultiSelect = False
            If .Show &lt;&gt; -1 Then GoTo AllGood
            DirPath = .SelectedItems(1) &amp; "\"
        End With
    'User's Folder Choice End

AllGood:

    'Check if path is empty
      If DirPath = "" Then GoTo JumpToEnd

    'File Extension
      FileExt = "*.txt"

    'Let's find the first file &amp; how many total files we have
      FileName = Dir(DirPath &amp; FileExt)
      howmany = Len(Dir(DirPath &amp; FileExt))

        'let's loop though them
            For i = 1 To howmany

                'print the name of the file
                Debug.Print FileName

                'move to the next file
                FileName = Dir
            Next i

'Done!!!

JumpToEnd:

    'Regular Stuff
        Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    'Regular Stuff End

End Sub

Sub LoopThroughAllTextFilesInFolder()

'Declarations

Dim wb As Workbook

Dim DirPath As String

Dim FileName As String

Dim FileExt As String

Dim UserFolderChoice As FileDialog

Dim i As Integer

Dim howmany As Integer

'Declarations End

'Regular Stuff

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.Calculation = xlCalculationManual

'Regular Stuff End

'User's Folder Choice

Set UserFolderChoice = Application.FileDialog(msoFileDialogFolderPicker)

With UserFolderChoice

.Title = "Select A Target Folder"

.AllowMultiSelect = False

If .Show <> -1 Then GoTo AllGood

DirPath = .SelectedItems(1) & "\"

End With

'User's Folder Choice End

AllGood:

'Check if path is empty

If DirPath = "" Then GoTo JumpToEnd

'File Extension

FileExt = "*.txt"

'Let's find the first file & how many total files we have

FileName = Dir(DirPath & FileExt)

howmany = Len(Dir(DirPath & FileExt))

'let's loop though them

For i = 1 To howmany

'print the name of the file

Debug.Print FileName

'move to the next file

FileName = Dir

Next i

'Done!!!

JumpToEnd:

'Regular Stuff

Application.EnableEvents = True

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

'Regular Stuff End

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

* 1 student is allowed per tutoring session. If you need more participants rate changes will apply and we need to be notified beforehand.

* The client must provide suitable and safe work environment for the tutor to be able to provide the training.

VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

May 14, 2015/in Excel, Reference, Tutorial /by admin

The following procedure will take any numbers within text and increase them by 1.

For Example:
For20Example51Text will transform into For21Example52Text

Sub UpNumbersByOne()

cell = "YOUR16STRING1HERE10"

Dim v As Variant
 endresult = ""

With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+|\D+)"
    .Global = True
        v = Split(Mid(.Replace(cell, "|$1"), 2), "|")
        For Each num In v
        If IsNumeric(num) = True Then
        num = num + 1
        End If
        endresult = endresult &amp; num
        Next num
End With

Debug.Print endresult

End Sub

Sub UpNumbersByOne()

cell = "YOUR16STRING1HERE10"

Dim v As Variant

endresult = ""

With CreateObject("VBScript.RegExp")

.Pattern = "(\d+|\D+)"

.Global = True

v = Split(Mid(.Replace(cell, "|$1"), 2), "|")

For Each num In v

If IsNumeric(num) = True Then

num = num + 1

End If

endresult = endresult & num

Next num

End With

Debug.Print endresult

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

VBA – Get First Number & Then Text & Following Number – Excel Macro

May 14, 2015/in Excel, Reference, Tutorial /by admin

This Excel Macro will transform the text data placed in column A and Break it down into 3 Columns.

Data Format Required:
15654 Text goes Here 16 2 6 1595
1 Different Text 1 6
number (space) text of any lenght (space) number (space) other characters

Output will return:

First column: full first number
Second column: full text string in the middle of surrounded numbers
Third Column: first full number after text

Sub BreakTextNum()
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For Each cell In Range("a1:a" &amp; lr)

    'grab teh number
    num = Left(cell, InStr(cell, " ") - 1)

    'grab teh text
        For i = 1 To Len(cell)
            Dim currentCharacter As String
            currentCharacter = Mid(cell, InStr(cell, " ") + i, 1)
            If IsNumeric(currentCharacter) = True Then
                GetPositionOfFirstNumericCharacter = i
                Exit For
            End If
        Next i

    txt = Mid(cell, InStr(cell, " ") + 1, i - 2)
    'grab teh after number
    lasti = i + InStr(cell, " ") - 2
    anum = Mid(cell, lasti + 2, InStr(lasti + 2, cell, " ") - lasti - 2)

    Cells(cell.Row, 2) = num
    Cells(cell.Row, 3) = txt
    Cells(cell.Row, 4) = anum

    Next cell

End Sub

Sub BreakTextNum()

lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

For Each cell In Range("a1:a" & lr)

'grab teh number

num = Left(cell, InStr(cell, " ") - 1)

'grab teh text

For i = 1 To Len(cell)

Dim currentCharacter As String

currentCharacter = Mid(cell, InStr(cell, " ") + i, 1)

If IsNumeric(currentCharacter) = True Then

GetPositionOfFirstNumericCharacter = i

Exit For

End If

Next i

txt = Mid(cell, InStr(cell, " ") + 1, i - 2)

'grab teh after number

lasti = i + InStr(cell, " ") - 2

anum = Mid(cell, lasti + 2, InStr(lasti + 2, cell, " ") - lasti - 2)

Cells(cell.Row, 2) = num

Cells(cell.Row, 3) = txt

Cells(cell.Row, 4) = anum

Next cell

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

VBA – Delete Every Other Row – Odd or Even – Excel Macro

April 23, 2015/in Excel, Reference, Tutorial /by admin

This Excel Macro will go through all the cells in column A, find the last cell that has data in it, and then delete every other row in that data range.

To Delete Odd Rows

Sub Delete_Odd()

 Application.ScreenUpdating = False
lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then
lr = lr - 1
End If

For i = lr To 1 Step -2
    Rows(i &amp; ":" &amp; i).Delete Shift:=xlUp    
Next i

 Application.ScreenUpdating = True

End Sub

Sub Delete_Odd()

Application.ScreenUpdating = False

lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then

lr = lr - 1

End If

For i = lr To 1 Step -2

Rows(i & ":" & i).Delete Shift:=xlUp

Next i

Application.ScreenUpdating = True

End Sub

To Delete Even Rows

Sub Delete_Even()

 Application.ScreenUpdating = False
lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then
Else
lr = lr - 1
End If

For i = lr To 1 Step -2
    Rows(i &amp; ":" &amp; i).Delete Shift:=xlUp    
Next i

  Application.ScreenUpdating = True

End Sub

Sub Delete_Even()

Application.ScreenUpdating = False

lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then

Else

lr = lr - 1

End If

For i = lr To 1 Step -2

Rows(i & ":" & i).Delete Shift:=xlUp

Next i

Application.ScreenUpdating = True

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

VBA – Letter Count for Each Alphabet Character – Excel Macro

April 16, 2015/in Excel, Reference, Tutorial /by admin

This Excel Macro will go through all the cells in column A and output the count for each letter in column B. This is for English alphabet only and it will skip all the other characters and spaces.

Sub LetterCount()

Dim ws As Worksheet
Set ws = ActiveSheet

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

rg = Range("a1:a" &amp; lastrow)
i = 1

For c = 97 To 122

cnt = 0

    For Each cell In rg

    lth = Len(cell)
        For lt = 1 To lth

            chktext = Mid(cell, lt, 1)

              If chktext = Chr(c) Then

                cnt = cnt + 1
            End If

        Next lt

    Next cell

    If cnt &gt; 0 Then
        ws.Cells(i, 2) = Chr(c) &amp; " = " &amp; cnt
        i = i + 1
    End If

Next c

End Sub

Sub LetterCount()

Dim ws As Worksheet

Set ws = ActiveSheet

lastrow = Cells(Rows.Count, 1).End(xlUp).Row

rg = Range("a1:a" & lastrow)

i = 1

For c = 97 To 122

cnt = 0

For Each cell In rg

lth = Len(cell)

For lt = 1 To lth

chktext = Mid(cell, lt, 1)

If chktext = Chr(c) Then

cnt = cnt + 1

End If

Next lt

Next cell

If cnt > 0 Then

ws.Cells(i, 2) = Chr(c) & " = " & cnt

i = i + 1

End If

Next c

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

Excel VBA – Export Each Worksheet to a Separate PDF – Macro

March 25, 2015/in Excel, Reference, Tutorial /by admin

If you need to Export Each sheet to an individual .pdf file this Macro will do it for you.

It will go through all the sheets in your Workbook and save each one to a separate PDF file using the worksheet name as file name.

Sub ExportToPDFs()
' PDF Export Macro
' Change C:\Exports\ to your folder path where you need the diles saved
' Save Each Worksheet to a separate PDF file.

Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Exports\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

Sub ExportToPDFs()

' PDF Export Macro

' Change C:\Exports\ to your folder path where you need the diles saved

' Save Each Worksheet to a separate PDF file.

Dim ws As Worksheet

For Each ws In Worksheets

ws.Select

nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _

Filename:="C:\Exports\" & nm & ".pdf", _

Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

Excel – Age Calculation – How to Get Age from Date of Birth (DOB)

March 17, 2015/in Excel, Reference, Tutorial /by admin

The easiest way to calculate person’s age from using their birthday in Excel is by utilizing undocumented DATEDIF function. DATEDIF is a built-in Excel function, however, unlike all the other built-in functions it will not auto-populate, show up in auto-complete or give tooltips on the function.

So how do you use DATEDIF function?

The syntax for the function is as follows:

DATEDIF([start-date],[end-date],[return type])

To calculate person’s age using datedif function you can use the following formula:

Assumptions:

A1 – the cell where you have person’s birthrate

Enter the formula where you want to output the age:

=DATEDIF(A1,TODAY(),”Y”)

If you do not want to use the dynamic TODAY() function you may reference to a call with a date on which you would like to calculate the age.

Assumptions:

A1 – the cell where you have person’s birthrate
B1 – the date when you want to calculate teh age

Enter the formula where you want to output the age:

=DATEDIF(A1,B1,”Y”)

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

VBA – Loop Through All (.txt) Files in a User Specified Directory – Excel Macro

VBA – Get Information From a Web Page – Excel Macro – Amazon Example

Microsoft Excel VBA Training – Introduction to Macros

Microsoft Excel VBA Class – Introduction to Macros

Not Just Teachers

OnSite Training

Not Enough?

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Request

Get Confirmation

Done!

Microsoft Excel Class – Advanced

Microsoft Excel Class – Advanced

Not Just Teachers

OnSite Training

Not Enough?

Request a Session

Questions?

(312) 818-5515

How Does it Work?

Request

Get Confirmation

Done!

VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

VBA – Get First Number & Then Text & Following Number – Excel Macro

VBA – Delete Every Other Row – Odd or Even – Excel Macro

To Delete Odd Rows

To Delete Even Rows

VBA – Letter Count for Each Alphabet Character – Excel Macro

Excel VBA – Export Each Worksheet to a Separate PDF – Macro

Excel – Age Calculation – How to Get Age from Date of Birth (DOB)

So how do you use DATEDIF function?

To calculate person’s age using datedif function you can use the following formula:

Locations: