Sep 04 2019 02:58 PM
Sep 04 2019 06:21 PM
SolutionYou may tweak your code like this...
Sub GRABTOTAL()
Dim xFd As FileDialog
Dim xFdItem As Variant
Dim xFileName As String
Dim startRow As Long
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim wsDest As Worksheet
Application.ScreenUpdating = False
Set wbDest = ThisWorkbook 'The Workbook which contains this Macro
Set wsDest = wbDest.Worksheets.Add(before:=wbDest.Worksheets(1)) 'Sheet will be added to write the values from all the csv files
wsDest.Range("A1").Value = "Totals"
startRow = 2 'Write the values in column A starting from Row2
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
xFileName = Dir(xFdItem & "*.csv*")
Do While xFileName <> ""
Set wbSource = Workbooks.Open(xFdItem & xFileName)
'Fetchnig the value from cell AF2 in column A of the newly added Sheet in this Workbook
wsDest.Range("A" & startRow).Value = wbSource.Worksheets(1).Range("AF2").Value
wbSource.Close False 'Closing the csv file without saving
startRow = startRow + 1 'Incrementing the row by 1
xFileName = Dir
Loop
End If
Application.ScreenUpdating = True
End Sub
Sep 04 2019 06:21 PM
SolutionYou may tweak your code like this...
Sub GRABTOTAL()
Dim xFd As FileDialog
Dim xFdItem As Variant
Dim xFileName As String
Dim startRow As Long
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim wsDest As Worksheet
Application.ScreenUpdating = False
Set wbDest = ThisWorkbook 'The Workbook which contains this Macro
Set wsDest = wbDest.Worksheets.Add(before:=wbDest.Worksheets(1)) 'Sheet will be added to write the values from all the csv files
wsDest.Range("A1").Value = "Totals"
startRow = 2 'Write the values in column A starting from Row2
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems(1) & Application.PathSeparator
xFileName = Dir(xFdItem & "*.csv*")
Do While xFileName <> ""
Set wbSource = Workbooks.Open(xFdItem & xFileName)
'Fetchnig the value from cell AF2 in column A of the newly added Sheet in this Workbook
wsDest.Range("A" & startRow).Value = wbSource.Worksheets(1).Range("AF2").Value
wbSource.Close False 'Closing the csv file without saving
startRow = startRow + 1 'Incrementing the row by 1
xFileName = Dir
Loop
End If
Application.ScreenUpdating = True
End Sub