Append data to a table from an excel spreadsheet with not normal layout

[carmenlisa]

I need to append data from a spreadsheet (many spreadsheets over time) into an access table. There are several aspects of this I am not sure how to do.

First is that the spreadsheets have many tabs and within each tab are “tables” that contain the data. Thus, I believe that I need to designate named ranges for where to get data. And I believe the idea of linking to Access is out of the question considering the issue of multiple “tables’ within tabs in the spreadsheet. (And I really don’t want to think about linking and then unlinking tables via code. Maybe I just stated two problems, the idea of named ranges and the seeming unavailability of linking the tables to access.

Second, some of the “tables” in Excel have the field names vertically in a row with the data to the right. I have never tried to append data that was not in the traditional layout of field names spread horizontally in a row with the data below it. Is there a way to deal with that in the append query?

 

[SkipVought]

hi,

[You] need to append data from a spreadsheetWORKBOOK (many spreadsheets over time) into an access table

So now within the Workbook are many Worksheets, and within any Worksheet there may be one or more Tables. Yes? Or within any Worksheet there may be one Table? Yes?

Could you please clarify?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[carmenlisa]

The people created a very lengthy and complex workbook to gather data about a ton of characteristics about large buildings. They set it up in almost 20 different tabs (worksheets), each of which covered a certain aspect of the building. Things like insulation, cooling systems, types of motors and compressors used, and on and on. Within many of these tabs, there is more than one "table". One table might have to do with one aspect of the cooling systems, while another table within the tab might have to do with a different aspect of the cooling systems. The point of the workbook is to make is easy and intuitive for their people in the field to gather needed information.

(I beleive that, in addition to the stated problem here, I am going to have more trouble along the way with tables with 2 variables rather than a table with the variables laid out horizontally as field names. But that is a different issue.)

In any case, they now want to archive a lot of this information. They had hoped that there was a way to archive it in some sort of master spreadsheet, but in my opinion that is just out of the question. Access, as far as I can see this, is the only option.

Once we make that decision, we have to get all this semi-disparate information into Access tables. And we have to do this repeatedly over time from many spreadsheets. While I have certainly linked spreadsheets in the past into Access and manipulated the data from there, it was always using a worksheet with one set of data in it and what I think of as a standard table layout. That is, field names across the top and data in the columns below the field names.

One example of a problem table is that across the top are field names for Start Time and End time for compressor run times. Down the right side are variables for the day of the week. In the same tab is a somewhat similar table that deals with the same basic information but about when the building is open/occupied. Another table has to do with months of the year that the business operates.

These are discrete sets of data that need to come into Access from Excel. Plus they are two axis sets of data. If I can break them down into discrete sets of data in the eyes of Access, I think I can handle this. But trying to use an append query to pull out just part of the spreadsheet is not something that i have tried to do before and do not see how to do. And I could be wrong, but I do not think that this worksheet, with the three tables in it, is conducive to linking into Access.

Worse, even if I could do so, I then need to find a way not only to cycle through the Excel files in a folder, not only to cycle through the tabs, I would also need to figure out how to link in the various tables and unlink them after processing the data.

From the user's standpoint, I think I may need to force some restructuring of some of the data within the spreadsheets, but if I try to make every single table become a tab of its own, it may defeat the usability of the spreadsheets to them.