excel

[JayMan]

I have written an app that calculates data, then goes out and opens an excel spread sheet and loads the data. I'm using excel because I can then sort and filter the info retrieved later. My app is being used on a network, so multiple users could possibly attempt to write to the spreadsheet simultaneously. I have 2 questions:
1.)How can I check to verify that the spreadsheet is not currently open / being written to?
2.)I would also like to retrieve this data and bring it back to my application, would It be a better approach to attach it to an Access Database? I'd like to be able to scroll forward and back to view /search each record. (If so, how would multiple users work in Access?)

I would deeply appreciate any reply,
Thanks---Jay

[Cakkie]

The first question is easy, use a template. Instead of using xls files, use XLT files, which uses a copy of the file for every user that uses it, so no risk that two people write to the file at the same time.

FGor the second one, I'm affraid I cannot help you. It's always a risky and tricky thing to import data into an application, and I haven't don this before by code. Sorry

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[JayMan]

How will the file stay updated if they write to a copy of it? Will the master file be linked to each copy and update automatically???

Thanks for the info!

[Iouri]

How to create templates for every user and update the xls file?

Iouri Boutchkine
[email protected]

[Jim Niezgoda]

Do you know much about ACCESS/VB/DAO? I dont understand exactly what your app is doing but if you have multiple users reading and writing data to the same place then I would go with ACCESS. The DAO component is easy to work with and provides all of the sharing/security methods that are required to share the data among multiple users without the worry of something getting overwritten or not updated correctly.....

[Cakkie]

Ok, I think we got a misunderstanding here. When using templates (.xlt), you don't update the master file. What he wants is very hard to do. He wants to export to an excel file, change the data in excel, and then import it into a database again. Any changes in the database must be reflected in excel, and visa versa.

Tom Cannaerts
[email protected]

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[JayMan]

Maybe I've been trying to do too much... is it possible to just open the file, rename it to a temporary filename, write to it then save it back to the original filename?

Then I could use an error handler that if another user tries to write to it while it's open, it cannot find the original filename and either display's a message to try again (maybe an autotimer)or just keeps looping until it can access the original filename. (It only takes about 5 seconds to write this data to the file)

Is this possible? If so, how do I rename the file temporarily? I've never done this before.

P.S. Thanks to all for for your input... it's very exciting to get so much help from people you've never met! How cool is that!!

[Iouri]

'Rename the file

Dim OldName As String
Dim NewName As String

OldName = "c:\temp\abc" 'file exist
NewName = "c:\temp\ib" ' new name

Name OldName As NewName ' Rename file.


Iouri Boutchkine
[email protected]

[JayMan]

You are a Awesome! Thank you so much! I've been struggling with this for a long time and you replied within minutes!

You New Yorkers truly are wonderful!
Thank You Thank You Thank You!!