ExcelBanter - Macro Needed

Hi SmokingMirror,

Go to Ron de Bruin's site at:

http://www.rondebruin.nl/ado.htm#files

(1) Copy Ron's GetData_Example3() procedure, and all of the subsequent code
on that page, into a module in a new workbook.

(2) Note Ron's instruction:
'============================
You must set a reference to the Microsoft ActiveX Data
Objects 2.5 library in the VBA editor

'============================

To do this, in the VBE:

Menus | Tools | References | Scroll to and select the library reference.

(3) For your purposes, change the

GetData FName(N), "Sheet1", "A1:C5", destrange, False

to:

GetData FName(N), "Sheet1", "H3:K6", destrange, True

(4) Copy the following code into your module:
'==============
Sub ArrangeData()
Dim rng As Range
Dim i As Long
Dim LRow As Long

LRow = LastRow(ActiveSheet)

For i = 1 To LRow Step 4
Range("E" & i).Resize(1, 3).Value = _
Application.Transpose(Range("A" & i).Resize(4))
Range("H" & i).Value = Range("A" & i)(4, 4)
Next i

Columns("A:D").Delete
On Error Resume Next
Columns(1).SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0

End Sub
'<<==============

(5) In the GetData_Example3 procedure, insert the new line:

ArrangeData

immdiately befo

Application.ScreenUpdating = True
End Sub

(6) Now run the GetData_Example3 procedure; use the shift and/or control
keys to select the requisite workbooks from the File dialog which is
presented. This will furnish you with a new worksheet showing the customer
details from each workbook in sequential rows. The new worksheet can be used
as a Word MailMerge source to produce your labels.

---
Regards,
Norman

"SmokingMirror"
wrote in message
news:SmokingMirror.1wh3mc_1128589555.8103@excelfor um-nospam.com...

Firstly, thanks for any help you can give.

Essentially, I have a problem with trying to extract cell information
from well over a thousand different workbooks, and to arrange them into
some kind of arrangement whereby they can be printed directly onto label
paper.

Unfortuantely, whoever designed our workbooks did them in such a way as
to make Mail Merging impossible, and so the only help anyone could offer
was to suggest some sort of Macro that I could point to each workbook
and have it grab the data needed.

Sadly, my Macro knowledge is limited, too limited to produce such a
Macro, so I'm asking for any help you guys can offer.

The original thread, which contains further details, can be found at
http://www.excelforum.com/showthread.php?t=472899

--
SmokingMirror
------------------------------------------------------------------------
SmokingMirror's Profile:
http://www.excelforum.com/member.php...o&userid=12225
View this thread: http://www.excelforum.com/showthread...hreadid=473697