Transpose Excel data in Columns to Rows on Import?

[CutAndPaste]

I've got to import a great many Worksheets into a database. Each has 2 worksheets

Worksheet1 has the company information but this is laid out in 2 Columns:

Column1 Column2
CompanyName: Smiggins Mouldings
Address1: Brick Lane

Worksheet2 has the Product Information data laid out the "proper" way with a row of column headings for field names and then a row of data.

The data in the workbooks are defined as ranges "CompanyInfo" and "ProductInfo". When I use the transferspreadsheet method, Access imports the ProductInfo range fine but wants to treat the CompanyInfo range as several records each with one value whereas in the real world it is only one record with lots of fields.

As I see it, one solution would be:

1) Create some vba to open each Workbook, Insert a New Worksheet, Copy Cell A1 to A1, A2 to B1, A3 to C1 etc. to get the data in a row & then close, save, & move onto the next workbook.

Then I can use another routine to import the transposed company info range on the new worksheet into the mdb.

I can do the range import fine, but the Open, create new worksheet, copy / transpose data & save are beyond me.

How can I get around this?

tx

 

[chenn]

CutAndPaste,

You could do this programmatically to import all the worksheets from all the workbooks by running a loop. You could also run a loop to correct the layout on the first sheet. If you don't know how to write the code for the second loop, you could get a good start by recording a macro in excel. Then edit the macro to inlude the loop. I included some code below,

Hope this helps.....

Sub Macro1()

Dim iRow As Integer, iNewRow As Integer
Const COL = "C" 'Column where data is
Const COMPCOL = "F" 'Column to place company data
Const ADDRCOL = "G" 'Column to place address data

iRow = 2 'row where data starts
iNewRow = 1 'start putting data into first row

'last row of data, or default end place 40000 if necessary
Do Until iRow = 2500

'copy company
Range(COL & iRow).Select

'make sure there is data before copying and pasting
If Selection <> "" Then

Selection.Copy
'paste company
Range(COMPCOL & iNewRow).Select
ActiveSheet.Paste

'copy address
iRow = iRow + 1 'move down one row to address data
Range(COL & iRow).Select
Selection.Copy
'paste address
Range(ADDRCOL & iNewRow).Select
ActiveSheet.Paste

'goto next new line
iNewRow = iNewRow + 1

End If

'goto next data line
iRow = iRow + 1

Loop

End Sub

 

[CutAndPaste]

Further Clarification please

Chenn,

I'm assuming that this Routine will run from inside Access and not from inside Excel?

I know the range of data, say it's Sheet1!a1:a20

So, assuming I can get to each workbook in a folder in a loop where does your code come in?

Dim iRow As Integer, iNewRow As Integer
Const COL = "C" 'Column where data is
Const COMPCOL = "F" 'Column to place company data
Const ADDRCOL = "G" 'Column to place address data

'CUTANDPASTE - I'm assuming that I'd need to set up constants for each field e.g.

Const ADDRCOL2 = "H" 'Column to place address 2 data
Const ADDRCOL3 = "I" 'Column to place address 3 data
Const ADDRCOL4 = "J" 'Column to place address 4 data
Const Phone = "K" 'Column to place Phone data
Const Email= "L" 'Column to place Email data


iRow = 2 'row where data starts

'CUTANDPASTE - does this have to be on the same worksheet?
iNewRow = 1 'start putting data into first row

'last row of data, or default end place 40000 if necessary
Do Until iRow = 2500
'CUTANDPASTE - I know the data is only in rows down to 20 so assume that this will replace 250 above

'copy company
Range(COL & iRow).Select

'make sure there is data before copying and pasting
If Selection <> "" Then

Selection.Copy
'paste company
Range(COMPCOL & iNewRow).Select
ActiveSheet.Paste
'CUTANDPASTE - Does this paste inot a new worksheet or the exisiting?

'copy address
iRow = iRow + 1 'move down one row to address data
Range(COL & iRow).Select
Selection.Copy
'paste address
Range(ADDRCOL & iNewRow).Select
ActiveSheet.Paste

'goto next new line
iNewRow = iNewRow + 1

End If

'goto next data line
iRow = iRow + 1

Loop

End Sub


====

Many thanks!

 

[chenn]

The code I included will run from Excel. If you are going to run a loop to import the spreadsheets, you will have to convert the code so that it runs in Access. Basically, in Access - create an object, set the object = "Excel.Application", and all the code I sent you will have to be with the object.

Your constants aren't really necessary, if the column don't change, you could hardcode the letters - up to you.

This code would be run prior to importing each sheet - place it in your loop.

This will put the data into the same sheet, if you want it to go somewhere else, just tell it.

I good starting point is to go to excel - view - toolbars - visual basic toolbar. From there, record a macro that does what you want. Then edit the macro to see what the computer creates, this will give you a good start on what the code looks like and how it is assembled.