Hi all,
My appologies I am a very basic excel user and am in need of some help with VBA code.
I have 56 files in a folder that I need to copy a single cell from Sheet 1 in each file and output it into a new woorkbook, hopefully with the corresponding file name.
I found this code and have been trying to modify it. It seems to run fine (I can see each file opening and closing) but nothing is written into any sheets.
The code was written for someone who needs data from multiple sheets, but I assumed it would work for Workbooks that contain only a single sheet. PLEASE HELP! I am so lost.
Sub FolderPicker_ExportData()
'Nov 19, 2014
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook
Dim sPath As String: Dim sFile As String
Dim L As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select one folder"
.AllowMultiSelect = False
If .Show = True Then
sPath = .SelectedItems(1) & "\"
sFile = Dir(sPath & "*.xls*")
If sFile <> "" Then
Application.ScreenUpdating = False
L = 1
Set ws = wb1.Sheets.Add(before:=wb1.Sheets(1))
Do Until sFile = ""
Set wb2 = Workbooks.Open(sPath & sFile)
For x = 1 To wb2.Sheets.Count
ws.Cells(L, "A").Value = wb2.Sheets(x).Range("T1592").Value
L = L + 1
Next
wb2.Close False
sFile = Dir()
Loop
ws.Range("E1") = sPath '<< folder's path in cell E1
Application.ScreenUpdating = True
Else
MsgBox "no files found"
End If
Else
MsgBox "Cancel"
End If
End With
ActiveWorkbook.Save
End Sub
My appologies I am a very basic excel user and am in need of some help with VBA code.
I have 56 files in a folder that I need to copy a single cell from Sheet 1 in each file and output it into a new woorkbook, hopefully with the corresponding file name.
I found this code and have been trying to modify it. It seems to run fine (I can see each file opening and closing) but nothing is written into any sheets.
The code was written for someone who needs data from multiple sheets, but I assumed it would work for Workbooks that contain only a single sheet. PLEASE HELP! I am so lost.
Sub FolderPicker_ExportData()
'Nov 19, 2014
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ThisWorkbook
Dim sPath As String: Dim sFile As String
Dim L As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select one folder"
.AllowMultiSelect = False
If .Show = True Then
sPath = .SelectedItems(1) & "\"
sFile = Dir(sPath & "*.xls*")
If sFile <> "" Then
Application.ScreenUpdating = False
L = 1
Set ws = wb1.Sheets.Add(before:=wb1.Sheets(1))
Do Until sFile = ""
Set wb2 = Workbooks.Open(sPath & sFile)
For x = 1 To wb2.Sheets.Count
ws.Cells(L, "A").Value = wb2.Sheets(x).Range("T1592").Value
L = L + 1
Next
wb2.Close False
sFile = Dir()
Loop
ws.Range("E1") = sPath '<< folder's path in cell E1
Application.ScreenUpdating = True
Else
MsgBox "no files found"
End If
Else
MsgBox "Cancel"
End If
End With
ActiveWorkbook.Save
End Sub