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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
|remove computer from using logon script||17||52|
|why do i get this error of invalid procedure or argument in this VBA?||6||43|
|Copy Sheet to New Workbook based on Cell Value||6||57|
Join the community of 500,000 technology professionals and ask your questions.