I have spent far too much time trying to find a simple solution to the problem of checking to see if an Excel workbook is open. After following many dead ends and misleading posts I eventually hit on this website here:-
I found the code I needed, it was succinct, self explanatory and of the type/format I understand. I can't vouch for the rest of the code on this site, however going by the code I have used I should imagine it's very good!!!
Looks like a good site. I might reference your post with the endless questions about Excel automation from many users in the Programming forum.
As a former MCT with the specific certification for Excel Object Model Programming, let me suggest the following chart. During the 5 day Excel 97 VBA training, we used this kind of map to assist new programmers keep track of where they were at.
The other power tool available is the F2 Object Browser in the Code Module. http://www.excel-spreadsheet.com/vba/objectbrowser.htm
This is a very, very powerful tool with the Object Library at your fingertips.
It will vastly reduce any programmers frustration level if they take the time to review each object they program in the F2 Object Browser. For example: the difference between a Cell and a Range.
During the 5 day course, we started with seasoned programmers and then spent 4 intense hours on the Object Model, F2 Object Browser, Immediate Window, and the Debugger. The official course had it on day 3. But, ignorance in these tools held back the ability to learn.
You would be surprised how many people had been programming for years, yet had no or little idea about these tools.
Don't be like that Rob Lowe! (A Comcast pun about the successful Rob Lowe vs the looser Rob Lowe)
Do yourself a favor and learn these tools. BONUS: they also work in Access!
So you actually get a 2 for one bonus. Act now, and we will throw in Power Point and MS Word immediate window and debugging.
If you have questions, there are many on this forum who can help.
I have found a another interesting website for Excel. In the same project I mentioned in earlier posts, I was trying to blank out a set of cells in an Excel spreadsheet, well... it's actually it's an open document (Excel version) of a spreadsheet (*.ods) Again, one of the first examples I found seemed a bit long-winded and very slow.
Code:
Dim x As Integer
For x = 18 To 517
xlsheet.Range("C" & x).Value = ""
xlsheet.Range("D" & x).Value = ""
xlsheet.Range("E" & x).Value = ""
xlsheet.Range("F" & x).Value = ""
xlsheet.Range("G" & x).Value = ""
xlsheet.Range("H" & x).Value = ""
xlsheet.Range("I" & x).Value = ""
Next
I realized there might be a better way and my research that led me to this website:- Excel Easy The code:-
Code:
Dim rngGiftAid As Range
Set rngGiftAid = xlsheet.Range("C18:I517")
rngGiftAid.Value = vbEmpty
is obviously more compact as you can see; my guess is I am accessing 1 range instead of multiple ranges which gives a significant speed increase.
My rough one, two, three under the breath count gives me the impression that it is 4 times as fast using the more compact code.
I would be interested to know if there was a better way? It occurred to me that I am filling the spreadsheet with the following SQL statement:-
Code:
sSql = "SELECT Number_Name, ROAD1, POSTCODE, " & strTaxDate1 & " AS Expr1, GiftAidAmount AS Gift1, " & strTaxDate1 & " AS Expr3, GiftAidAmount as Gift2 FROM MEMBERS"
'////// For Gift Aid Start at "C18"
xlsheet.Range("C18").CopyFromRecordset Rs
I wondered if there was a way of writing an SQL statement that would fill a range of cells with a blank (or empty expression vbEmpty) I wondered if this would be possible?
I ask because I don't believe it is possible.. But I have seen some "What I considered Impossible things done with SQL!!!
RE:...gives me the impression that it is 4 times as fast using the more compact code.
You are off by just a little bit. It is about 8,000 times faster to use Array logic.
This example of your code would be about 7 times as fast. That is how many times the .Range didn't have to go through the COM interface XLSheet.Range
Code:
Dim x As Integer
For x = 18 To 517
with xlsheet
.Range("C" & x).Value = ""
.Range("D" & x).Value = ""
.Range("E" & x).Value = ""
.Range("F" & x).Value = ""
.Range("G" & x).Value = ""
.Range("H" & x).Value = ""
.Range("I" & x).Value = ""
end with
Next
For example, each time the xlsheet object is called, the Application Sheet object (actually just a pointer in memory thanks to the Set statement) must be penetrated (or crossed).
Plus, the loop has overhead.
This code must also be read and compiled by the Interperter layer.
Have you ever used someone to translate when conducting a transaction?
It is much slower. Think of an ATM machine that for each step in using it, it keeps asking for your PIN, your User name and what language youprefer.
The used above uses the Excel native object function that compiled (in ones and zeros). Think of Excel as a huge array of memory points with relative addressable connections.
The single statement is only one instruction penetrating (crossing) the boundry line talking to Excel in native binary language.
All of the Cells are associated as a set in the range. The range is set to empty. All in the Excel native language.
Likewise, copying a recordset over (with nulls or data) finds a starting point. Then it matches a cell at a time iwth an associated row, column.
While it might be in a stream. For blanking out cells, it is not as efficient.
Just to explain the point of choosing or justifying different vba for the purpose.
This code on a large set of data can takes minutes to run. But, the custom formatting is very useful the the end user. This steps through a record at a time then makes the adjusted formatting for a range of cells on that row.
Code:
2000 With objxl.ActiveWorkbook.ActiveSheet
'objxl.ActiveWorkbook.ActiveSheet
2010 For i = intRowPos To intMaxRecordCount + intRowPos
'If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
2020 If .Cells(i, "O").Value = 1 Then ' 1st sorted order for Lease type
2030 .Range(.Cells(i, "B"), .Cells(i, "J")).Font.FontStyle = "Bold"
2040 .Range(.Cells(i, "B"), .Cells(i, "J")).Borders(xlTop).ColorIndex = xlAutomatic
' must set back to automatic xince Else statement changes style
2050 .Range(.Cells(i, "B"), .Cells(i, "J")).Borders(xlTop).Weight = xlThick
2060 Else
2070 '.Range(.Cells(i, "B"), .Cells(i, "F")).Font.ColorIndex = 16 'metalic gray ' Gray was too light
2080 If .Cells(i, "O").Value = 4 Then
2090 .Range(.Cells(i, "G"), .Cells(i, "H")).Font.FontStyle = "Bold"
2100 End If
2110 End If
2120 Next i
2130 End With
When the Access data was imported into the spreadsheet, the True/False columns show up as -1/0. Well, that is no good for a user. So, this code changed about 80,000 cells in just a fraction of a second.
The difference is: The top code is running from MS Access and crossing the Application boundry one command at a time. It isn't the same as network traffic. But, it still represents a COM package that must be Marshalled across the Access Application to the Excel Application boundry. Once in Excel, the command must be received then compiled.
The second example is calling on a built-in Excel Function. There is only two commands from Access to Excel. The built-in function in Excel was wirtten in C++. The function is pre-compiled.
This is an example where both just do some formatting to some degree.
Now, the question. Could the first code in the loop have been written more efficiently to use Excel's built-in formatting and arrays?
The answer is actually yes it could. If this report was going into a wide distribution then the answer is Yes it should.
This code is part of a weekly managemnt Quality Assurance report.
So, in this case, the extra hours spent would never be recovered by the user's time.
So, when I said "don't do that", just remember that "it depends".
Your situation may be different.
Code maintenance also plays a big part in this decision.
In the detailed code shown above, the customer already added six changes to it.
