Your thoughts on shared workbooks

[MagicMike]

This is more of a general "what would you do" question.

Well I have been working on this huge excel file here wich is intended on replacing our manual work order numbering system for about 2 months now. Given that I know excel very well and vba about average I thought it would be easier to create something in excel rather than access. I don't have any experience with access other than I tried using it once and was left scratching my head. The problem that I just realized after being 80% done with this file is that my boss pointed out to me the requirement to have multiple users accessing this thing at the same time and all creating different work order numbers at the same time possibly. So, I looked into shared workbooks and from what I see they can be very problematic. My question is this:

Is there a way to use the excel file I have now and just keep a seperate file that holds all of the work order numbers that acts like access (such that a record is pulled from it and edited and then placed back when the user is done)? Or is there a safe way to use shared workbooks?

I hope I am conveying what I'm trying to do properly and I hope I have put this post in the right place on the board.

Thanks in advance.

-Mike

[CreganTur]

my boss pointed out to me the requirement to have multiple users accessing this thing at the same time and all creating different work order numbers at the same time

This truly is a job for Access, not Excel. You could go the shared workbook route, but you'll run into a lot of problems due to concurrent users. Access is designed to be a multi-user database, which is what you're trying to emulate via Excel.

Access isn't that scary- the VBA syntax has some differences, but you'll pick them up quickly. I'd suggest getting a good Access front-end book, like:
Access 2003: your visual blueprint for creating and maintaining real-world databases or Access 2003 Step By Step, which is a good guide book for the MOS:Access 2003 exam.

That's the humble opinion of this Access dev anyway-one of the Excel gurus may have a different view point.

[lucas]

I agree with Randy as do many others:

http://vbaexpress.com/forum/showthread.php?t=23610

[MagicMike]

This is what I didn't want to hear

Thanks for responding so quickly guys. I am screwed. I've put a solid 2 months into this excel file and it looks like I wont be able to use any of the features in access. I guess I'm going to look at access and see what I can do there.

Thanks again.

-Mike

[lucas]

Why not export the excel files/sheets as Access tables and go from there?

[CreganTur]

Why not export the excel files/sheets as Access tables and go from there?

That's a great suggestion- you can import your excel spreadsheets into Access as Tables- if your Excel creation uses Named Ranges you can also import them specifically as individual tables too.

Hopefully you were trying to emulate relational database structures in your Excel spreadsheet(s)- multiple tables that all hold different, but related data. If so, then the process of conversion to access shouldn't be too arduous. I'm also willing to provide help/guidance as you start working with Access (if you choose to do so), and I'm sure all of the other users at the Access forum would be happy to help too.

It's always rough getting news like this, but it could be a really good learning experience for you.

[MagicMike]

Well I had a short meeting with my boss aka the CEO. He apparently has had some very bad experiences with Access and we have some outdated accounting access db's that are pretty aweful. Needless to say I could not argue that it HAD to be done with access because I don't know enough about access. All I told him is that the "experts" on the message boards that have helped me out tremendously have advised that there is no way to have multiple users in an excel file at the same time writing data without sharing and even if shared the last person to save owns the changes. What I did suggest to him and I think might work is if I create a totally different excel file with only one sheet for the consolodated work order numbers. This way, more than one user can go into the original file and ONLY when they request a new work order number will the new file be opened, written to, saved, then closed promply. I deduced that the odds of two people trying to open the same work order type (15 different types) within that same 10-20 second time window are pretty good or at leaste much better than relying on a shared workbook.

What do you think about that?

[lucas]

I deduced that the odds of two people trying to open the same work order type (15 different types) within that same 10-20 second time window are pretty good or at leaste much better than relying on a shared workbook.

Ken has offered this in the past to check to see if the workbook is being used.......I have never tested it but you can try it and see if it helps:
[VBA]Sub WhoIsSharing()
Dim wbShared As Workbook
Dim lUsers As Long
Dim aryUsers() As Variant
Dim sUsers As String

'Set the workbook name here
Set wbShared = Workbooks("sharetest.xls")

With wbShared
aryUsers() = wbShared.UserStatus
For lUsers = 1 To UBound(aryUsers())
sUsers = sUsers & aryUsers(lUsers, 1) & vbNewLine
Next lUsers
MsgBox "The following people are using your workbook:" & vbNewLine & _
sUsers, vbInformation + vbOKOnly, "Active Users"
End With
End Sub [/VBA]

[MagicMike]

Steve,

Thank you very much. I will definately have to add that in. I was wondering what I would do in that rare case of the 20 second window. I have some similar code that found somewhere for checking if a file is open but it looks different to this and does not tell who has it open if it is open. Thank you very much for trying to help me on my only apparent path instead of just saying "that's the only way to do it" and carry on like most people would do.

I'd just like to say that I really appeaciate this site and all the great people who have helped me out. Thank you!

[lucas]

Bosses are hard to please......

[MagicMike]

Bosses are hard to please......

Especially when they themselves don't even know what they want! All they know is that they want you to do it!

Thanks again !

[CreganTur]

Something else that might help you to know is that you can get data out of a workbook without ever opening it! This could keep you from some of the headaches of using a shared workbook if you have any data retrieval procedures as a part of this project.

It involves using a ADO (ActiveX Data Objects) connection that you can make to a specified worksheet in a workbook. Basically you set your worksheet as a data source and then you can get records out of it via a SQL query. This current version utilizes a file dailog window so the User can select the workbook they want to interact with. You can remove this bit of coding and hardcode in the filepath to your shared workbook and the name of the worksheet you want to query. You'll need a reference to the Microsoft Office xx.0 Object Library (Where xx.0 is 11.0 or 12.0, depending on your version of Office). By default it only looks at the very first worksheet in a workbook. You can specify a worskheet by hardcoding it in place of the szSheetName variable.

You can put this code into a module and when you run it you can use the dialog window to choose a file. It will query all records from the first worksheet in the workbook and print the field name and its value for all records to the Immediate Window.:

[vba]Dim strFilepath As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rsData As ADODB.Recordset
Dim szSheetName As String

'select workbooks using file dialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen '<<<Opens file dialog window
.AllowMultiSelect = False '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
Next
End If
End With
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strFilepath & ";" _
& "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set rsData = conn.OpenSchema(adSchemaTables)
szSheetName = rsData.Fields("TABLE_NAME").Value '<<<Get worksheet name
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM [" & szSheetName & "];", conn, adOpenStatic, adLockOptimistic

Do Until rst.EOF
Debug.Print rst.Fields(0).Name & vbTab & rst.Fields(0).Value
Loop

MsgBox "Look at the first field of your recordset in the Immediate Window."[/vba]

HTH

[MagicMike]

Thanks Randy! Also good to know. I need to find a place to save all this great stuff. You guys are awesome!

[lucas]

You can retreive data from a closed workbook based on getting data from a sheet, a range or a named range.

There are 3 files in the attachment. One is the database:
SQL Reading Excel.xls

The other two contain examples of retrieving data from that database file using the three methods above.

The only difference in the runme files is that one works on the database file in the same directory as the runme file.....I would recommend trying it first to get a handle on what happens when you run it.

The other works on a fixed file path and you can implement it when you figure out how to use it if this seems like a viable alternative.

[phendrena]

Sorry to hijack the thread, just looking at post #12 from Randy Shea.

Something else that might help you to know is that you can get data out of a workbook without ever opening it!

Can I write to another excel workbook using ADO?
I am using a shared workbook setup (unavoidable atm) but will eventually switch to an access database, for the moment though it's excel and all it nice faults.

Would there be any benfit having the data form and the actual data stored in seperate workbooks? Or this likely to cuase even more problems?

Thanks,

[Bob Phillips]

If it is another workbook, you don't need ADO, just open up the workbook and write to it.

[MagicMike]

ok been a while but I am back to this point in my project. I am using the below code to read an entire sheet from a data.exe file.

[VBA]Sub GetData()
Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@
'@@@@@@@@@@@@@@@@@@@@@ REMEMBER TO CHANGE TO CORRECT FILE NAME @@@@@@@@@@@@@@@
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@
sFilename = "U:\Work\Programs\Global Project Status\Program\data_12_08_08.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@
'@@@@@@@@@@@@@@@@@@@@@ REMEMBER TO CHANGE TO CORRECT FILE NAME @@@@@@@@@@@@@@@
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ @@@
'the sheet retrieved
sSQL = "SELECT * FROM [All_WO$]"

Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

If Not oRS.EOF Then
ThisWorkbook.Sheets("Import").Range("A4").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

oRS.Close
Set oRS = Nothing

End Sub 'GetData()[/VBA]

My question is this: I lose the formatting when I get the data into the working workbook. For example, I have a column that uses dates and when it is "imported" to the original workbook I just get a bunch of random numbers that can be formatted back into a date from the cell. Its like its grabbing the value and not the text of each cell. Is this possible to do or do I need to set the formatting into the sheet before the data dump?

[Bob Phillips]

See this thread http://vbaexpress.com/forum/showthread.php?t=24106

[MagicMike]

See this thread http://vbaexpress.com/forum/showthread.php?t=24106

ok now I'm more confused lol. In that thread I see you say that it cant be done and offer a suggestion. I then see that the last link in there suggestd how to do something but it looks like it applies to an access db.

Are you suggesting the fix you suggested or are you suggesting I try the last link suggestion

[Bob Phillips]

No, the last post suggests iterating the recordset rather than using CopyFromRecordset. In other words, one item at a time instead of a block operation, I know which I prefer.