Spreadsheet vs grid

[XFan]

Hi again,

I found we can set the viewable range in the options of the spreadsheet control. That is very nice but... can we do it using VBA? I can't find a damn method for this.
All I'm trying to do is change the range dynamicly based on my needs.

Then this is my "more advice than question" question:
For those who already used controls like iGrid... is there a reason you would prefer one over the other one ? (spreadsheet vs iGrid)

The iGrid is amazing (tons of methods already builded that work just fine..) but well. Cost some money and we are not really throwing money in garbage without a good reason.
I'm currently "playing" with both those controls to figure out which one I would pick but I know I wont be an expert of those by tomorrow so if some advanced users want to share their thoughts, would be mucho appreciated!

Happy pre-friday!
XFan

 

[vbaInet]

I've not used the iGrid control but had a quick look online and it looks very much like a listview control with a few functionalities thrown it. This is only from seeing an image anyway.

Since you're paying for it I wonder if it can be bound to a data source?

Neither have I used the spreadsheet control before but it sounds like a viewer for Excel files. Perhaps you can make edits to it but does it save the changes to the actual file or is it just for that session?

Sorry I can't help you here because I've not used any of them, just thought I should make some comments

 

[XFan]

I didn't look in dept about listview because I thought it was read only. Can a user actually write on it like a datagrid?

Spreadsheet you can use it for more than just viewing excel files. You can work it the same way as you would do in Excel vba: write/read cells, let user input (or not) or cycle trought datas to read and write them into an Access table (manually...) But to me, this is not the best option to make a "grid" work. I mean, it was not meant for this so this is probably not very optimal and you have to work all with loops and... Well, if I'm trying to do the work in Access it's not to step back in Excel!

I miss the datagrid. Wonder why MS removed it. This is such a nice tool I've used alot in .Net ...
For the iGrid, I'm testing it right now since you can have a full demo for free with no other limitations than a popup windows every 5 minutes or so! I'll try to figure out if you can use a datasource with it.

But just to show you, you can fill the full grid (with column names from the table) with a single line of code:

iGrid0.FillFromRS CurrentProject.Connection.Execute("Customers")

Where customer is just a table (it could be a query too). Pretty quick no? I guess with a spreadsheet I would have to create a connection, retrieve data, cycle with a loop to write them down...

 

[XFan]

Long search of 2 minutes: no it can't be bound to a datasource.
But you can add a sub:

Private Sub iGrid0_AfterCommitEdit(ByVal lRow As Long, ByVal lCol As Long)

And force an update of the edited data every time a data is changed.

Else, works only with recordset. Well still, it's faster than with a spreadsheet I would guess. As for the listview, I would have to dig more into it. That's for sure I was more searching for controls I already knew but so many things changed!

 

[vbaInet]

To be honest I've not used Access in a long while so I can't remember if it's editable or not.

So have you experimented with the spreadsheet control yet? It sounds like the Spreadsheet control is ideal for spreadsheets because Access inherits all the properties and methods through that one object, much the same as using the Excel object.

I suppose it depends what you want to use it for. If you want something generic then iGrid sounds ideal. I also like the fact that you can fill the grid with just one command, quite neat. But then again, you could have written your own class to do this.
So the FillFormRS method is just to fill the data grid right? It doesn't bind that source to the grid?

It would be interesting to do a speed test between both to see which one traverses each cell between a range of say 1:60000.

 

[XFan]

Same here ahahaha! I'm more oop than anything else! VBA makes me cry!

I've read on the forum of iGrid that it does not do the job of a datasource. So no, I guess if you edit the datas, it wont do anything unless you make an update on "AfterCommitEdit" event. And truely sad because if I remember well, in .Net the datagrid can have a datasource no? That's the only thing I remember of that VB.net I've done 7 years ago!

The problem of the spreadsheet is I can manualy edit the Viewable range of the sheet. But can I do it with VBA? I can't find a way.

Other cons, I have to setup my columns as my first row. I dont have to deal with this with a grid as iGrid. Columns are separated headers from the data.

And last cons is that I have, as you said, to write my own methods to do the read/write work while it seems so quick to do it with iGrid. 250$ or something is not that expensive. If cost was more like 1000$ and more, maybe I would have dig deeper into spreasheet but... Not sure I want to waste time for this. I'll only if it's possible to edit the "viewable range" with VBA else I wont even spend more time on it.

Maybe i'll take a look at listview but I think it's read only. I'll try it just in case it could do the job of a datagrid. But I'm not sure of this.

Anyway, thanks for your help. Your replies help me thinking further and about things that I didnt considered earlier. Will keep thinking about it, make test and share my results later here.

Regards,
XF

 

[vbaInet]

There's got to be some trick to do that. Unfortunately I don't have the control in front of me so I can't give any suggestions

Yeah in a listview you have to set up the column headers too. That I remember. You would have thought that the developers of the control would have created a wrapper to pick the field name as the column header and fill the grid accordingly with just the FillFormRS command.

Remember the way of getting properties of an ActiveX control we spoke about? Did you try it with this control? Also, see if it has a Properties collection, then do a For Each loop to see the properties it has.

Let us know your findings if you end up doing a comparison between iGrid and Spreadsheet controls.

 

[XFan]

Earlier this morning, tried to dig out a bit of listview. Major problem of this control is that only the first column is selectable. Also, it seems you can make it "editable" but I didn't manage to make it work (and I'm not going to waste my time on it since only first column can be worked on by user).

As for the spreadsheet, here is the magic line:

Dim sprdsht As Spreadsheet
Set sprdsht = Me.Spreadsheet9.Object
 sprdsht.ActiveWindow.ViewableRange = "A1:B5"

Manually, I had setup my viewable range to A1:B10. When I run my form, I see A1:B5 which mean it works as intended.

For now, I seem to be able to work the iGrid and the spreadsheet exactly the same way. Only big differences are iGrid has ColumnHeaders while spreadsheet I need to work with the first row as header (not a major problem)... And, the iGrid has fully working methods -> one line to fill the full grid while you have to do it manually with the spreadsheet.

HOWEVER, considering what I'm going to do with the grid, I could not use the iGrid filling method and would need to make loops anyway. Because to be honest, I was not trying to just display the content of a table like this. Else I would have just use the datagrid subform feature provided which would have done exactly the same thing and would have taken less amount of time to setup. While displaying, I need to do alot of VBA work on datas before displaying. Considering this, I could do the work pretty easely using both iGrid or spreadsheet.
So which one win on the other? I could not tell. If I was doing tons of Access program, I would probably buy the iGrid for sure because I think it's a really useful tool and spreadsheet are alot of messing around for nothing. But I'll probably only dev one application for now (summer job anyway ahahaha) so. I guess I could make it with the spreadsheet too.

Conclusion: possible to turn the spreadsheet control into a limited range grid and setup some rows/columns as readonly so user can only edit rest of the cells (you can also edit background colors to make visual separation of what is editable and what is read only). I just need to figure out if the RangeName feature works with spreadsheet (a very useful and powerful tool imo).

So well, probably going to use spreadsheet since it's free and write my own methods!

Thanks for your help buddy!

Regards,
XF

 

[vbaInet]

Interesting analysis. And I knew there had to be a way of setting the viewable range and it makes sense that it's part of the Worksheet object.

I'm not so sure about the single column selection in a listview but I remember it has limitations, just don't remember which ones. It's been a couple of years since I used it. Probably not worth faffing about with since you want individual editable columns and I'm sure it's called a "listview" for a reason

With regards filling the Spreadsheet I'm sure (or I would imagine) one can still do CopyFromRecordset which will take a dataset from a recordset and copy it to the spreadsheet.

The other thing I would imagine the Spreadsheet does that iGrid doesn't is being able to read from XML. With Excel being OpenXML format now, you could load from a formatted XML doc so they would have incorporated this functionality into the Spreadsheet.

 

[XFan]

Oh well I never intended to use another source of data than my Access Database.

I'm very sad but...

sprdsht.ActiveWindow.ViewableRange = "A1:B5"
sprdsht.Names.Add "ARangeName", "A1:B5"
MsgBox sprdsht.ActiveSheet.UsedRange.Columns.Count

The Names.Add does work... but for the UsedRange... in excel you can make call this way:

 Range("ARangeName").Columns.Count

Sounds like in Access, you can't. Or if someone knows how, tell me! :banghead:

UsedRange does work but I feel so much more comfortable with the other one! It's safer to work than UsedRange (count cells til it reachs the last column/row with a data inside.. if you mess up and write data in a hidden cell far far away , you're screwed ahahahaah!) This could not happen with the Range method.

Anyway, gonna work with it. Maybe this is the reason they removed the grid. They considered spreadsheet was doing the job? Don't know!