Firstly, thanks for any help you can give.

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

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

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

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

--
SmokingMirro
-----------------------------------------------------------------------
SmokingMirror's Profile: http://www.excelforum.com/member.php...fo&userid=1222
View this thread: http://www.excelforum.com/showthread.php?threadid=47369

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

Dude!

Thank you very, very, very, very much!


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