I am completely out of my depth but have been tasked to create a specific excel spreadsheet based on a query in my database. I need to Excel spreadsheet to have the 1st 2 columns filled with the names of everyone in my org. Then i need the top row of the spreadsheet to have the months in order and the 2nd row have the days of each month in order. I basically want to make a spreadsheet calendar by day so I can load in personal days off to see who is available on certain days.
Create Excel from Access
- Thread starter Valentine
- Start date
I am hoping the button I push will fill in the all the fields for them but there are always going to be outliners that someone didn't submit their form in time and we have to input their days off manually in the spreadsheet.
I suggest a pivot table in Excel. Create a query to generate the data, paste the data into Excel on one tab, create a pivot table on another tab. Then set the formatting to make it all pretty.
Next month, or whenever you need to update the data, you rerun the query, repaste the data and refresh the pivot table. 2 hours to set up, 5 minutes to refresh.
Next month, or whenever you need to update the data, you rerun the query, repaste the data and refresh the pivot table. 2 hours to set up, 5 minutes to refresh.
- Local time
- Today, 07:32
- Joined
- Oct 29, 2018
- Messages
- 22,497
Hi. You can do all of that using Excel Automation. But what we're trying to tell you is that you may be trying to work too hard.
- Local time
- Today, 07:32
- Joined
- Oct 29, 2018
- Messages
- 22,497
So, we can't use PDF, because you want people to fill in the cells. However, that would also allow them to "change" what's already in the cell that came from Access. Is that okay? Just thinking out loud...
I just read online about linking the Excel spreadsheet to the database and running an append query to add the info to the spreadsheet. Would that work for what I am trying to do because I know how to do that, the pivot table and basically anything with excel is foreign to me. I just think that doing things that i know would be easier then trying to work with excel but learning new things is great too. I do not have a timeline on this it's just something i noticed was lacking and wanted to look into accomplishing it.
This is in my database now, which i want is when the user hits the save button I want the date fields filled in with the word "Off" in the Excel. If making the pivot and doing excel stuff is easier so be it, or if i can link and append is easier thats fine too, I just dont want to go 1 path and waste a bunch of time.
- Local time
- Today, 07:32
- Joined
- Oct 29, 2018
- Messages
- 22,497
Hi. Would you mind posting a link to the online article you mentioned? Thanks.
Man you are all over the place, but only letting us see through keyholes and cracks.
How about you give us a one paragraph explanation of what it is your organization does. No database or industry specific jargon, just tell us like we have no clue about your business what it does. Then, give another paragraph explaning what this database does for your organization--not this specific issue, but the database as a whole. Then finally, tell us what real world problem you are trying to solve. Don't tell us what you are attempting technologically, tell us about the real world issue you need help with.
How about you give us a one paragraph explanation of what it is your organization does. No database or industry specific jargon, just tell us like we have no clue about your business what it does. Then, give another paragraph explaning what this database does for your organization--not this specific issue, but the database as a whole. Then finally, tell us what real world problem you are trying to solve. Don't tell us what you are attempting technologically, tell us about the real world issue you need help with.
ok sorry for confusion. I am in the ARMY, the database I am making is accountability and soldier data tracking purpose. We have an Excel spreadsheet that tracks soldiers leave days to inform command who is available throughout the year. The database is newly created by me which has already consolidated 7 different tracking methods to 1 and I am trying to add another to that list. If I can get the user to input their leave into the database and once they hit save the Excel spreadsheet that tracks leave gets updated.
The allready formated and controlled tracker is in excel I just want to update it from access. If I make a new form in access to track the same data in the excel spreadsheet I have to go through so much BS to get it approved OVER the excel spreadsheet it really isnt worth it.
- Local time
- Today, 09:32
- Joined
- Feb 28, 2001
- Messages
- 30,003
Clarification, because I think you answered this by implication... but I want to be sure about the requirements. Are you creating a new spreadsheet every so often? Or is this sheet going to have a history going back in time for all the past absences?
If this is something that gets created periodically, say once per month, and just updated now and then, that solution is incredibly different in complexity from something that is created once and never loses data even if it is more than a year old.
Not only that, but if it is regenerated every now and then, there is the possibility that, because it would be relatively smaller (lacking tons of history) that it would be practical to update the Access tables and then just rebuild it all the way, from scratch, with every update. If it is too big because of its historical baggage, then a different method becomes significant.
EDIT: Not to mention that you ought to have a row with YEAR in it if it has enough historical range. So educate us as to that aspect of what is being displayed.
If this is something that gets created periodically, say once per month, and just updated now and then, that solution is incredibly different in complexity from something that is created once and never loses data even if it is more than a year old.
Not only that, but if it is regenerated every now and then, there is the possibility that, because it would be relatively smaller (lacking tons of history) that it would be practical to update the Access tables and then just rebuild it all the way, from scratch, with every update. If it is too big because of its historical baggage, then a different method becomes significant.
EDIT: Not to mention that you ought to have a row with YEAR in it if it has enough historical range. So educate us as to that aspect of what is being displayed.
It is an ongoing never lose data spreadsheet with a new tab created for each new fiscal year.
This is the top showing day by day.
The bottom showing the tabs of each fiscal year.
In between in the first column is all the names of every soldier, the top 3 rows are frozen.
This is the top showing day by day.
The bottom showing the tabs of each fiscal year.
In between in the first column is all the names of every soldier, the top 3 rows are frozen.
- Local time
- Today, 09:32
- Joined
- Feb 28, 2001
- Messages
- 30,003
This will require you to engage in learning how to manipulate Excel Application objects. You will have no hope of loading up and managing this any other way through Access.
The next question becomes trickier. How many rows will you require for personnel whose leave is being tracked? Because there will come a time when you might have more cells than a 32-bit Excel would allow, and there are compatibility issues to consider as well. Excel is "forgiving" in terms of data capacity since it uses sparse mapping, but there are limits to the number of rows. The idea of no more than 367 columns does limit what you have to keep, fortunately. That is 365 days + 1 for leap year + at least 1 more column as a row-header for identifying the person.
The Julian Day computation that is available in the DatePart function will help you in computing the column. The row computation might be aided by having a behind-the-scenes field for an assigned row number for each person so you don't have to search for the name. So mechanically, this won't be impossible. Just awfully tedious.
The next question becomes trickier. How many rows will you require for personnel whose leave is being tracked? Because there will come a time when you might have more cells than a 32-bit Excel would allow, and there are compatibility issues to consider as well. Excel is "forgiving" in terms of data capacity since it uses sparse mapping, but there are limits to the number of rows. The idea of no more than 367 columns does limit what you have to keep, fortunately. That is 365 days + 1 for leap year + at least 1 more column as a row-header for identifying the person.
The Julian Day computation that is available in the DatePart function will help you in computing the column. The row computation might be aided by having a behind-the-scenes field for an assigned row number for each person so you don't have to search for the name. So mechanically, this won't be impossible. Just awfully tedious.
Ok after reading all of this seems like I am not going to go that route. Switching if I get approved I want to try to create a monthly spreadsheet that shows days off in the same format as the yearly one. How do you format an excel spreadsheet through VBA in access?
- Local time
- Today, 07:32
- Joined
- Oct 29, 2018
- Messages
- 22,497
One way is to use Excel Automation.
How to automate Microsoft Excel from Visual Basic - Office
Demonstrates how to create and manipulate Excel by using Automation from Visual Basic.
docs.microsoft.com
