Results 1 to 9 of 9
  1. #1
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20

    Spreadsheet report formatting

    I have a database which happens to be for cemetery plots. I need to print a report that graphically represents the cemetery and colors each plot based on its status (vacant or used) and, if used, who is there.

    Currently, I can do everything I need with a combination of Access and Excel, but it's a tedious process. I export the data in table form from Access and import into Excel. Then I manually associate the "status" on each line of the imported sheet with the color-coding of a cell representing a plot on another sheet. Then I run a script to update the color coding. After that runs, I need to match the contents of another cell (the "owner") and link that to the associated color-coded cell on the other worksheet. All in all, it's a very tedious process and subject to errors. Adding plots is a problem because the manual matching process is required for the color coding and cell text to be updated properly.



    My question is this... is there some way I can set up a report that exports from Access directly with the proper color coding and text? If I could color-code an exported spreadsheet, I'll figure out a way for it to output in the proper layout. (probably by exporting blank cells)

    I know that I can generate a report with conditional formatting, and that gets me part of the way there. The problem is that I ultimately need this in Excel so that someone can click on the cell to see the details. Unfortunately, when I export the report, the conditional formatting isn't retained... everything is black on a white background.

    Or, is there something I can do differently to make the importation and processing in Excel more automated?
    - Dave

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,682
    You could try using Automation to write the Access table directly into an Excel workbook.

    Are the Excel worksheet(s) pre-formatted?

    Maybe you could post the Excel workbook? Change any names/sensitive info.
    Also if you made a copy of the dB and changed names/sensitive info..... maybe had 20 names entered.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,982
    not sure how you intend to achieve this in Excel 'someone can click on the cell to see the details'. Can they not use the access report? you can put code in the report to open a form to show the details

  4. #4
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by ssanfu View Post
    You could try using Automation to write the Access table directly into an Excel workbook.

    Are the Excel worksheet(s) pre-formatted?

    Maybe you could post the Excel workbook? Change any names/sensitive info.
    Also if you made a copy of the dB and changed names/sensitive info..... maybe had 20 names entered.....
    An example would help, wouldn't it? I've attached a file.


    I couldn't figure out a way to easily provide a sample of the database, so I'll summarize it here and can provide later if needed. Basically, the database has 3 tables - list of plots, list of sales and list of burials. They are all associated via the plot number. I output an Excel report represented by the Data tab in the attached spreadsheet.


    The plot number is used to associate the line of data with the plot on the Plot tab. The status is used to color the cell. Then the other data is concatenated as content in the cell.



    Quote Originally Posted by Ajax View Post
    not sure how you intend to achieve this in Excel 'someone can click on the cell to see the details'. Can they not use the access report? you can put code in the report to open a form to show the details
    To answer your question about clicking on the cell... this is simply done by sizing the cell so only the plot number is visible. Clicking on the cell lets you see the data that's in it but normally hidden due to the cell size.


    The challenge is that all of the associations between the Access report data and the cells on the Plot tab need to be made manually, which is a tedious and error-prone process. I'm looking for something where I can output this level of detail from Access directly.


    As I mentioned above, I can create an Access report that shows the plots in the correct layout with the color-coded status. The two challenges I'm currently facing are:


    1. I can't add the owner/burial detail in the cell because it makes the report formatting big and unreadable (from a layout standpoint). How can I set the cells/fields in the report so clicking on them reveals the details or opens a form or subreport with the details for that plot?


    2. This needs to be hosted online so multiple people have access to it. I can easily put a spreadsheet up online on Google Sheets. I'm not sure how I would host an Access database online, especially if it requires interactive reports.


    I'm open to any suggestions about how to make this work, either simplifying the export to Excel (eliminating the need to manually link all the cells) or by working directly from Access.


    Thanks.
    - Dave
    Attached Files Attached Files

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,982
    1. I can't add the owner/burial detail in the cell because it makes the report formatting big and unreadable (from a layout standpoint). How can I set the cells/fields in the report so clicking on them reveals the details or opens a form or subreport with the details for that plot?


    2. This needs to be hosted online so multiple people have access to it. I can easily put a spreadsheet up online on Google Sheets. I'm not sure how I would host an Access database online, especially if it requires interactive reports.
    re 1 - a number of ways - for you might use the control click or mousemove event to open another report or message box or repopulate a subreport with the relevant details. However 2 precludes that, access cannot be hosted online as such - each user would need to download a copy. So excel is probably the way you'll need to go, but that will have it's issues with security, primarily around preventing users from making changes - tie it down so they can't and you may find you cannot get the functionality you require

  6. #6
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    Quote Originally Posted by Ajax View Post
    re 1 - a number of ways - for you might use the control click or mousemove event to open another report or message box or repopulate a subreport with the relevant details. However 2 precludes that, access cannot be hosted online as such - each user would need to download a copy. So excel is probably the way you'll need to go, but that will have it's issues with security, primarily around preventing users from making changes - tie it down so they can't and you may find you cannot get the functionality you require
    I'm able to get the functionality I need with Google Sheets because I can set users with different privileges - including view only. That works fine for them.

    What I'm trying to do is get output from Access that I can import (or cut and paste) directly into Google Sheets without all the gymnastics in Excel to get the cells colored and populated correctly.

    Alternatively, I can host something else (like html pages) to get the functionality I need if that's an easier path out of Access.

    I would even consider hosting the whole database online if it gave me what I needed and there was a cost-effective solution that didn't require a lot of maintenance.
    - Dave

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,982
    if your plot table included fields for column/row identifiers, you could export to excel (and I presume google sheets) by looping through the table and putting values in the specified cells - and set colour formatting at the same time - automation as suggested by Steve.

    Or just export the table to excel/sheets and have a macro there to loop through the table.

    exporting to excel is straightforward enough - use transferspreadsheet

    for google sheets, here is a link https://stackoverflow.com/questions/...le-spreadsheet

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,682
    I haven't use Excel as I used to about 15 years ago, but Excel does have forms available.
    Have you tried designing a form in a worksheet. Don't know anything about Google Sheets, so don't know if forms are available there.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    dth122 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2014
    Posts
    20
    The problem exporting the table to Excel is that I lose my layout formatting.

    I may be able to do what I need to do in Access using a crosstab query and then importing that into Excel and using conditional formatting to do my color coding.

    A macro could work but would require a lot of setup to transpose the long table-style list into the properly-formatted layout.

    I need to do some more experimentation.
    - Dave

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 03-19-2019, 08:35 AM
  2. Replies: 21
    Last Post: 09-18-2015, 11:54 AM
  3. Create Report that looks like a spreadsheet
    By Hank153 in forum Reports
    Replies: 10
    Last Post: 12-10-2012, 10:08 PM
  4. Replies: 1
    Last Post: 03-05-2012, 04:21 PM
  5. Display report in spreadsheet style
    By w2vijay in forum Reports
    Replies: 1
    Last Post: 02-11-2010, 11:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums