VectorLinux
August 29, 2014, 11:57:34 am *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
News: Visit our home page for VL info. To search the old message board go to http://vectorlinux.com/forum1. The first VL forum is temporarily offline until we can find a host for it. Thanks for your patience.
 
Now powered by KnowledgeDex.
   Home   Help Search Login Register  
Please support VectorLinux!
Pages: [1]
  Print  
Author Topic: Coding VBA in excel.... Need help  (Read 11306 times)
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« on: December 19, 2007, 06:37:56 am »

Has anyone here ever coded any VBA into an excel document or have any experience with M$ 0ffice applications?... I need a little help here

I have created an excel document using pure vba code. I use forms to fill in the information, so the grid is never touched. The reason behind this is to keep the user from entering data in the wrong place. The dilema I'm facing now is this

When User "A" is using it, wind os locks it, so when user "B" and / or "C" opens the document, they get a read-only copy.

Is there a way to allow more than one user to read / write to the same document?
Anyone have any experience in this field?
Logged

rbistolfi
Packager
Vectorian
****
Posts: 2282


« Reply #1 on: December 19, 2007, 06:50:13 am »

I have read Ms Windows has no that ability at all, and in my experience, you just cant have two instances of the same doc. I have done something similar with OO, editing the xml file. I think the easiest solution could be to save the data in a database or text file, and build the excel doc 'on demand'.
Logged

"There is a concept which corrupts and upsets all others. I refer not to Evil, whose limited realm is that of ethics; I refer to the infinite."
Jorge Luis Borges, Avatars of the Tortoise.

--
Jumalauta!!
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #2 on: December 19, 2007, 07:08:04 am »

Well, Database is out of the question... the reason I created such a complex spreadsheet is because of the lack of databse software here.

Do you have any idea how to accomplish this saving the data to a text file?
Logged

wcs
Packager
Vectorian
****
Posts: 1144


« Reply #3 on: December 19, 2007, 07:51:28 am »

Saving it to a text file sounds like a good idea.
It is possible to use the Open command to open a new or existing text file and read or write the data on it. I think using Write or Print for putting the data in it, and Input to read it. Or something similar.
Logged
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #4 on: December 19, 2007, 08:06:58 am »

Yes, it can indeed be done. The syntax is much similar to gambas
Here is a sample sub that writes to a text file
Code:
Sub TextIODemoWrite()
  Dim sFile As String
  Dim sText As String
  Dim iFileNum As Integer

  sFile = "C:\test\textio.txt"
  sText = "Testing 1 2 3."

  iFileNum = FreeFile
  Open sFile For Output As iFileNum
  Write #iFileNum, sText
  Close #iFileNum
End Sub

but in reality, that would create a text file with an enormous string. Retreiving that data and making sense of it is where it gets complicated
Logged

Triarius Fidelis
Vecteloper
Vectorian
****
Posts: 2399


Domine, exaudi vocem meam


WWW
« Reply #5 on: December 19, 2007, 08:29:41 am »

What about CSV files? I have no idea, just throwing it out there.
Logged

"Leatherface, you BITCH! Ho Chi Minh, hah hah hah!"

Formerly known as "Epic Fail Guy" and "Döden" in recent months
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #6 on: December 19, 2007, 08:49:58 am »

* M0E-lnx doesn't even know what a csv file is...
Logged

Triarius Fidelis
Vecteloper
Vectorian
****
Posts: 2399


Domine, exaudi vocem meam


WWW
« Reply #7 on: December 19, 2007, 08:52:29 am »

* M0E-lnx doesn't even know what a csv file is...

comma-separated values ... a crappy way of serializing data
Logged

"Leatherface, you BITCH! Ho Chi Minh, hah hah hah!"

Formerly known as "Epic Fail Guy" and "Döden" in recent months
wcs
Packager
Vectorian
****
Posts: 1144


« Reply #8 on: December 19, 2007, 09:05:23 am »

I think there's one VB command that writes data on a new line (perhaps PRINT).
You can also insert tabs in it, although I do not remember how.
In this way, you get a two-dimensional matrix on your text file... retrieving it might be a bit more complicated though.
Logged
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #9 on: December 19, 2007, 09:23:20 am »

well, you can format the string however you want... but I find that kind of hard to do

you can insert new lines using vbCRlf to keep it from being one really long line

I gues an INI file would be ok...
make it write it like settings

for instance
Code:
VARIABLE_1 = "This"
VARIABLE_2 = "That"
VARIABLE_3 = "The Other"
I know how to do that in gambas, but I'm not sure how to do that in VBA... i'll have to do some digging.... but that should do it

No on to another problem... This document has many worksheets... Should we use several INI files? one for each sheet? (that could be efficient, but messy)
or do we do one file for all sheets (This would be hard to read)
« Last Edit: December 19, 2007, 10:36:45 am by M0E-lnx » Logged

MikeCindi
Tester
Vectorian
****
Posts: 1073


« Reply #10 on: December 19, 2007, 09:53:01 am »

Sorry, getting into this a bit late but this is from the help on Excel 2003 on sharing workbooks:
Quote
If you need input from several people, you can create a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.) and place it on a network location where they can edit it simultaneously. For example, if the people in your workgroup each handle several projects and need to know each other's status, the group could use a shared workbook. All persons involved can then enter the information for their projects in one and the same workbook.
It seems that I've been able to get simultaneous use/changes between users in the past (Office 97) but haven't tried it in a long time...perhaps there was a problem.
Here's some more from the help:
Quote
After you open a shared workbook, you can enter and change data as you do in a regular workbook. A few aspects are different from working in a regular workbook, however.

Seeing other users' changes   Each time you save the shared workbook, you're updated with any changes that others have saved since the last time you saved. If you want to keep the shared workbook open to monitor progress, you can have Microsoft Excel update you with changes automatically, at timed intervals that you specify, with or without saving the workbook yourself.

Resolving conflicts   When you save changes to a shared workbook, another person who's currently editing the workbook might have saved changes to the same cells. In this case, the changes conflict, and a conflict resolution dialog box appears that allows you to decide whose changes to keep.
HTH,
Mike
Logged

The plans of the diligent lead to profit...Pro. 21:5
VL64 7.1b3                                     RLU 486143
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #11 on: December 19, 2007, 10:43:36 am »

Well, that would definitely take care of the sharing problem...

Thanks... Never thought of looking there

BTW, do you know how to force a cell to display something like "10/07" preferably from VBA code.
every time I do that, it keeps tripping... some weird value appears

I just tried this, and I got an error saying "Macros or VBA code cannot be viewed or edited in shared workbooks"
« Last Edit: December 19, 2007, 10:53:01 am by M0E-lnx » Logged

Joe1962
Administrator
Vectorian
*****
Posts: 2499



WWW
« Reply #12 on: December 19, 2007, 10:57:57 am »

Well, Database is out of the question... the reason I created such a complex spreadsheet is because of the lack of databse software here.
This is the kind of problems databases excel (pun intended) at. Just grab the PostgreSQL installer for windows and access it through odbc or oledb, etc. A look through the SQL manual should get you going with it too.

Do you have any idea how to accomplish this saving the data to a text file?
Handling a text file is probably more work than using a database. In fact, if it is a csv file, you can use it just like a database from vba, using the proper odbc driver. What I fear though is that accessing a text file will have the same multi-user problems as the spreadsheet.
« Last Edit: December 19, 2007, 11:01:04 am by Joe1962 » Logged

O'Neill (RE the Asgard): "Usually they ask nicely before they ignore us and do what they damn well please."
http://joe1962.bigbox.info
Running: VL 7 Std 64 + self-cooked XFCE-4.10
M0E-lnx
Administrator
Vectorian
*****
Posts: 3179



« Reply #13 on: December 19, 2007, 01:09:57 pm »

Exactly..

Handling a text file is probably more work than using a database. [...] What I fear though is that accessing a text file will have the same multi-user problems as the spreadsheet.

Exactly.
I'm not sure I want to do all that just to get this done though... It's a demo, and works surprisingly good as it is now...
Most likely, I'll be the only user actually doing something with this, but the other day my boss wanted to take it for a spin, and I tried to use it while he had it running, so I got the read-only copy.

If anybody else has any more suggestions, keep 'em coming.
Logged

MikeCindi
Tester
Vectorian
****
Posts: 1073


« Reply #14 on: December 20, 2007, 12:35:44 pm »

Quote
Thanks... Never thought of looking there

Where? The help files?  Wink

Quote
BTW, do you know how to force a cell to display something like "10/07" preferably from VBA code.
every time I do that, it keeps tripping... some weird value appears

I just tried this, and I got an error saying "Macros or VBA code cannot be viewed or edited in shared workbooks"

Never had a problem with macros in a shared file that I recall but I believe they are locked in a shared workbook by default. The online help for Excel has a topic called "Features that are unavailable in shared workbooks" which includes the following:
Quote
Write, record, change, view, or assign macros - You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook.

I've never tried VBA so can't help there.

As far as the value "10/07" if the default cell format is "general" then it is going to give you 10 divided by 7 which is 1.42857143... I am sure there is a way with VBA to define the cell's format to show data as you would want it. Thus if 10/07 is to be a date then you would define the cell (or range) with a date format.
HTH,
Mike
Logged

The plans of the diligent lead to profit...Pro. 21:5
VL64 7.1b3                                     RLU 486143
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!