[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Auto script to copy excel data

Posted on 2012-09-04
3
Medium Priority
?
615 Views
Last Modified: 2012-09-10
Hi

I have 25 excel files generated daily that I would like to copy a few cells out into one main one. I have had a fiddle and have attempted some excel macros to copy what I require although am coming unstuck when trying to grab a particular value as the cell will never be static.

Background:
I require to record the peak usage of 25 systems ( column c ) as well as the total duration this hits its highest peak ( column D ). The layout of the each file is the same although some may have more lines than others and have duplicate entries in column c. This is why I have tried to group each MAX although this maximum number changes daily and I can have multiples of them. How do I take the max number and total duration this occurs when the cell that the total is displayed in can change daily ( i.e one day i will have 1 22 in column C, the next i could have 5 listed in which case the total i want wont always be C7) is this some kind of multiple array? or can this be done in vbscript?

After I have grabbed this data I just need to paste it into a "central" file on a new row for each day for each of the 25 files i grab data from.

Does anyone know how to get around the moving total or suggest another method? regards in advance.

Macro below I have attempted which gets the data and sorts the figure I require as well as a couple of example files i grab the data from.

Sub GetDataCH()

' GetData Macro
' Get spreadsheet data.
'
    ChDir "E:\"
    Workbooks.Open Filename:="E:\CH.xls"
    Sheets("CH").Select
End Sub

Sub FormatTheDataCH()
' SortTheData Macro

    Range("A5").Select
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("C6:C60"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("B6:B60"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("CH").Sort
        .SetRange Range("A5:D60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
' SubTotals Macro
'
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-3])"
    Range("E6").Select
    Selection.AutoFill Destination:=Range("E6:E60")
    Range("E6:E60").Select
    Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Range("E5").Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
Sub Macro4()
'
    Windows("CH.xls").Activate
    Sheets("CH").Select
End Sub

Open in new window

CH-Original.xls
CH-After-Macro..xls
BP-Another-example.xls
Ideal-output-in-central-file.xls
0
Comment
Question by:phitusers
  • 2
3 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 38364709
so after a quick look at your data,  I'd use the max on the column that has the different values.  This would give me the maximum value of anything in that row.  

Then I would lookup the values associated with the maximum value and move that data over.

Or heck I might just copy paste the row and insert it into the other worksheet.  


Just a couple of thoughts.

-SA
0
 

Accepted Solution

by:
phitusers earned 0 total points
ID: 38368915
I had quite a few difficulties with this seeing I have not used macro's for many years, below is code on how to overcome the following for fellow macro noobs / forgetful.

1. how to copy only visible subtotals, not hidden
2. Removing parts of a cell ( in my case the leading 6 characters from a string )
3. Pasting data onto the next blank line ( i lost hair on this ! )

Sub CH()
Call GetDataCH
Call FormatDataCH
Call CopyDataCH
End Sub
Sub GetDataCH()
    ChDir "E:\Busy Channels"
    Workbooks.Open Filename:="E:\Busy Channels\CH.xls"
    Sheets("Busy channels - coghurst").Select
    Sheets("Busy channels - coghurst").Name = "CH"
End Sub
Sub FormatDataCH()
    Range("A5").Select
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("C6:C60"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("CH").Sort.SortFields.Add Key:=Range("B6:B60"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("CH").Sort
        .SetRange Range("A5:D60")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
' SubTotals Macro
'
    Range("E5").Select
    ActiveCell.FormulaR1C1 = "Duration"
    Range("E6").Select
    ActiveCell.FormulaR1C1 = "=VALUE(RC[-3])"
    Range("E6").Select
    Selection.AutoFill Destination:=Range("E6:E60")
    Range("E6:E60").Select
    Selection.NumberFormat = "[$-F400]h:mm:ss AM/PM"
    Range("E5").Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub
Sub CopyDataCH()
    Windows("CH.xls").Activate
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("About this report").Select
    ActiveSheet.Paste
    Range("B6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=LEFT(RC[1],LEN(RC[1])-6)"
    Range("B6").Select
    Selection.Copy
    Range("D6").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F6").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=NOW()-1"
    Range("D6:F6").Select
    Selection.Copy
    Windows("AllChannels.xls").Activate
    Sheets("CH").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
End Sub

Open in new window

0
 

Author Closing Comment

by:phitusers
ID: 38382342
Code could be simplified?
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question