Macro: Copy data from files in a folder.

A

athropos20

New Member
Joined
Jun 10, 2011
Messages
2
Hi guys!!! Need help in optimizing a code I put together to do the following:

1. Cycle through all the *.dat files in the folder where the main file is located.
2. The *.dat file is opened.
3. Occupied cells in Column A are copied.
4. Data is pasted in the main file.
5. *.dat file is closed.
6. rinse and repeat.

Here is the code:

Code: 
Sub Data_Grabber()
Dim wb As Workbook
Dim sht As Worksheet
Dim r As Long
Set Main_Sheet = ActiveSheet 'sheet for results
r = 0 '1st row
myDir = ThisWorkbook.Path & "\"
myfile = Dir(myDir & "*.dat")
Do While Len(myfile) <> ""
    Set wb = Workbooks.Open(myDir & myfile)
    On Error GoTo Error_Handler
    With wb.Sheets(1)
         Main_Sheet.Cells(1 + r, 1) = .Range("a1")
         Main_Sheet.Cells(2 + r, 1) = .Range("a2")
         Main_Sheet.Cells(3 + r, 1) = .Range("a3")
         Main_Sheet.Cells(4 + r, 1) = .Range("a4")
         Main_Sheet.Cells(5 + r, 1) = .Range("a5")
         Main_Sheet.Cells(6 + r, 1) = .Range("a6")
         Main_Sheet.Cells(7 + r, 1) = .Range("a7")
         Main_Sheet.Cells(8 + r, 1) = .Range("a8")
         Main_Sheet.Cells(9 + r, 1) = .Range("a9")
         Main_Sheet.Cells(10 + r, 1) = .Range("a10")
         Main_Sheet.Cells(11 + r, 1) = .Range("a11")
         Main_Sheet.Cells(12 + r, 1) = .Range("a12")
         Main_Sheet.Cells(13 + r, 1) = .Range("a13")
         Main_Sheet.Cells(14 + r, 1) = .Range("a14")
         Main_Sheet.Cells(15 + r, 1) = .Range("a15")
         Main_Sheet.Cells(16 + r, 1) = .Range("a16")
         Main_Sheet.Cells(17 + r, 1) = .Range("a17")
         Main_Sheet.Cells(18 + r, 1) = .Range("a18")
         Main_Sheet.Cells(19 + r, 1) = .Range("a19")
         Main_Sheet.Cells(20 + r, 1) = .Range("a20")
         Main_Sheet.Cells(21 + r, 1) = .Range("a21")
         Main_Sheet.Cells(22 + r, 1) = .Range("a22")
         Main_Sheet.Cells(23 + r, 1) = .Range("a23")
         Main_Sheet.Cells(24 + r, 1) = .Range("a24")
         Main_Sheet.Cells(25 + r, 1) = .Range("a25")
         Main_Sheet.Cells(26 + r, 1) = .Range("a26")
         Main_Sheet.Cells(27 + r, 1) = .Range("a27")
         Main_Sheet.Cells(28 + r, 1) = .Range("a28")
         Main_Sheet.Cells(29 + r, 1) = .Range("a29")
         Main_Sheet.Cells(30 + r, 1) = .Range("a30")
         Main_Sheet.Cells(31 + r, 1) = .Range("a31")
         Main_Sheet.Cells(32 + r, 1) = .Range("a32")
         Main_Sheet.Cells(33 + r, 1) = .Range("a33")
         Main_Sheet.Cells(34 + r, 1) = .Range("a34")
         Main_Sheet.Cells(35 + r, 1) = .Range("a35")
         Main_Sheet.Cells(36 + r, 1) = .Range("a36")
         Main_Sheet.Cells(37 + r, 1) = .Range("a37")
         Main_Sheet.Cells(38 + r, 1) = .Range("a38")
         Main_Sheet.Cells(39 + r, 1) = .Range("a39")
         Main_Sheet.Cells(40 + r, 1) = .Range("a40")
         Main_Sheet.Cells(41 + r, 1) = .Range("a41")
         Main_Sheet.Cells(42 + r, 1) = .Range("a42")
         Main_Sheet.Cells(43 + r, 1) = .Range("a43")
         Main_Sheet.Cells(44 + r, 1) = .Range("a44")
         Main_Sheet.Cells(45 + r, 1) = .Range("a45")
         Main_Sheet.Cells(46 + r, 1) = .Range("a46")
         Main_Sheet.Cells(47 + r, 1) = .Range("a47")
         Main_Sheet.Cells(48 + r, 1) = .Range("a48")
         Main_Sheet.Cells(49 + r, 1) = .Range("a49")
         Main_Sheet.Cells(50 + r, 1) = .Range("a50")
         Main_Sheet.Cells(51 + r, 1) = .Range("a51")
         Main_Sheet.Cells(52 + r, 1) = .Range("a52")
         Main_Sheet.Cells(53 + r, 1) = .Range("a53")
         Main_Sheet.Cells(54 + r, 1) = .Range("a54")
         Main_Sheet.Cells(55 + r, 1) = .Range("a55")
         Main_Sheet.Cells(56 + r, 1) = .Range("a56")
         Main_Sheet.Cells(57 + r, 1) = .Range("a57")
         Main_Sheet.Cells(58 + r, 1) = .Range("a58")
         Main_Sheet.Cells(59 + r, 1) = .Range("a59")
         Main_Sheet.Cells(60 + r, 1) = .Range("a60")
         Main_Sheet.Cells(61 + r, 1) = .Range("a61")
         Main_Sheet.Cells(62 + r, 1) = .Range("a62")
         Main_Sheet.Cells(63 + r, 1) = .Range("a63")
         Main_Sheet.Cells(64 + r, 1) = .Range("a64")
         Main_Sheet.Cells(65 + r, 1) = .Range("a65")
         Main_Sheet.Cells(66 + r, 1) = .Range("a66")
         Main_Sheet.Cells(67 + r, 1) = .Range("a67")
         Main_Sheet.Cells(68 + r, 1) = .Range("a68")
         Main_Sheet.Cells(69 + r, 1) = .Range("a69")
         Main_Sheet.Cells(70 + r, 1) = .Range("a70")
         Main_Sheet.Cells(71 + r, 1) = .Range("a71")
         Main_Sheet.Cells(72 + r, 1) = .Range("a72")
         Main_Sheet.Cells(73 + r, 1) = .Range("a73")
         Main_Sheet.Cells(74 + r, 1) = .Range("a74")
         Main_Sheet.Cells(75 + r, 1) = .Range("a75")
         Main_Sheet.Cells(76 + r, 1) = .Range("a76")
         Main_Sheet.Cells(77 + r, 1) = .Range("a77")
         Main_Sheet.Cells(78 + r, 1) = .Range("a78")
         Main_Sheet.Cells(79 + r, 1) = .Range("a79")
         Main_Sheet.Cells(80 + r, 1) = .Range("a80")
         Main_Sheet.Cells(81 + r, 1) = .Range("a81")
         Main_Sheet.Cells(82 + r, 1) = .Range("a82")
         Main_Sheet.Cells(83 + r, 1) = .Range("a83")
         Main_Sheet.Cells(84 + r, 1) = .Range("a84")
         Main_Sheet.Cells(85 + r, 1) = .Range("a85")
         Main_Sheet.Cells(86 + r, 1) = .Range("a86")
         Main_Sheet.Cells(87 + r, 1) = .Range("a87")
         Main_Sheet.Cells(88 + r, 1) = .Range("a88")
         Main_Sheet.Cells(89 + r, 1) = .Range("a89")
         Main_Sheet.Cells(90 + r, 1) = .Range("a90")
         Main_Sheet.Cells(91 + r, 1) = .Range("a91")
         Main_Sheet.Cells(92 + r, 1) = .Range("a92")
         Main_Sheet.Cells(93 + r, 1) = .Range("a93")
         Main_Sheet.Cells(94 + r, 1) = .Range("a94")
         Main_Sheet.Cells(95 + r, 1) = .Range("a95")
         Main_Sheet.Cells(96 + r, 1) = .Range("a96")
         Main_Sheet.Cells(97 + r, 1) = .Range("a97")
         Main_Sheet.Cells(98 + r, 1) = .Range("a98")
         Main_Sheet.Cells(99 + r, 1) = .Range("a99")
         Main_Sheet.Cells(100 + r, 1) = .Range("a100")
         Main_Sheet.Cells(101 + r, 1) = .Range("a101")
         Main_Sheet.Cells(102 + r, 1) = .Range("a102")
         Main_Sheet.Cells(103 + r, 1) = .Range("a103")
         Main_Sheet.Cells(104 + r, 1) = .Range("a104")
         Main_Sheet.Cells(105 + r, 1) = .Range("a105")
         Main_Sheet.Cells(106 + r, 1) = .Range("a106")
         Main_Sheet.Cells(107 + r, 1) = .Range("a107")
         Main_Sheet.Cells(108 + r, 1) = .Range("a108")
         Main_Sheet.Cells(109 + r, 1) = .Range("a109")
         Main_Sheet.Cells(110 + r, 1) = .Range("a110")
         Main_Sheet.Cells(111 + r, 1) = .Range("a111")
         Main_Sheet.Cells(112 + r, 1) = .Range("a112")
         Main_Sheet.Cells(113 + r, 1) = .Range("a113")
         Main_Sheet.Cells(114 + r, 1) = .Range("a114")
         Main_Sheet.Cells(115 + r, 1) = .Range("a115")
         Main_Sheet.Cells(116 + r, 1) = .Range("a116")
         Main_Sheet.Cells(117 + r, 1) = .Range("a117")
         Main_Sheet.Cells(118 + r, 1) = .Range("a118")
         Main_Sheet.Cells(119 + r, 1) = .Range("a119")
         Main_Sheet.Cells(120 + r, 1) = .Range("a120")
         Main_Sheet.Cells(121 + r, 1) = .Range("a121")
         Main_Sheet.Cells(122 + r, 1) = .Range("a122")
         Main_Sheet.Cells(123 + r, 1) = .Range("a123")
         Main_Sheet.Cells(124 + r, 1) = .Range("a124")
         Main_Sheet.Cells(125 + r, 1) = .Range("a125")
         Main_Sheet.Cells(126 + r, 1) = .Range("a126")
         Main_Sheet.Cells(127 + r, 1) = .Range("a127")
         Main_Sheet.Cells(128 + r, 1) = .Range("a128")
         Main_Sheet.Cells(129 + r, 1) = .Range("a129")
         Main_Sheet.Cells(130 + r, 1) = .Range("a130")
         Main_Sheet.Cells(131 + r, 1) = .Range("a131")
         Main_Sheet.Cells(132 + r, 1) = .Range("a132")
         Main_Sheet.Cells(133 + r, 1) = .Range("a133")
         Main_Sheet.Cells(134 + r, 1) = .Range("a134")
         Main_Sheet.Cells(135 + r, 1) = .Range("a135")
         Main_Sheet.Cells(136 + r, 1) = .Range("a136")
         Main_Sheet.Cells(137 + r, 1) = .Range("a137")
         Main_Sheet.Cells(138 + r, 1) = .Range("a138")
         Main_Sheet.Cells(139 + r, 1) = .Range("a139")
         Main_Sheet.Cells(140 + r, 1) = .Range("a140")
         Main_Sheet.Cells(141 + r, 1) = .Range("a141")
         Main_Sheet.Cells(142 + r, 1) = .Range("a142")
         Main_Sheet.Cells(143 + r, 1) = .Range("a143")
         Main_Sheet.Cells(144 + r, 1) = .Range("a144")
         Main_Sheet.Cells(145 + r, 1) = .Range("a145")
         Main_Sheet.Cells(146 + r, 1) = .Range("a146")
         Main_Sheet.Cells(147 + r, 1) = .Range("a147")
         Main_Sheet.Cells(148 + r, 1) = .Range("a148")
         Main_Sheet.Cells(149 + r, 1) = .Range("a149")
         Main_Sheet.Cells(150 + r, 1) = .Range("a150")
         Main_Sheet.Cells(151 + r, 1) = .Range("a151")
         Main_Sheet.Cells(152 + r, 1) = .Range("a152")
         Main_Sheet.Cells(153 + r, 1) = .Range("a153")
         Main_Sheet.Cells(154 + r, 1) = .Range("a154")
         Main_Sheet.Cells(155 + r, 1) = .Range("a155")
         Main_Sheet.Cells(156 + r, 1) = .Range("a156")
         Main_Sheet.Cells(157 + r, 1) = .Range("a157")
         Main_Sheet.Cells(158 + r, 1) = .Range("a158")
         Main_Sheet.Cells(159 + r, 1) = .Range("a159")
         Main_Sheet.Cells(160 + r, 1) = .Range("a160")
         Main_Sheet.Cells(161 + r, 1) = .Range("a161")
         Main_Sheet.Cells(162 + r, 1) = .Range("a162")
         Main_Sheet.Cells(163 + r, 1) = .Range("a163")
         Main_Sheet.Cells(164 + r, 1) = .Range("a164")
         Main_Sheet.Cells(165 + r, 1) = .Range("a165")
         Main_Sheet.Cells(166 + r, 1) = .Range("a166")
         Main_Sheet.Cells(167 + r, 1) = .Range("a167")
         Main_Sheet.Cells(168 + r, 1) = .Range("a168")
         Main_Sheet.Cells(169 + r, 1) = .Range("a169")
         Main_Sheet.Cells(170 + r, 1) = .Range("a170")
         Main_Sheet.Cells(171 + r, 1) = .Range("a171")
         Main_Sheet.Cells(172 + r, 1) = .Range("a172")
         Main_Sheet.Cells(173 + r, 1) = .Range("a173")
         Main_Sheet.Cells(174 + r, 1) = .Range("a174")
         Main_Sheet.Cells(175 + r, 1) = .Range("a175")
         Main_Sheet.Cells(176 + r, 1) = .Range("a176")
         Main_Sheet.Cells(177 + r, 1) = .Range("a177")
         Main_Sheet.Cells(178 + r, 1) = .Range("a178")
         Main_Sheet.Cells(179 + r, 1) = .Range("a179")
         Main_Sheet.Cells(180 + r, 1) = .Range("a180")
         Main_Sheet.Cells(181 + r, 1) = .Range("a181")
         Main_Sheet.Cells(182 + r, 1) = .Range("a182")
         Main_Sheet.Cells(183 + r, 1) = .Range("a183")
         Main_Sheet.Cells(184 + r, 1) = .Range("a184")
         Main_Sheet.Cells(185 + r, 1) = .Range("a185")
         Main_Sheet.Cells(186 + r, 1) = .Range("a186")
         Main_Sheet.Cells(187 + r, 1) = .Range("a187")
         Main_Sheet.Cells(188 + r, 1) = .Range("a188")
         Main_Sheet.Cells(189 + r, 1) = .Range("a189")
         Main_Sheet.Cells(190 + r, 1) = .Range("a190")
         Main_Sheet.Cells(191 + r, 1) = .Range("a191")
         Main_Sheet.Cells(192 + r, 1) = .Range("a192")
         Main_Sheet.Cells(193 + r, 1) = .Range("a193")
         Main_Sheet.Cells(194 + r, 1) = .Range("a194")
         Main_Sheet.Cells(195 + r, 1) = .Range("a195")
         Main_Sheet.Cells(196 + r, 1) = .Range("a196")
         Main_Sheet.Cells(197 + r, 1) = .Range("a197")
         Main_Sheet.Cells(198 + r, 1) = .Range("a198")
         Main_Sheet.Cells(199 + r, 1) = .Range("a199")
         Main_Sheet.Cells(200 + r, 1) = .Range("a200")
         
    End With
    wb.Close
    myfile = Dir
    r = r + 200
Loop
Error_Handler:
Exit Sub
End Sub

I find this code a bit clunky but it does the job. I want to find a way to make it more flexible and efficient. Here are the limitations I found:

1. The code is limited to 200 rows. - I think this should be adaptable to however many occupied cells the source file has.
2. It also inserts way too many blank rows in between data sets. - an offshoot problem of item#1.
3.It errors out when it runs out of *.dat files to read. - I feel that my error handler for this is just a cheat.

Hopefully someone out there will be able to help me out. Any tips is much appreciated.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Well, I was able to improve the code 1 day after I posted query. Here a snip of the vital code.

Code: 
Set Main_Sheet = ActiveSheet 
myDir = ThisWorkbook.Path & "\"
myfile = Dir(myDir & "*.dat")
myFN = ThisWorkbook.Name
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Do While Len(myfile) <> ""
    Set wb = Workbooks.Open(myDir & myfile)
    On Error GoTo Error_Handler
    With wb.Sheets(1)
        Range("A1").Select
        Range(Selection, Selection.End(xlDown)).Copy
        Windows(myFN).Activate
        ActiveSheet.Paste
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
    End With
    wb.Close
    myfile = Dir
Loop

It took me a lot of tinkering, but I managed somehow. Just goes to show how noob I am. LOL!

Hopefully this helps someone someday. Cheers! :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Excel VBA file importing data from several files located in same folder -> challenge to copy data only (rest is working perfectly)
Replies
3
Views
434
NdNoviceHlp
NdNoviceHlp
jerry12302
  • Question Question
Prevent Windows popup options when copying or extracting files that already exist in the folder. (Force Copy and Replace, or other options)
Replies
5
Views
1K
rollis13
rollis13
H
  • Question Question
Macro to download zip, unpack and copy data
Replies
0
Views
741
HeinrichPaul
H
A
  • Solved
Help with macros to open many files, and extract data. Need help with match / find.
Replies
9
Views
1K
johnnyL
J
W
  • Solved
Copy files from defined folders to a separate folder
Replies
9
Views
714
wayne9606
W

Forum statistics

Threads
1,224,522
Messages
6,179,292
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top