ExcelSujeet (YouTube Blogger)

 Sometimes we have to separate multiple row data from a cell and the data in a cell is separated by line breaks. We can do this using the text-to-columns feature available in excel.    

 Pivot Table - Assuming the Data and the Pivot table both are on Sheet1 in the workbook. Sub PivotTableChart() Dim pc As PivotCache Dim pt As PivotTable Dim lrow As Integer Dim lcol As Integer Dim rng As Range ThisWorkbook.Activate Sheet1.Activate lrow = Cells(Rows.Count, 1).End(xlUp).Row lcol = Cells(1, Columns.Count).End(xlToLeft).Column 'Debug.Print lrow & "-" & lcol Set rng = Range(Cells(1, 1), Cells(lrow, lcol)) 'Debug.Print rng.Address Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, rng, 6) Set pt = pc.CreatePivotTable(Sheet1.Range("z5"), "MyPivot") With Sheet1.PivotTables("MyPivot")     With .PivotFields("Location")             .Orientation = xlRowField             .Position = 1     End With      '    .AddDataField Sheet1.PivotTables("MyPivot").PivotFields("InsuredValue"), _ '    "Sum of InsuredValue", xlSum     With .PivotFields("Insured...

How to install Selenium? Click the below link to know the steps. https://learnexcelwithsujeet.blogspot.com/2021/05/steps-to-install-and-use-selenium-for.html Example-1 Sub test() Dim ch As Selenium.ChromeDriver Dim ts As Selenium.WebElements Set ch = New Selenium.ChromeDriver ch.Start ch.Get "https://www.indiatoday.in/education-today/gk-current-affairs/story/indian-states-with-highest-population-1358414-2018-10-08" Set ts = ch.FindElementsByCss("table") Debug.Print ts.Count ts(1).AsTable.ToExcel ThisWorkbook.Sheets(1).Range("a1") End Sub

 Example-1 Multiselect:=True, let you select multiple files in one go and store the location of all files in a variable and let you open one file or workbook at one time. Note: It is important to note that the type of variable holding file or files name should be Variant. Sub OpenFile() On Error Resume Next Dim str As Variant Dim i As Integer str = Application.GetOpenFilename(MultiSelect:=True) Debug.Print str For i = LBound(str) To UBound(str) Debug.Print str(i) 'Workbooks.Open str(i) 'Debug.Print ActiveWorkbook.Name 'ActiveWorkbook.Close Next End Sub

  Sub test() 'MkDir "D:\Test\" 'MkDir "D:\Rust\" 'FileCopy "D:\Test\txt.txt", "D:\Test\txt-2.txt" 'Kill "D:\Test\txt-2.txt" Name "D:\Test\txt.txt" As "D:\Rust\txt.txt" End Sub Sub Movefile() Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject fso.Movefile "D:\Rust\txt.txt", "D:\Test\txt.txt" End Sub Create Text File and Read Data in Excel Line by Line. Sub CreateTextFile() Dim fso As Scripting.FileSystemObject Set fso = New Scripting.FileSystemObject Dim txt As Scripting.TextStream Dim i As Integer i = 1 Set txt = fso.CreateTextFile("D:\Test\txt-3.txt", True) txt.WriteLine "This is first line" txt.WriteBlankLines (1) txt.WriteLine "This is second line" 'Set txt = fso.CreateTextFile("D:\Text\txt-4.txt", True) Set txt = fso.OpenTextFile("D:\Test\txt-3.txt") Do Until txt.AtEndOfStream Cells(i, 1) = txt.Re...

Set Default Download Location ch.SetPreference "download.default_directory", "Put here default download location, like  C:\TestFolder "  Open Google Chrome in Maximized Mode ch.AddAgrument "start-maximized" Open Google Chrome in Kiosk Mode ch.AddAgrument "--Kiosk"

 Sometimes we come across some dates which are formatted in US date format or in text format. When we try to find the Day, Month or Year out of it, we get an error. Usually, we use the excel Text-To-Column feature to format the date column with default options ( Delimited , not Fixed width ). This works for dates less than the 13th day of the month but not for dates greater than the 12th day of the month. Sometimes this becomes very painful especially if you are working on something which has to be delivered in a short span of time. In this situation, we need to dig deep into the Text-To-Column feature. In step 3, under Column data format, in Date,  we should select either DMY or MDY to format the dates in the correct format. We may do this using VBA also and the codes are as follows: Let's say, our data is present in Column1 in Sheet1. 1) For Normal Format (Just Next-Next, default options) Selection.TextToColumns Destination:=Range("A2") , DataType:=xlDelimited, _   ...