Excel as data source? (2000)

Topic

New Reply

I have a substantial catalog in the form of a multi-sheet workbook. Some of the fields are multilined. The owner of the data wants to use it for numerous ends, such as a printable Word version, a customer version with certain fields not visible, a “data entry” version with data input validation…

My suggestion was a “data source” which could be effectively “ported” to these various end uses. All the data is currently in a workbook, and I am able to deal with preserving the linebreaks and producing a tab-delimited text file as a data source for one of the other end uses. I tend to feel that the primary source of the data should be an Access database, considering the relative ease of maintenance in terms of data validation/restriction. Perhaps also, Access is a better way of generating required user views.

What I’m unsure of is whether I can export essentially text from Access, in the same way I can from Excel. I’m also unsure of whether there might be a better overall approach, starting with the existing workbook. I know the query is very general, but I would appreciate any suggestions from anyone who has had experience working with using a single source of data to produce multiple “products”.

thanks

Alan

Reply | Quote

Replies

Alan,

Working with 97 here at home but pretty sure the following carries over at least to 2000:
– you can use an Excel sheet as a table in Access. Under the file menu, there is an item to Get External Data. This allows the excel sheet to be brought into access (and converted – watch some of those conversions; there’s been some threads on this but I think going the other way – or linked, which seems like it might be ideal for your situation)

– you can then do queries of your tables, including whatever you brought into access from excel

– you can Save As on a table or query, which gives you your views. You can also create delimited or fixed width text data (check the help).

HTH

Fred

Reply | Quote

Thanks Fred.

There are lots of issues to address here, not the least of which is creating and maintaining a master *single* data source. All other views and presentations (spreadsheets, docs etc.) will need to reference this master, and changes to it must only be possible through the appropriate means. It’s for this reason that I thought of exporting from Excel and generating this “master” in Access. The Access software will provide better access security to various views for maintenance, as well as better data validation, I’d think, than Excel.

I’m not sure how I’ll go about filling a tabular Word template with references to the master data. Maybe I’ll have to again export a copy from Access, or maybe table data can be referenced directly. I’ll have to see how all of these issues go, as things develop.

Alan

Reply | Quote

Alan – as a general comment, Access will be a much better data repository than Excel. You will be able to produce different reports (manager, salesman, customer, etc) with come information highlighted or omitted much more easily, simply by defining different report layouts. You are also right that the data security and validation will be better. Using Excel as a database (even a flat-file) is a lot of work – it’s tough to coerce Excel to do the job. Access is built to do this kind of work – it may not be as immediately accessible as Excel, but within a short period of time the Access solution will be much more robust. Have you ever had somebody “sort” your Excel data, but not include the entire data range? I rest my case!

I am not sure what you are trying to do with the Word template – if it cannot be replaced by an Access report there are a couple of approaches that I have used (I am an Access newbie – but the loungers over there are just as helpful as the folks in this corner). If the data you want fits nicely into a query, you can set up a Word mail merge main document and specify the Access query as the data source – this works very well if you are using the same subset of data repeatedly, and you want better control of the formatting and document layout than you can get in Access – a periodic form letter is a good example. I have a database where the required subset of the data changes often – this could be set up as a parameter query in Access, but I decided to do it a little differently. I use some list boxes and option buttons to allow users to select the subset of the whole database as they require. I have a command button to export the query results to Word (or to Excel, in some cases), which creates a Word table that is useable as a mail merge data source in its own right. I do this instead of a direct link to a query to “freeze” the data so that a mistake in selecting the sub-categories of output doesn’t screw up a live mail merge. In addition, most of the people who use the data are (reasonably) familiar with Word, but are intimidated by Access (there seems to be an inverse relationship at work there ). The export to Excel works the same way – I can manipulate -say- lists of suppliers in Excel representing a sub-set of our whole database and then e-mail the file to someone who does not have security rights to the Access database. Again, the users are more familiar with Excel, and if they screw things up after it leaves me it’s their problem not mine (they just ask for another copy, anyway)!

Reply | Quote

Thanks Dean. This is the sort of info I was after. The mail merge table sounds like a great way of holding the exported data prior to formatting. The Word template (which is just one possibility) is used as the basis of a printed catalog of products, something like that which would be produced by desktop publishing software – so lots of heavy formatting & pictures. I don’t think an Access report would meet the requirements, but I’m a rank beginner with Access, so might be wrong :-).

The people who maintain the data are not that computer savvy, but will have to get used to Access. As you do, I want their mistakes to be their problem, not mine ;-). The kinds of views they need are fairly simple – essentially just blocking out certain columns for various users. For instance, some “owner” users will be able to change Descriptions, but not Price structures. Again, your suggested methods, using Acces, seem conducive to that.

Their data has been ported to a website using MySQL, which seems a bit of a shame to repeat, but I think that in the long run, tackling the whole show independently might be better. Trouble is that the website developers want the data in Excel (not Access???) so that’s where the need to export from Access to Excel comes from. Anyway, all this info is really helpful in establishing the “big picture” of what I’ll need to do. Then it’s just a matter of detail – that bit sounds really easy :-))

thanks again

Alan

Reply | Quote