S
Sandy Hayman
Hi
This is more of a functionality question. I am developing a process in
Access that looks at an Excel spreadsheet and based on keywords in the
spreadsheet, exports relevant data into the spreadsheet. I am working my way
through the code to do this but the problem I am having is that there are
heaps of different formats for the spreadsheets and I'm not sure if it is
possible to fully automate the process. Eg
Spreadsheet 1
Keyword: Row
Identifier Location1 Location2 Location3....
1 542 365 287
2 198 421 219
3 265 408 110
Spreadsheet 2
Keyword: Col
Identifier 1 2 3....
Category1 542 365 287
Category2 198 421 219
Category3 265 408 110
Spreadsheet3
Keyword: Row
Identifier 1996/1997 1997/1998 1999/2000....
1 542 365 287
2 198 421 219
3 265 408 110
The common element is that all the spreadsheets have an identifier that
matches an id in the database. The locations/categories/date will also
match entries in the database. The data that will be updated will be the
numeric data but I will also have to update the dates to the next set of
dates. Eg 1996, 1997, 1998 will become 1997, 1998, 1999 (1996 will drop off
and 1999 will be added).
Has anyone had experience with this level of automation? Is what I am trying
to do possible in a fully automated system or will I need my users to define
ranges, set columns etc? Any ideas of how I might achieve this functionality
for multiple formats would be appreciated.
Thanks
Sandy Hayman
AA Absolute Access - ACT, Australia
This is more of a functionality question. I am developing a process in
Access that looks at an Excel spreadsheet and based on keywords in the
spreadsheet, exports relevant data into the spreadsheet. I am working my way
through the code to do this but the problem I am having is that there are
heaps of different formats for the spreadsheets and I'm not sure if it is
possible to fully automate the process. Eg
Spreadsheet 1
Keyword: Row
Identifier Location1 Location2 Location3....
1 542 365 287
2 198 421 219
3 265 408 110
Spreadsheet 2
Keyword: Col
Identifier 1 2 3....
Category1 542 365 287
Category2 198 421 219
Category3 265 408 110
Spreadsheet3
Keyword: Row
Identifier 1996/1997 1997/1998 1999/2000....
1 542 365 287
2 198 421 219
3 265 408 110
The common element is that all the spreadsheets have an identifier that
matches an id in the database. The locations/categories/date will also
match entries in the database. The data that will be updated will be the
numeric data but I will also have to update the dates to the next set of
dates. Eg 1996, 1997, 1998 will become 1997, 1998, 1999 (1996 will drop off
and 1999 will be added).
Has anyone had experience with this level of automation? Is what I am trying
to do possible in a fully automated system or will I need my users to define
ranges, set columns etc? Any ideas of how I might achieve this functionality
for multiple formats would be appreciated.
Thanks
Sandy Hayman
AA Absolute Access - ACT, Australia