excel vba

Delete First line of Text file and Import -Excel vba

Delete First line of Text file and Import -Excel vba Select Folder that contains the text files. It reads each text file into an array and then builds a new array (that doesn’t include the first line of each text file) first way (not verified):  Sub SO() Const fileLocation As String = “C:\Users\BloggsJ\Folder\textFile.txt” Dim...

Replace specific line of text file using Excel VBA

Replace specific line of text file using Excel VBA It includes both the first line replacement, and the last 6 characters replacement. Sub GenerateACEQueries() 'Script sourced from 'http://www.excelbanter.com/showthread.php?t=427791 'http://www.ozgrid.com/forum/showthread.php?t=181679 Dim strFileNum As String Dim rCell As Range ‘Declarations for parsing first line in text file (opening date) Dim vSz As Variant, vFilename As Variant...

Wait/Delay in Excel VBA

Wait/Delay in Excel VBA Delaying the process for few seconds to hours in Excel VBA newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime   If Application.Wait(Now + TimeValue(“0:00:10”)) Then MsgBox “Time expired” End If https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-wait-method-excel

Range of data Copy and Paste in Excel VBA

Copy Paste range of data in Excel VBA Various ways to copy and paste the range of data. Sub Range_Copy_Examples() 'Use the Range.Copy method for a simple copy/paste     'The Range.Copy Method - Copy & Paste with 1 line     Range("A1").Copy Range("C1")     Range("A1:A3").Copy Range("D1:D3")     Range("A1:A3").Copy Range("D1")          'Range.Copy to other worksheets     Worksheets("Sheet1").Range("A1").Copy Worksheets("Sheet2").Range("A1")          'Range.Copy to...

Case statement in Excel vba

Case statement in Excel vba Select Case test_expression Case condition_1 result_1 Case condition_2 result_2 ... Case condition_n result_n End Select https://www.techonthenet.com/excel/formulas/case.php

Export range or sheet to PDF in Excel VBA

Export sheet to PDF in Excel VBA ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "c:\Book1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True Export Range in a sheet to PDF in Excel VBA Sheets("Sheet1").Range("A1:B1").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _ "c:\Book1.pdf", Quality:= _ xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _ OpenAfterPublish:=True  

Clear Non Printable Characters using Excel VBA

Clear Non Printable Characters using Excel VBA Sub CleanUpData()     Dim Ws As Worksheet     Dim Rng As Range, Cell As Range     Dim ArrCodes     Dim i As Long              Set Ws = ActiveSheet     On Error Resume Next     Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers + xlTextValues)     If Rng Is Nothing Then         Exit Sub     End If     On Error GoTo...

Find and Find Next in column using Excel VBA

Find text in column using Excel VBA Range(“A:A”).Select Set find_rng = Selection.Find(What:=find_text, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False) If Not find_rng Is Nothing Then           find_row=find_rng.row end if xlPart > xlwhole: to search for the whole matching text xlFormulas > xlValues: to search in value of the...

Send Emails with Outlook from Excel using Excel VBA

Send Emails with Outlook from Excel using Excel VBA Sub Email_program() On Error GoTo ErrHandler ‘ SET Outlook APPLICATION OBJECT. (Late Binding – Works for any excel version) Dim objOutlook As Object Set objOutlook = CreateObject(“Outlook.Application”) Dim objEmail As Object Set objEmail = objOutlook.CreateItem(olMailItem) With Sheets(“Data”) LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row End With Search_text =...

Published On

December 2018
M T W T F S S
« Nov    
 12
3456789
10111213141516
17181920212223
24252627282930
31