I’m porting an application to Excel 2007 and thought I’d share some problems I’ve run into. First, there’s FileSearch. I use FileSearch to read CustomDocumentProperties from closed Excel files. That way I can determine if the file is a part of my app and relevant to the context before I open it up. FileSearch was discontinued in Excel 2007. I’ve heard it’s because there was some bugs that would cost more to fix than it was worth. Now I simply open the file to read the CDPs. It’s not as efficient if the user opens the wrong file, but for a correct file, it’s faster. Instead of reading the properties then opening the file, I just open the file. Reading properties from an already opened file has to be quicker that FileSearch.
Once I eliminated the reference to FileSearch, I could actually open my add-ins and test them. In one procedure I insert a Picture object onto a sheet with code similar to this:
Set pic = sh.Pictures.Insert(sPICFILENAME)
I’m very vocal about my objections to selecting ranges. It’s almost never necessary. So why do I do it here? Good question. In Excel 2003, the Insert method will place the picture at the ActiveCell. In 2007, it seems to place the picture in the same place on the sheet regardless of the ActiveCell. So instead of Selecting, I change the code to something like:
pic.Top = sh.Range(“E43”).Top
pic.Left = sh.Range(“E43”).Left
That also works in 2003, so there was really no need to select anything.
The last problem I encountered was with the UsedRange property. We all know how Excel can lose track of where the last cell is and give you a UsedRange bigger than actual. I have one sheet where I loop through all the cells. I never add or delete rows or columns and the UsedRange is never wrong. Even if it was wrong by a few rows or columns, it wouldn’t impact performance. So I iterated through the cells in UsedRange.
When I opened the template in Excel 2007 the UsedRange was huge. The last cell was something like CW1024 when it should have been G40. If I open it in 2003, no problem. I stopped using UsedRange and rolled my own.
Dim lLastRow As Long
Dim lLastCol As Long
lLastRow = 1: lLastCol = 1 ‘in case the sheet is empty
‘Finding “*” will find anything. I look backward from the first
‘cell to get the last cell with anything in it
On Error Resume Next
lLastRow = sh.Cells.Find(“*”, sh.Cells(1), , , xlByRows, xlPrevious).Row
lLastCol = sh.Cells.Find(“*”, sh.Cells(1), , , xlByColumns, xlPrevious).Column
On Error GoTo 0
Set GetUsedRange = sh.Cells(1).Resize(lLastRow, lLastCol)
End Function
This doesn’t give the same results as UsedRange. This will always start at A1, whereas UsedRange will show the smallest rectangle that includes all non-empty cells and that may start somewhere other than A1.
Yeah, there are a bunch of “gotchas” that one runs into when converting to 2007.
I haven’t encountered the UsedRange problem you describe. But, given that you can find the real last cell what would happen if you used Intersect(UsedRange, getUsedRange())? Or changed getUsedRange to return Range(UsedRange.cells(1),{lastcell})
Good one Tushar. I could easily limit it by intersecting with UsedRange. I wrote this anyway
Dim lLastRow As Long
Dim lLastCol As Long
Dim lFirstRow As Long
Dim lFirstCol As Long
Dim rFirstCell As Range
lFirstRow = 1: lFirstCol = 1
lLastRow = 1: lLastCol = 1 ‘in case the sheet is empty
‘Finding “*” will find anything. I look backward from the first
‘cell to get the last cell with anything in it
On Error Resume Next
If Not IsEmpty(sh.Range(“A1”).Value) Then
Set rFirstCell = sh.Range(“A1”)
Else
Set rFirstCell = sh.Cells.Find(“*”, sh.Cells(1), , , , xlNext)
End If
lFirstRow = rFirstCell.Row: lFirstCol = rFirstCell.Column
lLastRow = sh.Cells.Find(“*”, sh.Cells(1), , , xlByRows, xlPrevious).Row
lLastCol = sh.Cells.Find(“*”, sh.Cells(1), , , xlByColumns, xlPrevious).Column
On Error GoTo 0
Set GetUsedRange = rFirstCell.Resize(lLastRow – lFirstRow + 1, lLastCol – lFirstCol + 1)
End Function
But I should have written this
Dim lLastRow As Long
Dim lLastCol As Long
lLastRow = 1: lLastCol = 1 ‘in case the sheet is empty
‘Finding “*” will find anything. I look backward from the first
‘cell to get the last cell with anything in it
On Error Resume Next
lLastRow = sh.Cells.Find(“*”, sh.Cells(1), , , xlByRows, xlPrevious).Row
lLastCol = sh.Cells.Find(“*”, sh.Cells(1), , , xlByColumns, xlPrevious).Column
On Error GoTo 0
Set GetUsedRange = Intersect(sh.Cells(1).Resize(lLastRow, lLastCol), sh.UsedRange)
End Function
Dick, about 10 years ago, I came across this:
DSOFILE.EXE is a self-extracting executable that provides an in-process ActiveX DLL for Visual Basic programmers to use in order to read or modify the Document Summary Properties for an OLE Structured Storage file. Because Word, Excel, and PowerPoint use OLE Structured Storage to save their files to disk, this DLL lets programmers view and change document properties without the need to open the
document in Office.
I still have this file, plus sample code, if you are interested. And yes, it retrieves custom properties.
You can use a library called DSOFile to read document properties of closed files. Search Google or MSDN for it.
Thanks for the DSOFile tip. I had enough trouble getting .xla file past IT. We thought getting them to accept a dll wasn’t worth the trouble. My understanding is that this file is not installed by default and I would have to distribute it. I hate dependencies.
Excel’s UsedRange includes cells with formatting other than the Normal style even if they have no contents. Maybe not a problem for you, but harder to locate.
Possibly more of a problem, Finding * won’t find cells that ‘contain’ only the label prefix ‘ but have nothing else or cells in which the formula =”” was converted to a value. Such cells also aren’t blank. They’re degenerate text constants.
More robust to use something like
On Error Resume Next
Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
If Not r Is Nothing Then
Set r = r.Areas(r.Areas.Count)
Set lc = r.Cells(r.Cells.Count)
End If
Set r = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
If Not r Is Nothing Then
Set r = r.Areas(r.Areas.Count)
If lc Is Nothing Then
Set lc = r.Cells(r.Cells.Count)
Else
Set r = ActiveSheet.Range(lc, r.Cells(r.Cells.Count))
Set lc = r.Cells(r.Cells.Count)
End If
End If
With ActiveSheet
If lc Is Nothing Then
Set r = .Range(“A1”)
Else
Set r = .Range(.UsedRange.Cells(1), lc)
End If
r is then the portion of the used range with contents.
The DSOFile does need to be distributed. Therefore I use it on my machine, but I let other people wait for their machines to open files to read the properties. It’s still good for saving a build number to a custom doc prop prior to creating a new installation exe, and it conveniently also works on pdf files.
As I recall, the Find method for usedrange also comes up short if there are merged cells at the edge of the range.
For the DSO thing see ttp://www.cpearson.com/excel/docprop.htm
‘Document Properties Of Closed Files DSO Version 2.0
‘The following code illustrates how to read document properties using DSO version 2.0.
‘You can download DSO Version 2.0 from Microsoft at
‘http://www.microsoft.com/downloads/details.aspx?FamilyID=9ba6fac6-520b-4a0a-878a-53ec8300c4c2&DisplayLang=en
Function DSOODP()
Dim DSO As DSOFile.OleDocumentProperties
‘ requires project to have reference to DSO OLE Document Properties Reader 2.0
Set DSO = New DSOFile.OleDocumentProperties
DSO.Open sfilename:=”F:dirfile.xls”
Debug.Print DSO.SummaryProperties.ApplicationName
Debug.Print DSO.SummaryProperties.Author
‘ lots of other properties, cannot be accessed by text of name in a variable
DSO.Close
End Function
@fzz:
I tried your alternate technique, but it did not find the actual used range – in my test example, there is only one cell in the first column in the last row. Your lc range found this cell and then returned the used range as just the first column. Only if the last cell is at the most rightmost edge of the range will the technique work – any other alternate approaches – I can not think of how to improve on yours.
Issue 4: I have a range named “rep4? which is perfectly fine in 2003. But with the expanded columns in 2007, REP4 is now a cell reference. Excel 2007 seems to fix this automatically by changing the name to “_rep4?, but it won’t check VBA code. A quick Find in the VBE determined that I wasn’t using that range name in code.
Normally, I prefix my range names, such as rngRep4 for a range reference or conRep4 for a constant value. For some reason I didn’t prefix this one.
@fzz
Also I just added
Set ActiveSheet.UsedRange = r
ActiveWorkbook.save
It did not reset the UsedRange (Ctr+End – still takes me to the wrong used range
Love your postings!
Based on an example of capturing commas in text in CSV files, I looked up the “instr” command. I noticed many of the commands for manipulating text also hav a “b” or “$” or both. Can someone explain the difference between “instr” and “instrb” or “rightB” and “rightB$”.