Greetings,
I am trying to solve a problem that seems to require a tag team effort by excel and access. My current solution resembles a broken car that is only running because of the amount of duct tape currently holding it together. I am now thinking out of the box on a way to override the duct tape. Sorry for the poor analogy.
I need to identify current settings and positions, save/update, and call settings/positions from a few different objects in excel each time a generic plot is generated from an access record set.
How can I:
A. Catalogue the current formatting and position settings of the objects in a plot
B. Update these settings to a table
C. Call these settings when plots are generated from an on click event for a specific facility in a list box.
I think this is better than saving a plot for each unique facility because I need to avoid having 99 facility charts saved in a file ( if scaled to actual fleet size the amount of plots I would have to save in a file would be >10,000)
I am not asking that you solve my problem but because I am now out of the box perhaps I can receive Ideas how to approach this and ask better questions when I collaborate with the forum.
Example of stored values in a table:
Trend line position, color, etc.
textbox data position, formatting ,etc
Shape objects like boxes or lightning bolts, for current and updated values/positions
I figure, if this is possible, I would like to store the format values (color, shade, etc) and object positions (distance from top left corner for example) in a table. This way each facility can be double clicked on and have a custom chart called that resembles a how the worksheet looked before it was closed at a previous time frame(Formatting values and position of objects will be called to their previous state)
Background on my project
Access houses the data required to generate a plot.
The maximum amount of plots that would need to be generated at one time would only be one plot...the caveat is that there are 99 unique plots that would be generated over time and possibly scaled to 10,000 plots if the application is user friendly. I am trying to focus on small scale now and just accommodate the 99 unique plot challenge. I am thinking out of the box now because I am stuck. I can generate the plots for 99 different plots by using the double-click on action event for each facility in a list box. Double-clicking the facility generates a new workbook and plots the data from a record set created by SQL code running behind the form. My issue is that it always generates a new workbook. I want to be able to make changes, close the file and come back to the plot later with changes made. I DO NOT want to save 99 different excel spreadsheets because if this gets scaled up to 10,000 I do not want 10,000 spreadsheets saved somewhere locally or network drive.
Any given chart may have multiple trend lines applied by the user, boxes applied to chart to highlight areas of interest, callout boxes to detail a point in the plot (reason for high or low production) and possible symbols to relay information like a lighting bolt shape to indicate where their analysis left off.
I am trying to solve a problem that seems to require a tag team effort by excel and access. My current solution resembles a broken car that is only running because of the amount of duct tape currently holding it together. I am now thinking out of the box on a way to override the duct tape. Sorry for the poor analogy.
I need to identify current settings and positions, save/update, and call settings/positions from a few different objects in excel each time a generic plot is generated from an access record set.
How can I:
A. Catalogue the current formatting and position settings of the objects in a plot
B. Update these settings to a table
C. Call these settings when plots are generated from an on click event for a specific facility in a list box.
I think this is better than saving a plot for each unique facility because I need to avoid having 99 facility charts saved in a file ( if scaled to actual fleet size the amount of plots I would have to save in a file would be >10,000)
I am not asking that you solve my problem but because I am now out of the box perhaps I can receive Ideas how to approach this and ask better questions when I collaborate with the forum.
Example of stored values in a table:
Trend line position, color, etc.
textbox data position, formatting ,etc
Shape objects like boxes or lightning bolts, for current and updated values/positions
I figure, if this is possible, I would like to store the format values (color, shade, etc) and object positions (distance from top left corner for example) in a table. This way each facility can be double clicked on and have a custom chart called that resembles a how the worksheet looked before it was closed at a previous time frame(Formatting values and position of objects will be called to their previous state)
Background on my project
Access houses the data required to generate a plot.
The maximum amount of plots that would need to be generated at one time would only be one plot...the caveat is that there are 99 unique plots that would be generated over time and possibly scaled to 10,000 plots if the application is user friendly. I am trying to focus on small scale now and just accommodate the 99 unique plot challenge. I am thinking out of the box now because I am stuck. I can generate the plots for 99 different plots by using the double-click on action event for each facility in a list box. Double-clicking the facility generates a new workbook and plots the data from a record set created by SQL code running behind the form. My issue is that it always generates a new workbook. I want to be able to make changes, close the file and come back to the plot later with changes made. I DO NOT want to save 99 different excel spreadsheets because if this gets scaled up to 10,000 I do not want 10,000 spreadsheets saved somewhere locally or network drive.
Any given chart may have multiple trend lines applied by the user, boxes applied to chart to highlight areas of interest, callout boxes to detail a point in the plot (reason for high or low production) and possible symbols to relay information like a lighting bolt shape to indicate where their analysis left off.
Last edited:
