-1

There're a hundreds of excel files I have to collect data from and paste it into a new template according the designated row or column.

I'm thinking of creating an excel workbook that will be able to load all excel files(xls/xlsx extention) in a folder. Copy the content of the file according to the various row & column parameter I'm setting and paste it according to the row & column I set for it. Other than just copy & paste. Assuming it copied 80 rows worth of data, I would like to tag on to column E & F based on the 80 rows of data by referring to a cell number in the loaded file. Once done, it'll clear the rest of the content below the last row filled and save it as the same name but in a separate folder.

If this function is possible, could you guide me through the various commands, operators, code. That're specifically made for such functions. So it'll be easier for me to research on the implementation of the macro.

Thanks for your kind attention to this long wordy post

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Tyler
  • 604
  • 3
  • 10
  • 24
  • Hi Tyler, you could start with using Dir (https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba) or FileSystemObject (https://stackoverflow.com/questions/3233203/how-do-i-use-filesystemobject-in-vba) to iterate over a number of files in a folder. – majjam Oct 12 '17 at 09:08
  • Please have a look at https://stackoverflow.com/help/how-to-ask – iDevlop Oct 12 '17 at 09:22

1 Answers1

0

You don't need Visual Studio. Everything you want to do can be done through the VBA back-side that is always present in any MS Office application (hit alt-F11 to get there).

You don't have to limit yourself to just recording macros. Just type the VBA code there. You can either run it straight from there, give it a hot-key (like a macro), or link it to a button on your worksheet, etc.

FileCopy, Dir, MkDir, etc are all available to you to manipulate/move files. To pull data out of a specific workbook, you simply open it and refer to the cells you want with something like

Dim wb As Excel.Workbook
Set wb = Workbooks.Open("C:\Documents\WorkbookToLookAt.xlsm")
ActiveWorkbook.Sheets("Sheet1").Cells(1,1).Value = wb.Sheets("Sheet1").Cells(1, 1).Value

This would set A1 in your current workbook to whatever A1 was in the other workbook of interest. Expand that idea to extract whatever cells you want from whatever workbooks you want.

xpofer
  • 172
  • 1
  • 8