Archive for category: Excel

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 <> -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.chicagocomputerclasses.com/excel-classes/

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 & num
        Next num
End With

Debug.Print endresult

End Sub

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

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 & ":" & 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 & ":" & i).Delete Shift:=xlUp    
Next i

  Application.ScreenUpdating = True

End Sub

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

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

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