![]() Now that you are fully aware of the problem, it's time to work out a solution. So, you may be racking your brain trying to figure out why an obviously correct formula cannot match two seemingly identical entries. The point is that two cells containing the same text with and without spaces, even if it's as little as a single space character, are deemed different values. It wouldn't be much of a problem if extra spaces were just lying around, but they mess up your formulas. But there is no way to spot trailing spaces, those that keep out of sight at the end of cells. ![]() An attentive user can occasionally catch a leading space hiding before the text or a few extra spaces between words. ![]() What's the biggest problem with spaces? They are often invisible to the human eye. You will learn how to delete leading and trailing spaces in a cell, eliminate extra spaces between words, get rid of non-breaking white space and non-printing characters. tutorial explains how to remove blank spaces in Excel using formulas and the Text Toolkit tool. WDoc.Protect wdAllowOnlyFormFields, NoReset:=True, Password:="" If wDoc.ProtectionType = wdAllowOnlyFormFields Then wDoc.Unprotect Sub FixHighlights(wApp As Word.Application, wDoc As Word.Document) RngOutput.Cells(z + 1, i + 2).Font.Color = lColor(i) RngOutput.Cells(z + 1, i + 2).Value = strOutput ![]() StrOutput = WorksheetFunction.Trim(WorksheetFunction.Clean(wRange(i).Text)) RngOutput.Cells(z + 1, 1).Value = wordDoc.Name LColor(x) = wordDoc.Tables(lTable(1)).Cell(i, 2). Set wRange(x) = wordDoc.Tables(lTable(1)).Cell(i, 2).Range LColor(x) = wordDoc.Tables(lTable(1)).Cell(19, i). Set wRange(x) = wordDoc.Tables(lTable(1)).Cell(19, i).Range Set wRange(i - 1) = wordDoc.Tables(lTable(0)).Rows(i).Cells(2).Range Set wRange(1) = wordDoc.Tables(lTable(0)).Rows(3).Cells(1).Range Set wRange(0) = wordDoc.Tables(lTable(0)).Rows(3).Cells(3).Range Set wordDoc = (aFold.Path & Application.PathSeparator & aFile.Name) If InStr(1, aFile.Name, "~") > 0 Or InStr(1, aFile.Name, "Importer") Then GoTo SkipLoop Code is below for entire script: Public Sub ImportWordData(folderPath As String)ĭim aFold As Scripting.Folder, aFile As Scripting.Fileĭim lColor(0 To 20) As Long, lBColor(0 To 20) As Longĭim i As Long, x As Long, y As Long, z As Long The first picture is the results in Excel, so look at Lab 23 which has a date in yellow font, and compare it to the second picture of Lab 23 which appears in blue font. As you can see in the pictures, the Word document doesn't show any sign of yellow font. The most likely causes would be that the font was colored in those shades in the origin file or that there isn't any cleanup of formats being done before the macro is run again, but I've checked to make sure those aren't the case. Most of this comes out looking correct, but some comes out with yellow or orange font. I'm using VBA to import their data and the color of their font from their submitted documents. Students fill out some tables in Word, and they must occasionally use a certain color font to get full credit. I posted this a while back on Super User, but never got a response.
0 Comments
Leave a Reply. |