• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

VBA: Reformat data exported from software to excel.

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
Saqib Husain, Syed
Asked:
Saqib Husain, Syed
  • 5
  • 3
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

cheers, teylyn
0
 
Saqib Husain, SyedEngineerAuthor Commented:
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
 
Saqib Husain, SyedEngineerAuthor Commented:
Also, it would be good if the macro can do a sum of numbers which can be compared with the provided totals.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ButlerTechnologyCommented:
Can you provide a mock up of what the final results should look like?
0
 
Saqib Husain, SyedEngineerAuthor Commented:
It should look just the same - but without the extra columns and merged cells
0
 
ButlerTechnologyCommented:
Do you want the merged cells removed starting at B10?
0
 
Saqib Husain, SyedEngineerAuthor Commented:
Ok I have done a manual format.


***** File edited ********
Desired-format.xls
0
 
ButlerTechnologyCommented:
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
 
Saqib Husain, SyedEngineerAuthor Commented:
Perfect

Thanks
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now