Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
emailing files from excel, the files will not go until I open up . | New Users to Excel | |||
how do I toggle between 2 open excel files and leave both open | Excel Discussion (Misc queries) | |||
How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
Can not open excel files without open application | Excel Discussion (Misc queries) | |||
file open via IE hyperlink causes already open files to shrink and tile | Setting up and Configuration of Excel |