Sharing Excel sheet over network?

Is it possible to have a server PC running a Microsoft Excel sheet to which several Thin Clients, with Barcode Scanners, will be connected via TCP/IP? The scanners should dump their data in the Excel sheet in a First Come First Served basis? At the end of the day, the Excel sheet will be saved on server PC's hard drive. The Barcode Scanners have a USB output.

And, are there any other alternatives to achieve the above requirement?

Schematic Diagram:

enter image description here

Paddy04

Posted 2012-05-17T08:32:38.240

Reputation: 39

Barcode scaners have USB Output, and in what they are pluged in. Are they act as HIDs on some machine or they have own memory and mode to send data in other way – adopilot – 2012-05-17T09:28:35.917

@adopilot, A person will scan a barcode, whose data will be passed through USB port. Need to dump it in Excel Sheet. just as the schematic diagrams shows. – Paddy04 – 2012-05-17T10:00:19.440

1I wonder how the requirement of excel came about, without being a db admin, but having experience connecting from outside to excel sheets, this sounds like the hardest way to achieve the goal. – None – 2012-05-17T14:09:03.307

Tbh, I don't think it will work. A web front-end done in php or some other server-side language, with a smattering of JavaScript and pointing to a database would be a much better solution (providing the skill-set is there of course). – hardlywired – 2012-05-17T23:42:03.247

Answers

If you try and access an Excel Spreadsheet (or Access Database) with more than one user you are very likely to run into filesharing or permission issues, I would reccomend using something like MySQL that would support it. You have to bear in mind with Thin Clients that, as they are a stripped-down OS they may not have the drivers for the barcode scanners - there's probably better ways of doing what you want.

tombull89

Posted 2012-05-17T08:32:38.240

Reputation: 6 533

Oh, so what if I plan to swap Thin Clients to small Computers running Windows XP? will that still work with MySQL running in Server? And multiple such small Computers can concurrently dump their data in the SQL Server? – Paddy04 – 2012-05-17T15:14:22.300

Most barcode scanners tend to show up as a USB Keyboard (or at least have a mode to which you can set them and make them appear as a USB keyboard, so I wouldn't worry about that). – MikeyB – 2012-05-17T15:37:13.677

Have you tried using something like Google Docs / Office 365 as either of these two spreadsheet products are designed for collaboration?

Kane

Posted 2012-05-17T08:32:38.240

Reputation: 141

I have to agree with @tombull89 the sharing issues could be a mess.

Why not have each scanner write to a uniquely named csv file in a target network location, and have an excel template with a macro that appends them as a single file from that directory.

You would essentially process the scanned content into excel, and can rename or archive the original csv content out to a processed directory once they import.

datatoo

Posted 2012-05-17T08:32:38.240

Reputation: 3 162

Good idea, but can this be automated? And all those csv files will be added into one Excel sheet? – Paddy04 – 2012-05-17T15:16:03.797

Yes you can create a macro that processes them. How many users are scanning? My suggestion is to have them all scan to individual files and have the macro read a selected directory into your excel file. How you are using this final excel final might be a question. If it is for a reconciliation of what should be you can then do comparisons etc. – datatoo – 2012-05-17T15:34:38.363

There will be around 20 scanners(possibly more). So now, each will have their own individual Thin Clients through which they'll store their csv file in a common location in server right?(will this be possbile? as each will have different users created, since Thin Clients will link up through Remote Desktop Protocol)

Then the excel's macro will processes them and create a single file? Seems good, Thanks :) – Paddy04 – 2012-05-17T18:59:08.117

Excel 2003 and later has a feature called shared workbooks. This should let you do what you want. It does come with certain limitations, but for your use case, these shouldn't be a problem.

Specifically, you cannot:

Create an Excel table
Insert or delete blocks of cells (you can only insert entire rows/columns)
Delete worksheets
Merge or split cells
Add or change conditional formats
Add or change data validation
Create or change charts or PivotChart reports
Insert or change pictures or other objects
Insert or change hyperlinks
Use drawing tools
Assign, change, or remove passwords
Protect or unprotect worksheets or the workbook
Create, change, or view scenarios
Group or outline data
Insert automatic subtotals
Create data tables
Create or change PivotTable reports
Write, record, edit, view or assign macros
Add or change Microsoft Excel 4 dialog sheets
Change or delete array formulas
Use data form to add new data
Work with XML data, including:
- Import, refresh, and export XML data
- Add, rename, or delete XML maps
- Map cells to XML elements
- Use the XML Source task pane, XML toolbar, or XML commands on the Data menu

You can of course access all of these features again after you unshare the workbook.

Another option is to use Sharepoint's Excel services REST API.

Though I'd personally just use a proper database as that's what you're really using the spreadsheet as. You can always use an Access/Jet database and then export to Excel spreadsheets, or you can use a MySQL DB and connect to it using OpenOffice/LibreOffice Base and export the data to Calc and save as an ODS spreadsheet.

Lèse majesté

Posted 2012-05-17T08:32:38.240

Reputation: 3 129