Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    fake Access in Excel (2000+)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fake Access in Excel (2000+)

    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.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fake Access in Excel (2000+)

    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.

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: fake Access in Excel (2000+)

    <hr>Maybe you should ask higher management whether they would like an advanced data reporting tool in Notepad.<hr>
    Thank you Hans

    I'll remember that one.
    Regards
    Don

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Fred

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fake Access in Excel (2000+)

    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.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fake Access in Excel (2000+)

    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).

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: fake Access in Excel (2000+)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

  13. #13
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: fake Access in Excel (2000+)

    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
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: fake Access in Excel (2000+)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •