Open all files question
 

Hi. I found some code on the newsgroup that I have a question about. This
code opens all files within a particular folder. I have a bunch of files I
wish to open, copy the contents of the ONLY sheet in the file, and paste to
the file that the code resides in. The problem is each file in the folder
has a differently names sheet. So I modified the code to grab data from the
ActiveSheet, but that errors out - I think becasue the sheet is not active.
How do I get around this?

Sub OpenAllFiles()
Dim sFolder As String
Dim wb As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "C:\Test" 'Folder Location on server
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

wb.ActiveSheet.Range("A5:AG" & _
.Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("GM_Approval").Range("A" & _
.Range("G65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _
Paste:=xlPasteValues

wb.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

End Sub

Open all files question
 

Only one worksheet???

Then I'd just look at worksheets(1):


With wb.Worksheets(1)
.Range("A5:AG" & .Range("G20").End(xlUp).Row).Copy
End With

'and be careful with the paste, too

With ThisWorkbook.Worksheets("GM_Approval")
.Range("A" & .Range("G65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _
Paste:=xlPasteValues
End With

I didn't test this, but you want to be a little careful.

..Range(...)
refers to the previous With object.

In your code, the previous object was: Application.FileSearch





Steph wrote:

Hi. I found some code on the newsgroup that I have a question about. This
code opens all files within a particular folder. I have a bunch of files I
wish to open, copy the contents of the ONLY sheet in the file, and paste to
the file that the code resides in. The problem is each file in the folder
has a differently names sheet. So I modified the code to grab data from the
ActiveSheet, but that errors out - I think becasue the sheet is not active.
How do I get around this?

Sub OpenAllFiles()
Dim sFolder As String
Dim wb As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "C:\Test" 'Folder Location on server
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

wb.ActiveSheet.Range("A5:AG" & _
.Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("GM_Approval").Range("A" & _
.Range("G65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _
Paste:=xlPasteValues

wb.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With

End Sub

--

Dave Peterson

Open all files question
 

Perfect. Thanks for the info!

"Dave Peterson" wrote in message
...
Only one worksheet???

Then I'd just look at worksheets(1):


With wb.Worksheets(1)
.Range("A5:AG" & .Range("G20").End(xlUp).Row).Copy
End With

'and be careful with the paste, too

With ThisWorkbook.Worksheets("GM_Approval")
.Range("A" & .Range("G65536").End(xlUp).Offset(1,
0).Row).PasteSpecial _
Paste:=xlPasteValues
End With

I didn't test this, but you want to be a little careful.

.Range(...)
refers to the previous With object.

In your code, the previous object was: Application.FileSearch





Steph wrote:

Hi. I found some code on the newsgroup that I have a question about.
This
code opens all files within a particular folder. I have a bunch of files
I
wish to open, copy the contents of the ONLY sheet in the file, and paste
to
the file that the code resides in. The problem is each file in the
folder
has a differently names sheet. So I modified the code to grab data from
the
ActiveSheet, but that errors out - I think becasue the sheet is not
active.
How do I get around this?

Sub OpenAllFiles()
Dim sFolder As String
Dim wb As Workbook
Dim i As Long

With Application.FileSearch
.NewSearch
.LookIn = "C:\Test" 'Folder Location on server
.SearchSubFolders = False
.Filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(Filename:=.FoundFiles(i))

wb.ActiveSheet.Range("A5:AG" & _
.Range("G20").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("GM_Approval").Range("A" & _
.Range("G65536").End(xlUp).Offset(1, 0).Row).PasteSpecial _
Paste:=xlPasteValues

wb.Close SaveChanges:=False
Next i
Else
MsgBox "Folder " & sFolder & " contains no required
files"
End If
End With

End Sub

--

Dave Peterson