Count data in multiple Excel file vba

Question

I have multiple xls files in a folder.

In column G:G of help worksheet, it has datas like O , R

I want to count total number of O, R individually and put it in a excel table.

i have this code please help its not executing the loop also

 Private Sub CommandButton2_Click() Dim CSVfolder As String, _ Xlsfolder As String, _ fname As String, _ wbook As Workbook, _ SRange As Range, _ k As Integer Xlsfolder = "C:\Users\sam\Desktop\macro\macro\macro" fname = Dir(Xlsfolder & "*.xls") k = 5 Do While fnmae <> "" Workbooks.Open (fnamme) Set SRange = Workbooks(fname).Worksheets("Findings").Range("G:G") Cells(3, k) = Application.CountIf(SRange, "O") Cells(4, k) = Application.CountIf(SRange, "Cd") Cells(5, k) = Application.CountIf(SRange, "Cr") Cells(6, k) = Application.CountIf(SRange, "Cn") Cells(7, k) = Application.CountIf(SRange, "A") Cells(8, k) = Application.CountIf(SRange, "Cf") Workbooks(fname).Close Loop End Sub

Hi Sam, welcome to Stack Overflow. Could you please share with us what have you tried till now to resolve your problem? — PSotor
– PSotor, Commented Jan 27, 2017 at 8:38
I'm troll guessing, not much... So here is a basic to open files in a folder : stackoverflow.com/a/30758554/4628637 You'll just have to tune it to fit your need and if you're stuck come back here to ask a question! — R3uK
– R3uK, Commented Jan 27, 2017 at 8:46

ASH · Accepted Answer · 2017-01-28 13:52:55Z

You can do something very simple, like this.

=('NAME_OF__SHEET'!A1) NAME_OF__SHEET = "the name of your sheet" A1 = column, row and your done!

Or, of course, you can use VBA to import your data from several files into one sheet, and work on it there.

Sub combine() Dim app As New Excel.Application app.Visible = False Dim wbM As Workbook Set wbM = Workbooks("main") Dim fd As FileDialog Set fd = Application.FileDialog(msoFileDialogFilePicker) fd.AllowMultiSelect = True Files = fd.Show For i = 1 To fd.SelectedItems.Count app.Workbooks.Open fd.SelectedItems(i) Next i Dim wb As Workbook For Each wb In app.Workbooks If wb.Name <> "main.xlsm" Then Dim wsN As Worksheet Set wsN = wbM.Sheets.Add(after:=wbM.Sheets(wbM.Sheets.Count)) wsN.Name = wb.Name wbM.Sheets(wb.Name).Range("A1:K1").Value = wb.Sheets(1).Range("A1:K1").Value wb.Close SaveChanges:=False End If Next app.Quit Set app = Nothing End Sub

Collectives™ on Stack Overflow

Count data in multiple Excel file vba

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related