ReneY
07-27-2010, 07:56 AM
Hi everyone,
I'm fairly new to VBA and I'm trying to write a code, but I'm totally stuck.
I need to write a code that goes to a specific folder, opens each of the excel files that are in that folder (name and number of the files in that folder change monthly) copy specific cells from each file (file format is exactly the same, it's a template, cells to be copied are always: P5, I2, O3, E2 and Q41) and place them all in a file (Called "Monthly report"), all arranged in the same row, together with the name of the file they were taken from. Then go again to the folder with the files, open the next file and copy the same cells and arrange them next row down in the monthly report file and so on....
I know I have to rewrite it, but I ran out of ideas....I have something like this
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\ryanezga\Desktop\Files Rene\Macros\Files FG\ordered via"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ryanezga\Desktop\Files Rene\Macros\Files FG\ordered via\Monthly Report.xls"
I simply have no idea how to ask the program to select/copy specific cells from each file AND paste them in one single row without making the longest code ever and taking the program hours to do it (there's approximately 400-500 files in that folder monthly) so i could technically make a program that goes back and forth from the original file to the monthly report copy/pasting each bit of info and then try to make a loop, but I'm guessing it would run out of memory...
Any suggestions? I don't know what you think, but it's quite complicated... I must admit its way out of my knowledge to do all this in a simple manner... but the boss is the boss, its either that or copy/pasting them myself!
I appreciate your help!
ReneY
I'm fairly new to VBA and I'm trying to write a code, but I'm totally stuck.
I need to write a code that goes to a specific folder, opens each of the excel files that are in that folder (name and number of the files in that folder change monthly) copy specific cells from each file (file format is exactly the same, it's a template, cells to be copied are always: P5, I2, O3, E2 and Q41) and place them all in a file (Called "Monthly report"), all arranged in the same row, together with the name of the file they were taken from. Then go again to the folder with the files, open the next file and copy the same cells and arrange them next row down in the monthly report file and so on....
I know I have to rewrite it, but I ran out of ideas....I have something like this
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook
With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\ryanezga\Desktop\Files Rene\Macros\Files FG\ordered via"
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop through all
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
wbResults.Close SaveChanges:=False
Next lCount
End If
End With
On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\ryanezga\Desktop\Files Rene\Macros\Files FG\ordered via\Monthly Report.xls"
I simply have no idea how to ask the program to select/copy specific cells from each file AND paste them in one single row without making the longest code ever and taking the program hours to do it (there's approximately 400-500 files in that folder monthly) so i could technically make a program that goes back and forth from the original file to the monthly report copy/pasting each bit of info and then try to make a loop, but I'm guessing it would run out of memory...
Any suggestions? I don't know what you think, but it's quite complicated... I must admit its way out of my knowledge to do all this in a simple manner... but the boss is the boss, its either that or copy/pasting them myself!
I appreciate your help!
ReneY