fake Access in Excel (2000+)

Topic

New Reply

Hi All,

I need to be able to replicate some Access features in Excel. Why? Because people at work are familiar/comfortable with Excel and not Access. Maybe because not everyone has Access even loaded on their PC (corporate environment).

In particular, I can have an Excel sheet that has my raw data. Not a problem so far. I want to be able to create the equivalent of an Access Report (actually 2 or 3 reports for starters) using that sheet and its data.

Let’s call the sheet with the raw data “Raw Data”.

From the Raw Data sheet, I need to be able to select rows/records that go into the report. Not a problem adding a column in Raw Data that does that per report (a “Report1” field, a “Report2” field, etc since a given record can go on multiple reports). One problem is that I might have to make 4 passes thru the records for one type of report; this report shows all records grouped together where the Status field is “Red”, then all records where the status field is “Yellow”, then “green”, then “blue”. Status can change over time but the records on this report have to group all records with the same Status.

For another report, I need to have the records come out in “Item” order (the “Item” is like an Access Key but we use “D1”, “D2”, etc to identify the records and increase the Dx when we add a new record).

Further, the “main” fields may not all show on a given report (by “main” fields I mean those that provide useful info as opposed to those that I add for control purposes like the “Reportx” fields described above). For this, I was thinking of some kind of “report template” that would indicate the columns/fields needed for a particular report. This too might change over time.

Another issue that has to be addressed is that a column might be added to Raw Data. To keep things logically ordered, it might be necessary to insert this column between 2 existing columns. I can see this might create a problem with the report template I mentioned above; maybe naming each column would overcome this.

Some of the cell entries have >255 characters, which may not be a problem. I just mention it here because I know that copying entire sheets where such a cell exists results in a truncation of the cell entry.

I’m also thinking that the output could be a new sheet in the same workbook. But it would probably be better to create a new workbook each time a report is generated. That’s because I don’t want to mail the entire workbook to upper mgmt that needs to see the report (even if I hid the non-relevant sheets). Further, I envision some VBA needed for this, so don’t want mgmt to have to deal with the “macro” warning when opening the report.

I also envision creating some kind of template sheet for each report type so that the column headers and col widths are already in place (the widths in the output report are important). But the “template” should not be in a real template folder in case I need to give this to someone else to deal with while I’m on vacation (as turned out to be the case for the last 2 weeks). So everything should be self-contained in one folder.

I think that’s about it for right now in terms of what I need to fake. Don’t need queries (unless we define a filter for some of the things above); don’t need forms. Any thoughts on this? Has anyone done anything that might serve as a starter? Basically, this seems like some kind of report-writer for Excel.

TIA

Fred

Reply | Quote

Replies

People not being familiar with Access is not a sufficient reason not to use it. It’s the task of the database designer to create a simple and intuitive interface. In a well-designed database, users hardly know they are in Access.

If not all users have Access, you can install Access Runtime on their PCs. You need to get the Access 2003 Developer Extensions; this gives you the right to distribute Access runtime (no limits). See Obtain and deploy the Access 2003 runtime. Although it’s not free, the cost is probably less than that of developing a full-fledged reporting tool in Excel.

Reply | Quote

Hi Hans,

Thanks for the response. I totally understand the idea of designing the DB so people don’t know they’re in Access, having designed such an Access DB myself. In fact, I don’t even need an “interface” to the report – it’s not like the intent is going to allow them to enter, for example, a range of dates (via a query) and then spit out records that meet that criteria. What records and fields they see are pre-determined by us down below; it’s just that the specific records can change as well as their ordering. Further, we have several “levels” of users that need to see different things.

However, installing Access Runtime on other people’s PCs is not an option. This is a corporate environment. While I don’t need to do this for everyone in the corporation – just those that I work with, there are still several tens of people, including relatively high mgmt, that need to look at the reports. Installation of new software by users is heavily frowned upon by Corp IT (ain’t that usually the case). Once you do that and you have a problem, just try getting help; they blame it on your non-standard installation.

I’m not sure that my rqmts were the equivalent to a “full fledged” reporting tool in Excel; I certainly hope not. I did do a google search on “Report Writer” + Excel but didn’t find anything useful.

Maybe what I’ll try is bringing the Raw Data sheet into Access (which I have on my PC at work because I knew to ask for it) and work with Access’s report feature. Do you know if it’s possible to bring an Access report back into Excel? Maybe I’ll give that a try. Or is it possible, via Automation from within Excel, to invoke an Access report?

Basically, what I’m trying to do is avoid having to recreate the report every time we need to provide it to upper mgmt. I guess it could be done with hidden cols and some combination of filtered or sorted rows. It if were just for upper mgmt, it would not be too bad. However, before the report goes to upper mgmt, every one down below needs to see exactly what their bosses will be seeing. Of course, there are endless rounds of comments while that’s going on and that’s where the time gets spent.

Thanks.

Fred

Reply | Quote

Although you can export an Access report to Excel, it won’t look much like the original. Al graphic elements such as lines will be lost, as well as much of the formatting.

You can open an Access report from Excel using Automation, but this requires Access to be installed on the PC, which you say is not feasible.

Maybe you should ask higher management whether they would like an advanced data reporting tool in Notepad.

Reply | Quote

[indent]

---

Maybe you should ask higher management whether they would like an advanced data reporting tool in Notepad.

---

[/indent] Thank you Hans

I’ll remember that one.

Reply | Quote

Hi Hans,

Thanks.

Notepad – oh, come on. We know there are better text editors than Notepad. And even MS’s Notepad has been getting better with each Win version. Maybe I’ll wait for Vista’s version.

Fred

Reply | Quote

Hi Hans,

I just wanted to complete this thread by mentioning what I did to create a “report capability” for Excel. In case anyone else wants to know.

Basically, I used the Advanced Filter tool with some copy/paste and pre-formatted output report sheets.

Recall, I had what was essentially a set of rows (records) and columns (fields) in a sheet. A flat data base, table, whatever you want to call it. The fact that my table doesn’t start in row 1 doesn’t really matter.

I added one column for each report that I wanted to create – think of this col as the “show record for report x” col. The entry in that col on a given row is “yes” (but it could be anything) if that record is to be produced for a particular report. Right now I have 2 reports so 2 extra columns. The decision of entering “yes” is pretty much manual. I only have about 20 records but they could grow to about 100.

I defined an Advanced Filter criteria range for each report. That has the columns I want in the report plus the “show record for report x” column added per above for that report. The only criteria is the “yes” in the extra col.

I have a separate area of the sheet where the filtered output goes, excluding the extra “show record for report x” col but with just the cols for the report. The filtered records appear when I run the Advanced Filter tool.

I then copy/paste the filtered records into another sheet which I could insert as a new sheet each time I need that report type. The sheet is preformatted with the cols (widths, shading, lines) to match the filtered output (where I don’t care about widths), headings, etc.

So a few things left to do:
– I’d like to automate the above process, recognizing that the number of records will grow (dynamic ranges). A button per report type driving a macro to do as much as possible.
– I also need to sort the filtered records since all records with the same “status”, which can change from week to week, have to appear together in the ouput. I also need to shade the records to match the status (which is a color of red, yellow, green, or blue). Not sure about this yet.

For the most part, I don’t think Access would have given me any advantage over the above. Of course, if I had some relationalships between tables, I think a trip thru Access would have been necessary.

I may post back on some of the to-do’s but I know there are posts to cover some of this.

Fred

Reply | Quote

Hi Fred,
Did you know that you can use the Database query feature to extract data from one Excel spreadsheet to another? You can just setup a querytable for each report that extracts the data using the relevant ‘Include in report’ column with a criterion of Yes. You can set the wuery tables to refresh automatically when the workbook is opened too.
FWIW.

Reply | Quote

Rory,

Thanks for the input. I’ve never used the DB Query feature of Excel. I knew about it but didn’t have a need until now.

Now you’ve given me another tool to try. Before diving in, do you know of anything that might provide some examples? Maybe some threads on here that used it as part of a solution? It sounds, at the very least, that I won’t have the constraint of having the filtered output be on the same sheet as the input.

I’ll give it a shot when I have time.

Fred

Reply | Quote

Hi Rory,

I was able to do a little investigation on the DB query feature, so have some questions.

Am I right in understanding this is based on the Data | Import External Data dialog?

If above is correct, this seems like it’s geared to working in 1 workbook to import data from ANOTHER workbook (or other source). I didn’t see anything to suggest I could use this within a workbook. I think your email suggested this was to go from one workbook to another. Did I miss something?

The approach I was converging on was going to use a source worksheet and an “output/report” worksheet. Is there a benefit to using the DB query feature vs the approach I outlined?

One benefit, it seems, is that my “source” sheet doesn’t get messed up as much in defining criteria and output areas. Is this correct? I saw that I have to define my criteria somewhere but I’m not sure where that would be. If not in the source or output sheet, then the DB query feature is better.

Another benefit is that the DB query feature might give me an output sheet that is closer to my final output than the Advanced filter and maybe with less trouble unless I automate my approach with a macro. Is this a correct understanding?

The last benefit, which is based onsomething I didn’t mention in my summary, is that what I might do in my approach is make a copy of the file after all my work is done and delete all the OTHER sheets so that the audience only sees the “report” sheet. Yes I know I could hide the other sheets. Yes I know I could copy the “report” sheet to a new workbook except that there are a few cells where the number of chars is >255 so I run into that limit and get a warning msg. This gets back to an earlier question about the DB query feature working across 2 workbooks.

TIA

Fred

Reply | Quote

Fred,
Yes, that is the dialog I meant. You are correct that it is geared towards getting data from an external source, but you can use it to get data from the same workbook too. However, from what you said, I assumed you would want either a separate workbook for each report, or a separate reporting workbook, so that report users don’t need to see the data.
You can define the criteria in the query editor (just as you would in the Access query builder) so you don’t need to store the criteria in the worksheets themselves (though you can also set it up so that it will use cells as criteria and refresh whenever they are changed).
HTH

Reply | Quote

Hi Rory,

I played around a bit with the DB Query (DBQ) before leaving work earlier. So I got a little bit of a taste for using it.

It was not obvious that I could have the output directed to the same workbook as the input. I can’t remember that different files were a rqmt, just not obvious it could be the same workbook.

For the people getting the output, I’d want a separate file from the input. I don’t want to start to have to hide sheets. At very least, output, even with Advanced Filter (AF), would have to be a properly formatted worksheet separate from the sheet with the input.

I did not see using the DBQ how to set the criteria so it was picked up from the same workbook as where my data lived.

But if I set the output to be in the same file, just a different sheet, as my input and create my criteria in that same workbook, then there becomes less difference between AF and DBQ.

A few observations that I’ll pass along in comparing AF with DBQ:
1. Defining the Query: With DBQ, the query definition is contained in a separate file. This is good in that it does not take room in your Excel where the data lives. But you have to browse to the file when creating your output (usually in the …My DocumentsMy Data Sources folder). You can edit the file while going thru the dialog to import data. Using AF, your query is on the same worksheet as your data. Per above, I did not see how to direct DBQ to get the query from a workbook. AF requires a “criteria” range with rows defining the values a field has to meet so that the record is selected. DBQ defines the record selection criteria during the query definition.

2. Defining the Output: With DBQ, the output of the query is a new workbook. With AF, it is an area of the same worksheet where your data resides. Again, I didn’t see how to direct it to the same workbook as where the data lives. At a minimum, I’d want the output sent to a worksheet separate from the sheet with the input.

3. Maintaining Formatting of the original data: DBQ’s output did NOT maintain the format of the original data. AF’s filtered records did maintain the original formating, even for numbers with custom formats.

4. Sorting Records: AF does not allow a sort of the filtered records. DBQ allows you to define 3 levels of sort of the filtered records.

5. Selecting Fields for Output: AF’s criteria range or output range matches the column headings from the original data records to determine what columns/fields to include in the output; this is usually a copy & paste of the column headings. DBQ provides a 2-column dialog where you select a field and then click an arrow to move it over for inclusion in the output. Either approach allows one to change the order of the output fields relative to the original fields. But DBQ is probably easier since you just select a field from the output side and then just click “move up” or “move down” arrows.

6. Creating Final Report: Even though I started a new file based on a template file that had 1 sheet just with my desired column headings, when telling DBQ where to put the output, it inserted new columns for the output. I had to move the column headings back over the output. With AF, I would copy and paste the output to another sheet that was preformatted with my column headings.

7. Refresh when data changes: Although I didn’t try this, I’d think that DBQ has the edge here.

Anyway, I probably have to do some more experimentation. Thanks for the tip.

Fred

Reply | Quote

Fred,
My assumption was, based on your post, that the criteria for a report is simply whether a set column contains Yes. In the Query grid, you can set criteria for any column (you have to click on the Show criteria button).
I will try and address your points in order:
1. You can choose whether or not to save the query definition to a separate file. If not, it is effectively embedded in the workbook that retrieves the data.
2. The output goes wherever you tell it to go in the workbook you were in when you start the Import data procedure. If the open workbook is the source workbook, then you can put the data on any sheet within it. Note that you can only query the same workbook after it has been saved somewhere – you then simply browse to it as you would to another file.
3. If you format the destination cells, that will take care of your formatting problem – I assumed that was the idea behind the template!
4. Correct.
5. Correct.
6. It is easiet to set the headings in the query grid.
7. You can set the querytable to refresh at set intervals and/or whenever the workbook is opened. You can also refresh manually.

HTH

Reply | Quote

Hi Rory,

Not sure about your assumption. I know it would be better to attach a sanitized sample but it would take a little work to create the sanitized sample; so if we can get past this issue, it might not be necessary.

The source has a heading row with col/field names and and rows/records.

Each report has a subset of cols. This is pre-determined based on the report. What’s in my xlt template is the heading row with the subset of cols for that report; I was envisioning a separate template for each report type (I’ve only worked with 1 template so far).

I’ve added cols to my source to allow specification of whether records appear on a given report. Working with 2 reports for now, I’ve added cols with values in the heading row, for example, “Show on Report A” and “Show on Report B”. In the rows under the heading row (the records), I enter “yes” in the respective cols if that record is to be shown on that report. For each criteria (for each report), I include only the corresponding “Show on Report ‘x'” field and that’s what my criteria uses to select records based on “yes”. There really is nothing else in the record that allows a selection to be made for the report.

In the filtered output, I have to delete the “Show on Report ‘x'” column.

As it is, there is another col that I need on the output to “create” the report but not to show. There is a status col (Red, Yellow, Green) that is used for 2 purposes: shading the record in the report with the color and sorting the records so all “red” records are consecutive regardless of the order in the source. Status can change over time.

So I place the “Show on Report ‘x'” and “Status” fields on the right of the output so they can be easily deleted.

I think I understand all of your responses to my points except to #6. First, you mention a query grid – I don’t recall seeing any such thing. I know what this is in Access but didn’t see anything like this in Excel. I also don’t understand how that helps the undesirable behavior of the Query process inserting a bunch of cols and pushing my headings over. For example, suppose my headings were in cols A-L in my template. After running the query in a new file based on that template, my headings have been pushed over to start in col O and the filtered data is in cols A-N (remember those 2 extra cols that I need for “Show on Report ‘x'” and Status).

If there’s no way to avoid these inserted cols, it’s not a big deal. What I’d have to do is move the data in cols A-L to be under their displaced headings starting in col O (this may solve the formatting issue also; I was doing the reverse – moving the headings to be over A-L) and delete cols A-O after using Status for coloring (the Sort in the query definition already puts the records together by Status although I have to do something so the order is red, yellow, green – not alphabetical).

Anyway, I have a long weekend. I’ll try experimenting some more.

Thanks.

Fred

Reply | Quote

Fred,
I think the first thing I need to clarify is that in the last step of the dialog you have the choice of returning the data to Excel or editing the query in MSQuery. If you choose the latter, you will then see the query grid I am talking about. This works a lot like the query builder in access in as much as you can choose which columns to return, set criteria for the report, set column headings etc. By criteria, I simply meant that you would add the ‘Include in report A’ column and set the criteria to Yes. From what you said initially, I understood that that is the only criterion involved. Is that not the case?
Using the query option, you don’t need to have headings already set up in your template – the query will create them for you. You can use the Format-Columns option to hide the criteria columns.
HTH

Reply | Quote

Rory,

Thks a lot for the additional info.

You were correct that the only criteria for including a record in the output is based on the “Include in report A” col.

I had noticed the last step for editing the query when first creating a query that had the 3 choices. What is a cube?

I had not chosen the edit query while creating the query. Once I had it done and was running the query, the External Data toolbar popped up. I chose the first icon to edit my query and it put me in the middle of the dialog. I noticed that the Query Wiz popped up and put me at the point of choosing tables and columns. Since my cols were already shown, I clicked next.

What I saw on the next screen threw me. There was nothing filled in in the “Only Include Rows Where” even tho I had included “Show in Report x” field. However, I noticed that field was bolded on the left listing of available cols. When I clicked on that, it “re-populated” the “Only Include Rows Where”. The sort screen was ok. When I next got to the Finish screen, I thought I was done. By the way, this screen does not give the 3rd choice of creating a cube when editing. I guess that’s only available when creating something new.

If in the 1st screen of the Query Wiz I can choose tables and cols, does that mean I can create a relationship between tables so I can have a 1:n kind of mapping (eg, task with multiple people)?

So I do now see the Query grid. It seems a little different than the Access grid. The Query grid has a table area (same as Access) and a query grid that just has rows for criteria (a subset of Access). But the Query also shows the resulting records (but not sorted even if sort criteria were specified). This last part is not in Access.

I see by double clicking the “resulting records” grid at the bottom that I get a small dialog that allows me to have a different col heading that the field name; I also see I can do this thru the toolbar. I know you can do something like that in Access. I see that I can move cols around like in the Access grid.

But I didn’t see anything that allows me to set a field to be not visible on output (my “Show in Report x” and Status fields). Did I miss something? I saw where I could hide the cols in the query grid. But when I ran the query, I still got the “Show in Report x” field. Not a big deal to delete the col. I need the Status col anyway temporarily to shade the rows.

Still not sure about the formatting the data. I see in the Query grid that there is a “default” format for my dates that includes the time set at 0. It looks like I have to move the resulting data to the pre-formatted cols as I mentioned in my last email. Did I miss something here? I tried all kinds of selections and double clicks and right clicks.

I’m getting closer.

Fred

Reply | Quote

PS I should mention that the major downside to the query option is that the paths to the source data are hardcoded within the file. If you move the source file, you need to recreate the querytable or alter it in code.

Reply | Quote

Hi Rory,

This issue about the paths raises a concern but maybe it’s just what I’m looking for.

If I put everything in the same file (criteria and output) as the source data, then I don’t have to worry too much about where things are. I was recently away for 2 weeks and gave the file to a co-worker to update and generate “reports”. This was before any of my recent experiments with Advanced Filter and DB Query. She ended up creating a separate file containing just what we were going to show to upper mgmt, which was a subset of records and fields. She never updated the “source” file. On my return, I had to go back to recreate the source based on her updates to her “report” file. Very undesirable! Hence my experiments (which I was planning on anyway before vacation but this accelerated my work).

If I have everything in 1 file on my work PC in a folder named “x” and I go away for another 2 week vacation and give her the file, will she be able to use the file and run the queries?

If everything is in 1 file, I’m not sure that it’s much different than the Advanced Filter approach. Sounds like it’s a bit cleaner.
– I don’t have a separate output area on my source sheet (ie, the sheet where the source is and from which I copy/paste onto a “report” sheet); the filtered output goes directly where I point even on another sheet.
– the criteria is “effectively embedded in the workbook that retrieves the data”. I’m not sure I understand this. Sounds like it’s NOT a separate criteria range as with Advanced Filter. Sounds like it’s hidden somewhere. But hopefully editable?

If I have separate files for the query (which seems to create 2 files: x.dqy and I think x.oqy or x.odc), the source, and a template for the output but ALL in the same folder, is this portable over to her PC?

I obviously need to do some work on this before I take my next vacation.

Thks.

Fred

Reply | Quote

If you use the Database Query method, all paths are hard coded, even if the link is to the same file you are in. Therefore if you move the file, you are moving the source file and the links will be broken. You can get round this via code in the Workbook Open event, but I think it is better to have the source data in a network drive where anyone who needs to change the data can access it, and have the reports as separate workbooks with links to this source file.
HTH

Reply | Quote

Having all the links in the file is not good. I assume if the folder path is something like C:workfred for work and C:homefred for home, then we have different folders and links are broken.

For one thing, if I want to experiment at home, this rqmt is prohibitive. If I need to give the source file to a co-worker, this is also a problem.

At work, I’m not sure how easy it is to put the source file up on a network drive. IT might have to be involved, questions answered, money transferred from my org to IT. Too much trouble perhaps. And there are other people in the org who have the same need as I do for creating views of essentially flat file source DBs stored in Excel.

Do you have a sample of the code for the Workbook Open event to restore links? I’m not even sure what has to be put back where. As messy as this is, it might be the way to go. Once the code is written, it’s easily adapted for other situations. For example, I’m envisioning a text file with the link info, where one version of the file is for home, another for my PC, another for the person who subs for me. If all the files, including the text file, are in one folder, then the Workbook Open reads the text file and restores the links based on it. That way, I can move the source around.

By the way, is the file you’re talking about one of those extra files created by MS Query? I was looking in one of them (.odc or .oqy) in the “My Data Sources” folder at work and saw what looked like some kind of linking info. The other file (.dqy) seemed to contain the actual query info.

I would plan on having the report file as a separate file. That seems like the easiest. That way, I can recreate it or refresh it any time and not start making copies of files and deleting the other sheets.

Fred

Reply | Quote

Hi Rory,

I did a little experimenting on this issue of the links. I think it’s easily overcome. Here’s what I found.

– if you rename or move the source file, the import will NOT work
– if, on the other hand, you rename or move the query file, the import will work assuming the source has not changed it’s location

The query file xx.dqy is nothing more than a text file. Mine looks like:
====
XLODBC
1
DSN=Excel Files;DBQ=C:Documents and SettingsFredDesktopWorkIssue Tracking-v4.xls;DefaultDir=C:Documents and SettingsFredDesktopWork;DriverId=790;MaxBufferSize=2048;PageTimeout=5;
SELECT Risk_DB.`Item#`, Risk_DB.`Activity/Description`, Risk_DB.Security, Risk_DB.`Non Trgt Arch`, Risk_DB.`Reliability/_Maintainability`, Risk_DB.`Date Risk First Reported`, Risk_DB.`Form Initiated? (NO or date)`, Risk_DB.`Form Approved? (NO or date)`, Risk_DB.`Long-term Risk Elimination Mechanism`, Risk_DB.`Elimination Target Date`, Risk_DB.`Review Period`, Risk_DB.`Next Review Date`, Risk_DB.`STATUS_(Note 2)`, Risk_DB.`Executive Dashboard?` FROM Risk_DB Risk_DB WHERE (Risk_DB.`Executive Dashboard?`=’Yes’) ORDER BY Risk_DB.`STATUS_(Note 2)`, Risk_DB.`Item#`

Item# Activity/Description Security Non Trgt Arch Reliability/_Maintainability Date Risk First Reported Form Initiated? (NO or date) Form Approved? (NO or date) Long-term Risk Elimination Mechanism Elimination Target Date Review Period Next Review Date STATUS_(Note 2) Executive Dashboard?
=====

By editing the DBQ= and DefaultDir= parameters, you can put the source file anywhere. It looks like the SQL and the column headings make up the rest of the .dqy. I’m wondering if it’s possible to create a relational data base using several excel tables. If not by editing the Query as I asked, then it seems almost definitely doable by editing the SQL in the .dqy file (eg, bring in both tables to Access with fields set up to relate the table, create a query based on the related tables, view the SQL, copy/paste the SQL back to the .dqy file).

So my plan is to create a .dqy file with different sets of the folder-related parameters and put them wherever I’d have the source file (about 3 locations for me and one for my co-worker).

I think your suggestion of editing the links is probably not to hard to implement but beyond me. What I see needing to be done is to
– assume both the query and source file are in the same folder
– open the source file
– find out the path name for the source file
– edit the folder-related parameters to reflect the current location of the source file and save the .dqy (in my case, there may be several .dqy files, one for each view, so a way of choosing which view/.dqy would be needed at this time – show all .dqy files and have dialog allow selection)
– proceed with the import using the just-edited .dqy and place its output either somewhere in the source file or in a new file (perhaps based on a template in the same folder; maybe use the file name part of the .dqy as the file name part of the template)
– do whatever as far as formatting goes (I don’t know if this is resolved yet per previous observations) and creating a stand-alone file for others to see

Thanks for all of your help. I think I need to implement what I’ve learned but I think I’ve got all the pieces for now.

Fred

Reply | Quote

Hi Rory,

My last experiment was to create 2 trivial related tables in Access and then copy/paste those back to Excel. The tables are
– “people” with fields “firstn”, “lastn”, and “personID” as the key
– “things” with fields “item” and “personID”.

When I went to create the query in Excel by naming both tables and their fields, I got a message from MS Query saying it could not create the relationship but that I should drag the related field from one table to the other. I did that in the top part where the tables are shown and then examined the SQL. Although slightly different than Access’s SQL for the same relationship (even discounting the naming of the tables’ files in Excel/Query), it worked fine and produced almost the same results. The difference was that Excel/Query included the personID when building the SQL from the dragging; however, this could be edited out from the SQL in the .dqy file (and also from the default row heading that’s produced in the .dqy).

Access SQL:
SELECT people.firstn, people.lastn, things.item FROM people INNER JOIN things ON people.personID=things.personID;

Excel/MS Query SQL:
SELECT people.personID, people.firstn, people.lastn, things.item, things.personID FROM `C:Documents and SettingsFredDesktoprltnl db for excel`.people people, `C:Documents and SettingsFredDesktoprltnl db for excel`.things things WHERE people.personID = things.personID

I’m finished experimenting.

Fred

Reply | Quote

Who’s actually running the report? If it’s something that you run every so often and then need to distribute, you can just create an Access db linked to the Excel sheet. Run the report whenever you need to and export the result (or print it to a PDF writer). If you only need to distribute the report itself (or a handful of reports), you don’t need to send all the underlying data with it…

From reading your original post, I think what you want to do is certainly possible in Excel VBA but you do need to make sure you’re not creating work unnecessarily.

Reply | Quote

I agree, you can setup the connections between the db and Excel. Almost none of our people have Access. I have a four step (with four files) process, with Excel VBA to pull data through EssBase from our main frame. Then a second Xl file to accumulate ongoing data. Then a third step is to setup up display data in an XL file (we keep a running 13 week view, which we can choose the ending period for any view). And then each graph/table is linked (initially by VBA) into Powerpoint. Everything is done with formulas after the EssBase pull. With 99 markets, and 25 channels for each, plus three charts for each it is a rather substantial project. And the entire process takes only 45 minutes, 25 minutes of it involves the VBA pulls. I never touch the in-between worksheets, setup tables nor any graphs/charts.

I used this arrangement in a simpler process, and reduced report time from 10 hours, 15 minutes (and it could be faster with some code, but I have many other projects to do).

Reply | Quote

Hi Waggers,

After some more thought, I’m not sure about the best way to go.

For example, I know that I can link an Excel table into Access. So writing a report definition in Access would not be hard for me to do. Exporting the report back to Excel is also doable. I tried that with another Excel DB I had. Two observations on the export:
– for some reason I couldn’t find, the last 2 columns on the Access report got switched around when looking at the resulting Excel spreadsheet; not a big deal, cols can be rearranged in Excel if necessary
– I still have to do all the formatting in Excel and maybe add some stuff; this is what I was trying to avoid.

I’ll admit that my savings won’t be as much as Rich’s. I think if I were to start with an Excel table and create my output in Excel with a sheet that was already pre-formatted with col widths and col headings, my savings might come down to an hour at most. But when you’re working on other things, an hour is a lot of time.

Fred

Reply | Quote