Back to Blog
Cell(row, column) is almost always better and is much easier to use in loops. Unless you have a really good reason not to do so, using. Not only do you have to concatenate strings in order to build the addresses, Excel just has to convert them back into numeric indexes. Third - Using Range addressing C2 or 元 is not only harder to read, it is less efficient as well. Nothing will make the wheels come off of a long function faster than the user doing something that changes the active Workbook or Worksheet in the middle of your execution. Second - Get a reference to the Worksheet object at the start of the function instead of relying on calls to ActiveSheet. I'm fine with it since it gets the job done, but I'm curious if there is any way to streamline it further and possibly add error checking (for name mismatches).įirst - In addition to replacing the magic numbers, I'd put your file path into a constant at the top of the module for easier maintenance: Private Const WORD_DOCUMENT_LOCATION As String = "C:\Users\\Desktop\CyberAwareness.docx" The script works surprisingly well, however, it is pretty slow and takes ~10 minutes for one column. I have about 300 personnel that need information entered into this document and about 8 different columns I need to populate. SearchWordDoc = Left(Right(a.Paragraphs(i).Range.Text, 22), 11) If InStr(a.Paragraphs(i).Range.Text, strName) 0 Then Set objword = CreateObject("word.application") 'Searches word file for name, finds the associated paragraph, and returns the date' Range(currentCellToAdd) = SearchWordDoc(filePath, firstPlusLastName) Sub SearchTextFile()įilePath = "C:\Users\\Desktop\CyberAwareness.docx"ĪctiveSheet.Range(currentCellToAdd).ActivateįirstPlusLastName = Range(currentCellNumberLname).Value
0 Comments
Read More
Leave a Reply. |