PDA

View Full Version : Chat board



rajpalmanish
01-27-2006, 02:29 AM
HI,

Once again asking a question on the board but without any cross posting this time.

I want to make a Chat Server on excel.

I mean to say, i want to make a board in which one person can send a message and other can retrieve it on a networking. What I tried is, I ceated a file and share it. I also created a userform where 1 text box and 1 label was exist.

I tried to save the text in 1 cell and it should also come on the label above.

But this is not working on LAN.

Can anyone has a solution.

Thanks and Regards
Manish

Ken Puls
01-27-2006, 09:40 AM
Hello Manish,

You're issue, no doubt, is that the workbook cannot be opened by two people with write access at the same time, correct? I think you can get around this with shared workbooks, but I've never really liked them.

Personally, I think I'd develop an Excel addin with the code, and an access database as the backend. Push/pull the data to/from the database usind DAO or ADO and SQL queries.

Obviousley, this is at a conceptual level only, right now, but I can point you to some example code if you need it.

rajpalmanish
01-29-2006, 03:13 AM
Hi,

Thanks for your response and sorry for my late response.

As you said that you have developed an excel ad-ins using access. Can you give me that add-ins or can you provide me some codes to make this.

Thanks and Regards
Manish Rajpal

XLGibbs
01-29-2006, 06:09 AM
I agree with Ken. If the issue as it apparently stands is having multiple users having write access to the same file at concurrent times...the way to go is likely using ADODB connections with Access....

While we may be able to point you to code that can be applied to some of the scenarios you describe, there is not "out of the box" codes that willl simply "do that".

Setting up the Access database takes time, and a certain level of knowledge about how to structure that to recieve data..depending on how you plan on setting up the source "User INput" side in excel.

There is equally an amount of substantial work involved in conceptualizing how the "front end" and the "data storage" or back end will be.....

These are not tasks for beginners, so you may want to consider looking into packaged software that may be able to be applied to your needs or having a consultant work on the project for you. In the end it will save you time and lots of aggravation.

rajpalmanish
01-29-2006, 08:49 AM
Hi,

Thanks Gibbs for the suggestion. Well, I can understand that this is just not a simple task however i have a knowledge of access and excel including VBA. I am not perfect in it, however can manage it upto certain level.

If anyone can give me a suggestion or a solution to start my work, i can try for it atleast.

Thanks and Regards
Manish Rajpal

XLGibbs
01-29-2006, 09:10 AM
The mechanics of this will be the main issue. How many people may need to open this file and be able to enter messages?

If in excel, there would have to be statements to update the access table with new entries, code to subsequently refresh the excel file with data from the table in Access.

I dont really have any samples of such a level of interaction between the two that would more or less be live.

The access file would need to have a table that had an updateable table with sequential record updating (or the ability to qeue concurrent entries. Each time an update statement is sent to the table, that particular user would have a short term lock onthe data. Once the update is complete, the lock would be released.

The excel file could have a linked query set to refresh automaticall reading from that table. Your userform could be contained in Excel which then read from the table...

Then the issue is, how does the excel form determine which "conversation" to display.

My suggestion would be to make an excel file with form that reads from an auto-refreshing query (in excel) fed by a table in Access on the shared directory. The excel file would not have to be in a shared directory and could be distributed (as an addin per ken's suggestion.

The form would have to have code to connect to the database, update the message table, and then refresh the data feeding the form.

Does this give you some ideas to play with?

Ken Puls
01-29-2006, 11:33 AM
My thoughts on this...

The database doesn't need to be super complex to begin with. A field for username, timestamp and message.

I would set it up so that when a user launches the addin, it inserts a "Mr Smith has joined", and when they close the addin, it inserts a "Mr Smith has departed" type message. After that, give them an input field and a button to send their message somewhere.

I have an article on my site here (http://www.excelguru.ca/XLVBA/XLVBA03.htm) which shows how to push data to Access from Excel via ADO and an SQL query.

The username could be populated either by maintaining a login or by pushing some form of username from the PC or Office app. For some examples, see this entry (http://www.excelguru.ca/XLVBA/XLVBA02.htm).

You'd also need to pull the top 10 (or whatever) messages from Access to return to Excel. I have some code for that, but I haven't published it on my site at this point. I'll try an formalize that a bit later tonight/tomorrow night to give you a link to it. It would need to be wrapped with an OnTime handler to force automatic refreshes.

The difficult part will be:
-If you want it to maintain a list of "logged on" users, as you'll need to figure out how that part works.
-Will the data sit in an Excel sheet or a modeless userform, or some other medium? i.e. what is the interface, and is it acceptable to have it be the active spreasheet or does it need to sit beside the activespreadsheet.

There could probably be another way where you publish web type data and use a query table, or maybe even some XML route (I don't know XML). Just to be clear that this isn't the only route. It's the one I'm most familiar with is all.

rajpalmanish
01-29-2006, 02:15 PM
Thanks a lot,

I'll work on it and let you know the response.

c19h28O2
02-28-2006, 03:44 AM
Hi rajpalmanish,

Just wondering how you got on with your chat system, i'd be interested to know how far you have got, something i'm interested in doing too, currently don't have any projects on the go and would be glad to help.

Thanks

C19