MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.
' Open the TOAD Excel Spreadsheet Dim strPsoftExtract strPsoftExtract = "Y:\HT_BI_HEE_EXTRACT_V2.xls" Set objExcel = CreateObject("Excel.Application") Set objWorkbook = objExcel.Workbooks.Open(strPsoftExtract) Dim frmla Dim i Dim rg objExcel.ScreenUpdating = False 'Insert column headers in col Z objExcel.Cells(1, 25).Value = "ORG UNIT1 NAME" With objExcel.ActiveSheet Set rg = .Range("Y2") Set rg = .Range(rg, .Cells(.Rows.Count, rg.Column).End(-4162)) 'xLUp = -4162 'This inserts the VLOOKUP formula into cell Z2, AA2. For i = 1 To 2 frmla = "=VLOOKUP($Y2,'J:\[ManagerLevels.xls]sheet1!$A$2:$D$16," & (2 * i) & ",FALSE)" .Range("Z2").Offset(0, i - 1).Formula = frmla Next End With 'This copies the formula all the way down columns Z and AA. 'and then replaces the formula result with the value only (like doing Paste Special --> Values) With rg.Offset(0, 25).Resize(, 2) .FillDown .Formula = .Value End With objExcel.ScreenUpdating = True ' Retrieve the separate Date components. Dim TodayYYYY, TodayMM, TodayDD TodayYYYY = Year(Date) TodayMM = Month(Date) TodayDD = Day(Date) ' Save the sheet - appending TodaysDate to the end of the file-name. objWorkbook.SaveAs "Y:\HT_BI_HEE_EXTRACT_V2_" & TodayYYYY & "-" & TodayMM & "-" & TodayDD & ".xls" ' Delete the original Psoft Extract Set objFSO = CreateObject("Scripting.FileSystemObject") objFSO.DeleteFile strPsoftExtract, True ' Close Excel with the Quit method on the Application object. objWorkbook.Application.Quit ' Release the object variable. Set objExcel = Nothing
|VbScript to countdown to New Year's Day||6||77|
|how to loop through and process two columns in excel||8||46|
|VBA - Transfer values from Master wb to all wbs. Delete old data, copy new data from master wb to user wbs located in a folder.||3||37|
|how to read the text file and convert it to csv.||5||35|