Solved

VBA: Reformat data exported from software to excel.

Posted on 2014-01-01
9
236 Views
Last Modified: 2014-01-03
I have attached a file exported from an accounting software to excel which inserts the data and merges cells unnecessarily.

Can someone do a macro for me to convert this format to a more excellish layout?
Reformat-software-output.xls
0
Comment
Question by:Saqib Husain, Syed
  • 5
  • 3
9 Comments
 
LVL 50

Expert Comment

by:teylyn
ID: 39750728
Hello,

save the file as CSV. Close and re-open. Now you can easily delete empty rows and columns.

cheers, teylyn
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39750797
Thanks, teylyn but I need a macro to do this. I get such a file frequently and I want to put a button on my ribbon which will simply straighten it.
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39750799
Also, it would be good if the macro can do a sum of numbers which can be compared with the provided totals.
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39750874
Can you provide a mock up of what the final results should look like?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39750879
It should look just the same - but without the extra columns and merged cells
0
 
LVL 6

Expert Comment

by:ButlerTechnology
ID: 39750894
Do you want the merged cells removed starting at B10?
0
 
LVL 43

Author Comment

by:Saqib Husain, Syed
ID: 39750920
Ok I have done a manual format.


***** File edited ********
Desired-format.xls
0
 
LVL 6

Accepted Solution

by:
ButlerTechnology earned 500 total points
ID: 39750957
Try the following macro -- it is close minus some formatting (I think)

Public Sub Reformat()

Range("B10").Activate
Do
  With ActiveCell
  .Range("R1").Value = .Range("A1").Value
  .Range("S1").Value = .Range("F1").Value
  .Range("T1").Value = .Range("K1").Value
  .Range("U1").Value = .Range("O1").Value
  End With
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)

' Labels
Range("S1").Value = Range("B1")
Range("S2").Value = Range("B2")
Range("S3").Value = Range("B3")
Range("S4").Value = Range("B4")
Range("S7").Value = Range("B7")
Range("v7").Value = Range("Q7")

' Totals
  ActiveCell.Offset(1, 0).Activate
  ActiveCell.Range("T1").Value = ActiveCell.Range("K1").Value
  ActiveCell.Range("U1").Value = ActiveCell.Range("O1").Value
  
Range("S1").Activate
Range("A1:R1").EntireColumn.Delete
Range("A1").Activate

' Column Widths
  Range("A1").ColumnWidth = 15
  Range("B1").ColumnWidth = 30
  Range("C1").ColumnWidth = 15
  Range("D1").ColumnWidth = 15
  
  Range("A1:D1").Merge
  Range("A2:D2").Merge
  Range("A3:D3").Merge
  Range("A4:D5").Merge
  Range("A1:D5").HorizontalAlignment = xlCenter
  
  Range("A8").EntireRow.Delete
  Rows("9:9").Font.Bold = True
  
  Columns("C:D").NumberFormat = "$#,##0"
  
  Range("A9").Activate

Do
  ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)
ActiveCell.Offset(2, 2).Activate

Dim Top As Integer
  Top = ActiveCell.Row - 10
  ActiveCell.FormulaR1C1 = "=SUM(R[-" & Top & "]C:R[-3]C)"
  ActiveCell.Offset(0, 1).Activate
  ActiveCell.FormulaR1C1 = "=SUM(R[-" & Top & "]C:R[-3]C)"
  Range("A1").Activate
End Sub

Open in new window

0
 
LVL 43

Author Closing Comment

by:Saqib Husain, Syed
ID: 39753337
Perfect

Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now