|
George Spears
Posts: 79
Joined: 2010-05-06
|
Newbie problem... at least I hope...
Delphi XE6, 32 bit, running against Excel 2013
Scenario... Using Ribbon Tab, I have added a series of button...
Each button does a similar task. One button allows user to remove spaces, one button removes HTML formatting, etc.
I have 2 different forms, one for each process, and they inherit from a common form. The common form, called BaseEdit sets up some default information; it shows a ComboBox, which holds the column headers, and has a PROCESS and Cancel button.
The user clicks my Ribbon button, and the descendant form shows MODALLY. OnShow, the Combo Box will get loaded with Column names. 99% of the time, this works great. RANDOMLY - at least it seems - There will be NO data in my combobox, but whatever CELL the user was on PRIOR to hitting my button gets additional text added. (Normally, something in the form of "Sheet1!B..."
At the point, NO code has been executed in my descendant form, only in the ancestor. Here are the applicable code snippets.
When the ribbon button is clicked...
var
aws: ExcelWorksheet;
myForm: TRemoveSPACEForm;
begin
ExcelApp.Connect;
aws := Self.ExcelApp.ActiveSheet as ExcelWorksheet;
myForm := TRemoveSPACEForm.Create(nil);
try
myForm.aws := aws;// Pass active Sheet to form variable
myForm.ShowModal;
finally
myForm.Free;
end;
When my descendant shows (OnShow event), the ancestor code runs...
it Basically loops through all the columns, and populates the ComboBox for the user to select the column of interest.
...
for i := 1 to LastCol do
begin
CellAddr := ExcelColIntToStr(i) + '1'; // returns A1, B1, C1, etc.
HeaderText := Trim(aws.Range[CellAddr, EmptyParam].Value); // Get the data from that cell
ThisCol := '(' + string(ExcelColIntToStr(i)) + ' ) ' + HeaderText; // Build a string of Column address + Content
CB.Items.Add(ThisCol); // Add this to the ComboBox
end;
This issue happens very infrequently. I cannot force it to do so. It happens on multiple descendant forms, prior to ANY user interaction on the descendant forms. I cannot find any pattern, other than the fact that when in occurs, my combo box is empty, and there is "corruption" in the Excel spreadsheet, which is really odd, because NOTHING should have been written to the spreadsheet yet. The OnShow event in my ancestor form does NO writing to the spreadsheet, and there is no code being executed in the descendant form at this point.
Any ideas? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18830
Joined: 2006-05-11
|
Hello George,
Do you access the Excel object model in a background thread? If so, you must do this on the main thread only as all Office object models are not thread-safe.
Do you have any third-party components on that form?
Make sure that you have the XLAutomationAddin property of the add-in module set to false.
You can find what causes this by adding OutputDebugString calls to your code. At run time you collect the messages sent by these calls using the DebugView utility, see http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx.
Andrei Smolin
Add-in Express Team Leader |
|
|
George Spears
Posts: 79
Joined: 2010-05-06
|
I have tracked down the bug (or at least one of them...ha) but I don't know quite how to fix it. The issue has to do with determining the number of rows and columns in a spreadsheet. The flow of my program is...
User clicks on ribbon button...
I create, but not show a new window
I determine the last row and colomn to pass to that new window
...
Determining the last row and column is my problem. I use the following code.
try
{$IFDEF Excel2000}
BoundRec.LastRow := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByRows, xlPrevious, EmptyParam,
EmptyParam).row;
BoundRec.LastCol := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByColumns, xlPrevious,
EmptyParam, EmptyParam).Column;
{$ELSE}
BoundRec.LastRow := IWks.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByRows, xlPrevious, EmptyParam,
EmptyParam).row;
BoundRec.LastCol := IWks.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByColumns, xlPrevious, EmptyParam,
EmptyParam).Column;
{$ENDIF}
except
on E: Exception do
begin
BoundRec.LastRow := 0;
BoundRec.LastCol := 0;
end;
end;
Generally, this works fine, with one exception. Assume I had data in row 1, Columns A, B, and C. If I load this spreadsheet, Add data to Column D, but DO NOT CLICK ON ANOTHER CELL, then click on my ribbon button, when the above code is executed, it returns last row and last col to be 1, which is NOT accurate.
How do I get last row/last col when the user may have added data but not left the cell to update the internal mechanisms which track last row/last column? |
|
|
Andrei Smolin
Add-in Express team
Posts: 18830
Joined: 2006-05-11
|
|
|
George Spears
Posts: 79
Joined: 2010-05-06
|
That did it. Thanks! If anyone is interested, here is my code to determine if any cell is being edited...
// Is any cell in edit mode? If so, return true, else return false
function TAddInModule.InEditMode : Boolean;
begin
if ExcelApp.Interactive[LOCALE_USER_DEFAULT] = False then
begin
Result := False;
Exit;
end;
try
ExcelApp.Interactive[LOCALE_USER_DEFAULT] := False;
ExcelApp.Interactive[LOCALE_USER_DEFAULT] := True;
except
result := True;
Exit;
end;
Result := False;
end;
|
|
|
Andrei Smolin
Add-in Express team
Posts: 18830
Joined: 2006-05-11
|
Many thanks for posting this!
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register