Show Posts
351
Finished Programs / Re: DONE: Group of Excel files in folder, send all first rows to a text file (or other)
« on: April 09, 2013, 03:11 AM »
Found basically the same question here:
http://stackoverflow...-a-single-excel-file
With the following vba (?) code solution:
Sub FirstRow()
Application.DisplayAlerts = False
Dim strFilename As String
Dim strPath As String
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbFiles As Workbook
Dim i As Integer
i = 1
Set wbMaster = ThisWorkbook
Set wsMaster = wbMaster.Sheets(2)
strPath = "C:\path\to\your\files\"
strFilename = Dir(strPath & "*.xls")
Do While strFilename <> ""
Set wbFiles = Workbooks.Open(strPath & strFilename, False)
wbFiles.Sheets(1).Rows(RowIndex:=1).Copy
wsMaster.Cells(RowIndex:=i, ColumnIndex:=1).PasteSpecial Paste:=xlPasteAll
wbFiles.Close (False)
strFilename = Dir
i = i + 1
Loop
Application.DisplayAlerts = True
End Sub
"A couple of things to note:
- You'll want to change the Sheet(x) references to the appropriate values for your needs
- Ensure there is a "\" at the end of strPath when you put in your own path
- I've turned DisplayAlerts to false during the execution to avoid a pop-up on every file asking if you want to clear the clipboard or not."
Unfortunately I don't understand how to use his solution. Wish he had have given an example, or explained it more extensively.
http://stackoverflow...-a-single-excel-file
With the following vba (?) code solution:
Sub FirstRow()
Application.DisplayAlerts = False
Dim strFilename As String
Dim strPath As String
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim wbFiles As Workbook
Dim i As Integer
i = 1
Set wbMaster = ThisWorkbook
Set wsMaster = wbMaster.Sheets(2)
strPath = "C:\path\to\your\files\"
strFilename = Dir(strPath & "*.xls")
Do While strFilename <> ""
Set wbFiles = Workbooks.Open(strPath & strFilename, False)
wbFiles.Sheets(1).Rows(RowIndex:=1).Copy
wsMaster.Cells(RowIndex:=i, ColumnIndex:=1).PasteSpecial Paste:=xlPasteAll
wbFiles.Close (False)
strFilename = Dir
i = i + 1
Loop
Application.DisplayAlerts = True
End Sub
"A couple of things to note:
- You'll want to change the Sheet(x) references to the appropriate values for your needs
- Ensure there is a "\" at the end of strPath when you put in your own path
- I've turned DisplayAlerts to false during the execution to avoid a pop-up on every file asking if you want to clear the clipboard or not."
Unfortunately I don't understand how to use his solution. Wish he had have given an example, or explained it more extensively.