Data collection help..please!

zahbaker

New Member
Joined
Nov 24, 2005
Messages
2
Hi ,I am new here,so here goes.I have about 100 csv files containing data.
eg as follows:
Devicename Username Faxes Copies Pagecount Department

HP4100 KING 20 500 1000 sggt100
HP4100 queen 20 500 1000 sggp400

I need to collate this data in one excel worksheet by the different devicenames broken down by department and pagecounts by user.
Can anyone please assist.
Thanks
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi, Welcome to the Board!

This code should get you started, it prompte you for the csv files & collates them into sheet1:
Code:
Sub CSVMerge()
Dim lRowMFr As Long, lRowMTo As Long
Dim lRow1Fr As Long, lRow1To As Long
Dim vFiles As Variant, vCurFN As Variant
Dim wbCur As Workbook
Dim wsMaster As Worksheet, WS As Worksheet

vFiles = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv", _
                                  Title:="Select CSV files", _
                                  MultiSelect:=True)
If IsArray(vFiles) = False Then
    MsgBox "Macro Abandoned"
    Exit Sub
End If

Set wsMaster = Sheets("Sheet1")

Application.ScreenUpdating = False

For Each vCurFN In vFiles
    Workbooks.Open Filename:=vCurFN, ReadOnly:=True
    Set WS = Sheets(1)
    lRow1Fr = 2
    lRow1To = WS.Cells(Rows.Count, "A").End(xlUp).Row
    If lRow1To > 1 Then
        lRowMFr = wsMaster.Cells(Rows.Count, "A").End(xlUp).Row + 1
        lRowMTo = lRowMFr + lRow1To - lRow1Fr
        wsMaster.Rows(lRowMFr & ":" & lRowMTo).Value = WS.Rows(lRow1Fr & ":" & lRow1To).Value
    End If
    ActiveWorkbook.Close
Next vCurFN
Application.ScreenUpdating = True
End Sub
To install, [Alt-F11] then Insert / Module and paste into code window
 
Upvote 0
csv file

What is a CSV file. Dont understand what that is. All my files are excel files


thanks
 
Upvote 0
Re: csv file

melvinm said:
What is a CSV file. Dont understand what that is. All my files are excel files


thanks

CSV files are 'Comma Separated Variable' textfiles, and have each variable in the line separated by, wait for it, a comma.

Excel is clever enough to recognise these & load them in as Excel sheets - try creating some data in an Excel sheet & then saving it as a CSV file then open it with Notepad.
 
Upvote 0
CSv

is it possible to modify the vb code to read excel files. tried running it, it promts for the csv file merge and by the looks of it its coded to have csv as input.


Thanks
 
Upvote 0
Re: CSv

melvinm said:
is it possible to modify the vb code to read excel files. tried running it, it promts for the csv file merge and by the looks of it its coded to have csv as input.


Thanks

Hi,

Just change this statement:
Code:
vFiles = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv", _ 
                                  Title:="Select CSV files", _ 
                                  MultiSelect:=True)

to this:
Code:
vFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),*.xls", _ 
                                  Title:="Select Excel files", _ 
                                  MultiSelect:=True)
 
Upvote 0
Thanks Alan

Your vb code works just fine,i might be pushing my luck here..but maybe u can assist.I have all these csv files and compiling and sorting this data is really time consuming.Is there a way that i can automate excel to get this data,summing up totals,getting device utilization percentages and totals with one macro?
 
Upvote 0
Hi,

Can you post the format of your csv files & your expected results?

[edit] - erm I've just re-read your original post, watch this space!
 
Upvote 0
Hi,

This version also sorts the data:
Code:
Sub CSVMerge()
Dim lRowMFr As Long, lRowMTo As Long
Dim lRow1Fr As Long, lRow1To As Long
Dim vFiles As Variant, vCurFN As Variant
Dim wbCur As Workbook
Dim wsMaster As Worksheet, WS As Worksheet

'******************
'** Collect data **
'******************
vFiles = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv", _
                                  Title:="Select CSV files", _
                                  MultiSelect:=True)
If IsArray(vFiles) = False Then
    MsgBox "Macro Abandoned"
    Exit Sub
End If

Set wsMaster = Sheets("Sheet1")

Application.ScreenUpdating = False

For Each vCurFN In vFiles
    Workbooks.Open Filename:=vCurFN, ReadOnly:=True
    Set WS = Sheets(1)
    lRow1Fr = 2
    lRow1To = WS.Cells(Rows.Count, "A").End(xlUp).Row
    If lRow1To > 1 Then
        lRowMFr = wsMaster.Cells(Rows.Count, "A").End(xlUp).Row + 1
        lRowMTo = lRowMFr + lRow1To - lRow1Fr
        wsMaster.Rows(lRowMFr & ":" & lRowMTo).Value = WS.Rows(lRow1Fr & ":" & lRow1To).Value
    End If
    ActiveWorkbook.Close
Next vCurFN

wsMaster.Range("A1:" & _
                wsMaster.Cells(1, Columns.Count).End(xlToLeft).Address).Font.Bold = True

'***************
'** Sort Data **
'***************
wsMaster.Columns("A:F").Sort Key1:=wsMaster.Range("F2"), Order1:=xlAscending, _
              Key2:=wsMaster.Range("B2"), Order2:=xlAscending, _
              Key3:=wsMaster.Range("A2"), Order3:=xlAscending, _
              Header:=xlYes
              
Application.ScreenUpdating = True
End Sub

You can then record a macro which manipulates the data using a pivot table maybe.

I must admit I've not used pivot tables, so maybe someone else on the board can advise on writing a macro for this.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top