EXCEL 2003 - Transpose to dynamic array for Data Import - Access

[RobNauticus]

Hello all,

Client has a data page from external source in an excel format for weekly data. The weekly data is placed into columns for each week. There are 3 sections that get added to each week ( a new column is added for each section each week, see Gross, Cans, Traffic below).

I am designing a workbook that will translate this into a suitable format for importing into my access database for advanced reporting.

I need to transpose the data into rows with their corresponding labels intact for each row.

My problems are many, since the new columns are added each week, I cannot set a specific range to pull from. Furthermore, I am not sure how to get the data to create a new row for each corresponding entry on the original.


Current View of Data: (There is more data that is hidden!)
< graphic too wide for page >



Desired Output:

I am able to do this manually, but it would be much better to have this automated so the client can do this themselves. If you believe that VBA is necessary, please help me by outlining the basic steps that would be required.
Any help would be GREATLY appreciated!!!

Thanks,
Rob

 

[SkipVought]

Hi,


This can be done by building a new table in an Excel sheet and then importing the sheet into Access. Or you could directly insert into an Access table using ADO Data Objects.

How much VBA experience do you have?


Skip,

_[red][/red]

 

[RobNauticus]

Skip,
I am pretty competent when it comes to vba, mostly access. I am aware on how to get the data into access just fine but I need the steps in excel to translate the data into a usable format like the screenshots show.

Thanks,
Rob

 

[SkipVought]

What things are constant from week to week?

What things change from week to week?

You can absolute reference the constant things and use some other techniques for the variable things, like...

with activesheet.usedrange
  lRowFirst = .row
  lRowLast = .rows.count + .row - 1
  iColFirst = .column
  iColLast = .columns.count + .column - 1
end with

If a heading value changes position, like "cans"...

  iColCans = Activesheet.cells.find("Cans").Column

Skip,

_[red][/red]

 

[RobNauticus]

The constant data is easily imported in a separate transaction. I am not concerned with that. The columns marked with a date under sales, cans and traffic are the main ones I am concerned with now.

There will be a new column added to each of the three sections each week. It is important that all of the historical data is able to be imported at a later time because all of the data is not available right now.

Does that answer the questions?

Rob

 

[SkipVought]

I believe that I anticipated that answer with my examples.


Skip,

_[red][/red]

 

[RobNauticus]

I will take a look at them this evening. Thanks for the help!

 

[SkipVought]

Post back with specific questions.

Skip,

_[red][/red]