Preserving macro data

I just found out that loading a XLSM into a grid will cause all of the macro information to be lost. I do not need to use the macros in my app, but I do need to preserve them. I need to visually display this data to the users, do a lot of inserting data into existing columns programmatically without losing formulas, and then save the data out while preserving the macros. I have been starting with using grids as the basis for what I am doing. This XSLM file is huge, with 26 pages using the full 256 columns of the old limitations. Is there an easy way to preserve the macro and formula data, such as inserting the edited data into a template? Do I have to dual load the data into Flexcel and the grid, and then update the flexcel object (yuk!)? Any suggestions on how I should do this? I am semi-familiar with Excel, I have done a bit of Excel Automation but obviously do not like that approach or I would not be here.

Thanks!

Walt, 

First of all sorry about the delay in answering your last mail, I was waiting to see if I could make a small demo app to show you how this could be done. 

As we have discussed over mail already, once you import a file into a TAdvStringGrid, the info that cannot be imported (like macros) is lost. There is no real way to do it differently, once the file is loaded into the grid it doesn't "remember" anymore that the data came from an xlsx file: it could have come from anywhere. And as TAdvStringGrid doesn't do macros, then we can't import that data into the grid. 

If you don't need the advanced editing capabilities of TAdvStringGrid, the simplest approach might be to use FlexCelGrid instead of TAdvStringGrid to do the editing. FlexCelGrid updates the underlying FlexCel object directly, and so it will preserve the macros and in general be much more good at preserving the original file. 

Installing FlexCelGrid is a little tricky, so before doing so, you can try with this exe demo:
http://www.tmssoftware.biz/flexcel/samples/XlsViewer2.zip

To see if it could work in your case. As you will see, editing is more limited than in AdvStringGrid, but as a counterpart, macros and most stuff are preserved. Let me know if this is an approach you would want to do, and if that is the case I'll send more detailed instructions.

To use AdvStringGrid, you will have to export over an existing file. This is actually not that complex, as you can see in this example:
http://www.tmssoftware.biz/flexcel/samples/saveintoexistingfile.zip

Adrian,

First, I will mention the other suggestion you had, in case it works for someone else. Using an XLS file would work for what I am doing as far as retaining the macros  and formatting data, unfortunately this is one of those huge spreadsheets that exceeds the XLS size limitations.

I am currently using a set of TAdvSpreadGrids. What are the major differences with the FlexcelGrid from a TAdvSpreadGrid? Is it just the inplace editor capabilities? I can live with that. I need to be able to programmatically insert column data based on user input. They do not really need to directly edit the data. They do need the ability to sort the grid, search and search and replace would be nice but not required. Can I do the same things programmatically with the FlexcelGrid as I can with the TAdvSpreadGrid?

Also, are there any differences in import/export functionality such as being able to set what row the import starts with, what row contains the header data, etc?

About differences between FlexCelGrid and AdvSpreadGrid, mostly it is editing capabilities, yes. (inplace editors, etc). On the other hand, FlexCelGrid will be faster specially with huge spreadsheets, because it doesn't do an "import/export" cycle as AdvStringGrid does. (to use AdvStringGrid we first load the file into a TXlsFile object, then we import that data into the grid, then we create a new TXlsFile object, and export the cells back into that object. FlexCelGriddirectly shows the results of the TXlsFile object, so it will be much faster.


About inserting columns, you do that in the TXlsFile object (xls.InsertAndCopyRange) then call FlexCelGrid.LoadSheet so the changes are updated from the TXlsFile to the grid. To sort the grid, again, you sort the TXlsFile object (xls.Sort), then call LoadSheet so the changes are reflected in the grid. Search and replace (non visual) is the same: you do a search and replace in the TXlsFile object (xls.Replace). A visual search or replace is a little more tricky, but doable: xls has a Find method: you can call it to find the cell, then move the cursor to that cell in the grid.

As you can see, you do very little programatically in FlexCelGrid, you do the stuff in the non visual TXlsFile object that is connected to it. Then call loadsheet to update the grid with the changes.

About import/export functionality: well, there is actually no export/import being done in FlexCelGrid, and that is the main difference with the TAdvStringGrid approach. FlexCelGrid directly displays the xlsm file in the TXlsFile object that is connected to it. There is no extra memory used by the grid, and to change most things you have to change the TXlsFile object that works as the data store for the grid. So there are no options on where the import starts, etc, because there is no import going on. It woeks more like if you opened the file in Excel (which doesn't import/export anything either).

There may be a problem with using that approach, then, if I understand correctly. I need to be able to (in a relatively simple manner) adjust what row in the file is used as the column names. Most have the column data at the top, but some will have it a few rows down. Can that be adjusted using a TXlsFile object? The rest of what you describe sounds like it will work fine for my project.

FlexCelGrid doesn't use any row as column names. It is similar to Excel: it shows A, B, C, etc as the column names.





Does it allow for fixed rows? That would be acceptable, having the column names be in a fixed row area.

Yes, you can have fixed rows by checking the UseFixedCells property.


But note that this will use the "freeze panes" that you set in Excel (ribbon->view->Freeze panes). So again, you don't set the fixed rows in the grid, you freeze the panes in Excel (which you can do directly in the Excel template or with FlexCel and a command like     xls.FreezePanes(new TCellAddress("A7")); )

Another thing to notice is that if you want to say set row 5 as the fixed row, then you will have all rows from 1 to 5 fixed (just like in Excel when you freeze panes). If you want to freeze only row 5, then you can hide the other rows with xls.SetRowHeight

And a last thing: As you can see from this posts, FlexCel grid is mostly a wrapper to display and edit the FlexCel object behind it. It is a descendant from Delphi's TCustomGrid in virtual mode, which in its "OnDrawCell" event renders the cell values of the XlsFile FlexCel object. So to use the grid effectively, you need to learn to manipulate the XlsFile which serves as the data model. For that, you have a tool in FlexCel start menu: APIMate, which will tell you how to do this kind of stuff with FlexCel. For example, to find the line I quoted above (xls.FreezePanes(...)) I created an Excel file, froze the panes, and opened it with APIMate, which gave me the line. It was faster for me than to look at the docs and try to remember how to freeze the panes (I've been working in FlexCel from more than a decade now, and I can't remember every command out there either) 

Ok, so it looks like the FlexCelGrid is the best way to do what I need. Now where do I find it, and since you said it is "tricky", what are the important parts that I need to know?

Thanks for all the assistance!

Sorry for the delay. It isn't too much tricky actually, but you need to install FlexCel 3 besides FlexCel 6. 

The procedure is as follows:
1)Download FlexCel 3 from the registered users page. It is below FlexCel 6. But DONT DO IT YET. I have identified a bug in the latest FlexCel3 release, and we are working hard to fix it. A new version will be available in the coming days, install that.

2)When installing FlexCel 3, it will detect FlexCel 6 is installed, and offer to install XlsxAdapter. Install it. XlsxAdapter uses the FlexCel6 engine to open the files, and provides an interface that the FlexCel 3 components like FlexCelGrid can use.

3)Look at the XlsViewer demo. It mostly shows how to use the grid. But use the XlsxAdapter that was installed, not the XlsAdapter that the demo uses. XlsAdapter uses the old FlexCel3 engine, doesn't support xlsx and it is obsolete.

And well, that is it. I'll try to have a new FlexCel 3 version as soon as possible. It is already in the testing phase.