Macro Needed - Please Help!

[SmokingMirror]

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

 

[Norman Jones]

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").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 before:

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" <[email protected]>
wrote in message

 

[SmokingMirror]

Dude!

Thank you very, very, very, very much!