Excel 2010 VBA Copy Data from Multiple Excel Files

0
votes

I have search and searched for this and have come close but not perfect. Here it goes...

What I need is a code to work through other excel work books in succession and copy data. The files will be named 1,2,3,4 etc all the up to about 60 max. I have some code that will open all the files for my which works great. Next I need it to go into all of the work books and copy a set up data and return it to the main workbook which I have been able to do as well. Here are my problems...

  1. I know there must be a way to write the code one time and have it work through the remaining files. IE. if the first file is named 1.csv can the code say to do it on 1.csv, then on 1.csv + 1 meaning 2.csv and then loop out? It seems like it should be able to do it. I don't want to have to manually work my way through writing the same code to copy from 1.csv all the way to 60.csv

  2. If there is a way to loop it then how to I end the end if part. IE one week there might be 37 files and the next 40 and so on. I want it to start at file 1.csv and work until it gets to the last active file either 37.csv or 60.csv and then end if there is no 38.csv file.

Here is the basic recorded code I have.

   Public Sub testr()
  Dim filenum As Integer
  Dim lastrow As Long

  filenum = 1
  On Error GoTo my_handler

  Do
    Windows(filenum & ".CSV").Activate
    Range("A25").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("test.xlsm").Activate
    Range("A1").Select
    lastrow = Worksheets("Data").Cells(1, 1).End(xlDown).Row
    ActiveSheet.Paste
    filenum = filenum + 1
  Loop

my_handler:
  MsgBox "All done."
  Exit Sub
End Sub

Any help that anyone could give me would be amazing! Thanks in advance~

2
excelexcel-2010vba

2 Answers

0
votes

Okay, for the code you have now, you need to at least edit to look like this:

Public Sub testr()
  Dim filenum As Integer
  filenum = 1
  On Error GoTo my_handler

  Do
    Windows(filenum & ".CSV").Activate
    Range("A25").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("test.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    filenum = filenum + 1
  Loop

my_handler:
  MsgBox "All done."
  Exit Sub
End Sub
0
votes

You could try wrapping all this in a DO...LOOP. If you don't know how many .csv files you'll be working with, just put in an error handler to quit once no more files are found.

You can use string concatenation to make the filenames change: for example, using a variable called filenum, if filenum = 1 then filenum & ".csv" would be "1.csv," if filenum = 2 then filenum & ".csv" would be "2.csv," etc.

For example:

Public Sub testr()
Dim filenum As Integer
filenum = 1
On Error GoTo my_handler
Do
  Windows(filenum & ".CSV").Activate
  'rest of code goes here
filenum = filenum + 1
Loop

my_handler:
  MsgBox "All done."
Exit Sub