need ideas, db using excel link except columns need to be rows

[sum]

just got into this scheduling job and the current scheduling tool is an excel sheet that extends columns to the right for dates, up to years at a time. like this......

date | 6/1/13 |6/2/13 | 6/3/13
day | saturday | sunday | monday
equipment1 | washed | lubricated | ready for use
equipment2 | broken | troubleshoot | fixed

so the dates extend to the right and exceed 255 columns until archived every once in a great while. items get input on this shared spreadsheet from different sections and this thing gets updated often. there are things on this spreadsheet that get used over and over on different sheets of the workbook but using an access database would make a whole lot easier. right now, different sections have data on different sheets and the same data gets input on the calendar sheet depending on if the user remembers to do it or not. so i find inconsistancies all over the place on this thing.



my best idea for improving this is that the scheduling calendar spreadsheet part would stay and the rest of the sheets could go away because most of the data is repeated on the calendar sheet anyway. being able to link this calendar sheet would allow everyone to still schedule their events in the same way they are used to and also allow me to get more use of the data without having to look for inconsistancies first.

the biggest problem with this idea is that the calendar dates flow to the right as columns and exceed 255 which is the cutoff for linking an excel sheet in place of a table. this data being in an access database should idealy be converted from columns to rows or records so that there is a record for every day and not a column or field for each day. i thought if there was a way to link the columns as rows would be great, but i dont know how i could do it. also, the link would have to be dynamic because of how days pass and then do not need to be kept in the active group of records. after the day has passed, we do not need to schedule anymore, it can at that point be archived into a access table. but on that note, the link would never start on excel cell A1, it is more like start QJ3 ish or whatever column is current day or week and go out maybe 3 months of data at a time, like maybe UJ13 for the data end. so this link would need to be dynamic somehow, and again i dont know how to do this either.

any ideas, thanks in advance.

[Dal Jeanis]

First, you can only import 255 columns at once, but you can import the next 255 into a second table. Does that help?

Second, you could probably save yourself a giant headache and set the value of "every once in a great while" to "at the end of every quarter".

[sum]

I would like to build an access db that would allow me to use the calendar data better. The main problem being that the Excel calendar has lots of columns that need to be rows in access.

[alansidman]

This link may be helpful

http://www.datawright.com.au/access_..._using_VBA.htm

[Dal Jeanis]

Just off the top of my head, you could automate a copy (from old workbook) & paste-transpose (to new workbook) then import the transposed worksheet to Access. Doesn't sound difficult. You'd have to check the limitations on the paste-transpose, and I'd expect you'd want to only paste data rather than formulae.

[sum]

thanks for responding you two. i am looking at the function on that link from alan, looks like that might help, just havent had a second at work to try it out. i will let ya know.

and dal, i would like to automate as much as i can. when it comes to a manual copy and paste, it takes a lot of time and this would need to update often.

[Dal Jeanis]

sum - you can use VBA to automate the Excel copy and paste-transpose just like anything else.

[sum]

i was thinking that linking to excel would be better because the data in the spreadsheet is always changing. what do you think?

[Dal Jeanis]

In general, I wouldn't want to have my Access application doing anything to get input from Excel from a manually messed-with spreadsheet, unless I had just then TOLD Access to do it. Your mileage may vary. Either way, when you connect, you have to filter and massage the data appropriately. I would tend to copy the source workbook to a blank workbook and do my massaging there, then import the massaged data. Once again, do the job however you will understand it after a long weekend.