Requirements:
1. A Spot Billing Machine which records Monthly Gas Usage in an area downloads the data in CSV files.2. When Opened in a Notepad++
COL 12 - 2 DIGITS - DC - Distribution Code
COL 14 - 6 DIGITS - SCNO - Service Number
COL 71 - 2 DIGITS - STS - Status of Reading (integer)
COL 73 - 7 DIGITS DATE... dd (CRD) MM (CRM) YYYY (CRY) (INTO 3 COLUMNS)
COL 423 - TIME hh:mm (24 hr format)
3. Insert Column headers
4. Sort by CRD, TIME and SCNO
5. Adjust width of columns to Autofit the columns
6. Get the number of records taken on each Day (CRD) and also as per the Status (STS)
7. Save as Excel (xlsx) with the same name and to the same location.
If you are unable to follow the steps, please have a look at the Video which is a screen recording of the steps.
The videos do not include the updates done to the recorded Macro.
Please note that the Videos are just Screen Recording and doesn't include any audio but includes text for guidance wherever required.
Part-1
Part-2
Part-3 (Final Output)
1. Open a New Microsoft Excel Workbook. I am using Office 2007.
2. Save the file as Name=Formatting Text files into Excel and Type= Excel Macro-Enabled Workbook (.xlsm)
3. Go to View Tab. Click on Macro dropdown. Select Record Macro. Leave the default values and click OK button
4. Click on the Office Ribbon on the top left corner and select Open. Browse the .txt file which required to be formatted. If the file is not listed, change the Type of File = All Files. Select GAS-READING_1. Click on Open button. Text Import Wizard popup is displayed.
5. Let the default selection = Fixed Width and accept any other parameters. Click on Next button.
6. Now, we select the columns which are required and place a column break.
To Create a break line, click before the required column. We get a line with arrow to indicate the break.
For Delete an existing break line, double click on the line.
Follow actions to be done for the line Breaks. The column number indicates the position at which the line break should be created or removed (first number is 0)
3 - Delete
11,13,19 - Create
25,42,52,62 - Delete (any other line breaks between 20 and 69)
70,72,74,76,80 - Create
122 to 421 - Delete any line breaks (any other line breaks between 81 and 421)
422,427 - Create
428 to End - Delete
7. Click on Next button. Click on Finish button
8. Select the following columns by clicking on the Column Headers
A,D,I,K
9. Right click on any selected Column Header and click Delete
10. Click on Row1 and the entire Row gets selected. Right click and select Insert. New Row is created above.
11. Enter the following in the respective cells so that they become the column headers (Press TAB to go to the next cell)
Al - DC
B1 - SCNO
C1 - STS
D1 - CRD
E1 - CRM
F1 - CRY
G1 - TIME
12. Click on the top left corner so that all cells are selected. Double Click on the border between any two column headers to Autofit the columns.
13. Click on Home tab. Click on Sort & Filter dropdown. Select Custom Sort
Assumption: All the readings are taken in the same month
14. Select Sort By = CRD
Click on Add Level. Select Sort By=TIME
Click on Add Level. Select Sorty By = SCNO
Click OK on the Sort Popup
15. Click on A1 and Select A1 to G1 using Shift+Right Arrow Keys
From the Fill Color option, select Yellow
16. Click on Al and holding Shift key go to the last row by holding the Down Arrow key. Use the Right Key to select the columns till G. This selects All cells with value.
In the Alignment tab, click on Center.
17. With the same selection, click on All Borders
18. Go to View tab. Click on Freeze Panes dropdown and select Freeze Top Row.
19. Click on Macros > Stop Recording
20. Close the Excel creating (without saving)
21. In the Main Excel, click on Macros>View Macro. The Macro1 is selected by default. Click on Run button.
22. Verify the following:
Rows are sorted by CRD, TIME and SCNO
Column Header in Row1 is Colored=Yellow
All Data cells are Autofit and Centred
All Data Cells have All Borders
If we scroll down, the top row (Heading row) remains freezed.
The File Name and Sheet Name are the same as the .txt FileName
--------------Video Part-1 Ends--------------
Calculations
23. Go to the Main macro, click on Macros > Record Macro. Accept the default values Macro2. Click on OK button
24. Go to the newly formatted excel sheet (generated previously)
25. Select D1 and holding Shift Key go to the last value of Column D by pressing the Down Arrow key.
26. Go to Data tab. Click on Advanced
27. On the Advanced Filter popup, select the following:
Copy to another location
List range = leave as it is
Copy to = select the red arrow and click on the L1 cell. Click again on the red arrow.
Unique records only = checked
Click on OK button
28. In cell M2, write the below formula and press Enter or Tab Key:
= COUNTIF($D$2:$D$651,L2)
This Counts the number of times the value in L2 has occurred in the D column.
For more information - click here
29. Select M2 and COPY. Select M3 and press Down Arrow key till there is value in Column L.
Say, M5. PASTE the values.
30. Click on M6 and write the below formula and press Enter or Tab Key:
=SUM(M2:M5)
M5 is the last value in Column M
31. In Cell L6, write as Total and press Enter or Tab Key.
In Cell M1, write as COUNT and press Enter of Tab Key.
32. Select L1 and press Shift Key+ Right Arrow to select M1. Go to Home tab and apply Yellow fill.
33. Select the cells L1 to M6 (Use Shift and Down/Right Arrow Keys)
Apply All Borders
Apply Center Text.
Note: We can see that Center text is already applied for CRD column heading. If we click once on the Center text icon, they all are restored to the default alignment. Click once again on the Center text to see that all are centered.
34. Select the cell L6 and press Shift Key+ Right Arrow to select M6. Go to Home tab and apply Light Blue fill. With the same selection, click on Bold.
Checking the Total Rows against the Total Count
35. After 4 rows after the last value of Column L (above Total),say L enter the text=Last Row Number. Press Enter of Tab key
36. We can enter some text as we need to write the formula to calculate the last value row of the sheet in the Macro.
say, 651
37. Click on L10. Bold it and Center text
38. Click on cell L11 and enter the below text and press Enter Key:
Minus 1 from the above to match with Total Count
39. Select cell L12 and write the below formula:
=IF(L10-1=M6,TRUE,FALSE)
Press Enter or Tab Key
Select cell L12 and Bold it.
40. Enter the following Text in L13:
TRUE means Matching
Enter the following text in L14:
FALSE means Not Matching. Check it.
41. Similarly we do for Status column:
Select C1 and holding Shift Key go to the last value of Column C by pressing the Down Arrow key.
42. Go to Data tab. Click on Advanced
43. On the Advanced Filter popup, select the following:
Copy to another location
List range = leave as it is
Note: Ensure that the selected Column C is reflected here. Sometimes, the previous selection (Column D) is displayed.
Copy to = select the red arrow and click on the R1 cell. Click again on the red arrow.
Unique records only = checked
Click on OK button
44. In cell S2, write the below formula and press Enter or Tab Key:
= COUNTIF($C$2:$C$651,R2)
This Counts the number of times the value in R2 has occurred in the C column.
For more information - click here
45. Select S2 and COPY. Select S3 and press Down Arrow key till there is value in Column R.
Say, R7. PASTE the values
46. Select Columns R and S and click on Sort & Filter. Select Custom Sort.
Select Sort by as STS. Click OK button.
47. Click on S8 and write the below formula and press Enter or Tab Key:
=SUM(S2:S7)
R7 is the last value in Column R
48. In Cell R8, write as Total and press Enter or Tab Key.
In Cell S1, write as COUNT and press Enter of Tab Key.
49. Select R1 and press Shift Key+ Right Arrow to select S1. Go to Home tab and apply Yellow fill.
50. Select the cells R1 to S6 (Use Shift and Down/Right Arrow Keys)
Apply All Borders
Apply Center Text.
Note: We can see that Center text is already applied for CRD column heading. If we click once on the Center text icon, they all are restored to the default alignment. Click once again on the Center text to see that all are centered.
51. Select the cell R8 and press Shift Key+ Right Arrow to select S8. Go to Home tab and apply Light Blue fill. With the same selection, click on Bold.
Saving the Workbook
52. Click on the Office ribbon on top left corner. Select Save As. The default location/Name is the same as the Text file. Click on Save button. Close the Workbook
The file is saved as .xlsx format (default 2007 format). We will explore ways to save if it is required in .xls format during code editing.
53. Go to Main Workbook and Stop recording.
54. Click on Macros>View Macro. Select Macro2 and click Edit
55. Verify the VBA script and delete any lines relating to Scroll Bar and Windows.Activate
56. COPY the code between Sub and End Sub.
Go to Module1 (left hand menu) and Paste it between the end of Macro1 and before End Sub
Note: In case, you have stopped Macro recording before closing the newly created workbook, Don't Worry. Add the below line at the end (before End Sub)
ActiveWorkbook.Close
57. Save the VBA code
58. Close the VBA window. Close all Excel Windows
59. Delete the excel file newly created as result of the macro. (same name as the Text file)
60. Open the Macro workbook. Enable Macros if Security popup is displayed.
61. Click on Macros>View Macro. Select Macro1 and click Run.
Notice the Excel does the steps above and Closes the new formatted excel
62. Open the formatted excel and verify if everything is as expected:
Count of CRD
Count of STS
File Name and Location
Few points to note in the above process:
A. We have fixed the last row of the data. There might be chances of a new file with a different last row. So, we calculate the last row and use it instead of using fixed last row value.
B. The file location and name is hardcoded in the macro. We should be able to specify the location and .txt file name and the excel should be generated.
Steps to Generalize the Macro for any .txt file in any location
63. We create certain variables to use in the code. These should be declared at the start
Dim LastRow As Long
Dim LastRowCount As Long
64. After doing Autofit, we need to find the last row of the data. Write the below code:
Set sht = ActiveSheet
Set StartCell = Range("D2")
LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
--------------Video Part-2 Ends--------------
65. Replace ActiveWorkbook.Worksheets("GAS-READING_1") with sht. This is to ensure to make the sheet more generic.
66. In the lines where there are Sort operations, replace the fixed row 651 to a generic last row.
"D2:D651" should be replaced with "D2:D" & LastRow
Notice that the LastRow is outside the quotations and append the value to the D using the &
67. Similarly for G and B columns. Change also in the With code "A1:G651"
68. In the code for centering all cells, replace 651 with & LastRow
69. In the code to count based on CRD, update 651 with & LastRow.
Note if there are selections prior to the Unique code, please remove those .Select lines
70. After getting the unique values of the CRD column, we need to find the last row for this unique CRD list. So, write the below code after Unique filter and before selecting M2
Set StartCell = Range("L2")
LastRowCount = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
71. Update the COUNTIF formula to remove 651. Replace the value with the below:
=COUNTIF(C[-9],RC[-1])
The range is the column that is present 9 colums to the left.
72. Change the "M3:M5" to "M3:M" & LastRowCount
Change "M6" to "M" & LastRowCount + 1
73. Change the SUM formula to remove the 4.
=SUM(R[-" & LastRowCount & "]C:R[-1]C)
74. Change the "L6" to "L" & LastRowCount + 1
75. In the code to formatting the CRD Count table, change the below:
"L1:M6" to "L1:M" & LastRowCount + 1
"L6:M6" to "L" & LastRowCount + 1 & ":M" & LastRowCount + 1
76. In the code to check the Total Rows against the total count, change the below:
"L9" to "L" & LastRowCount + 4
"L10" to "L" & LastRowCount + 5
"651" to LastRow
"L11" to "L" & LastRowCount + 6
"L12" to "L" & LastRowCount + 7
"L13" to "L" & LastRowCount + 8
"L14" to "L" & LastRowCount + 9
77. Similarly do for the Code for Count by STS
Code to get the Last Row for the unique list of STS:
Set StartCell = Range("R2")
LastRowCount = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
"C1:C651" to "C1:C" & LastRow
COUNTIF formula to =COUNTIF(C[-16],RC[-1])
"S3:S7" to "S3:S" & LastRowCount
78. We need to sort the status as well. (CRD didn't require sorting as it is already sorted by CRD as the first criteria)
Replace "R2:R651" to "R2:R" & LastRowCount
"R1:S651" to "R1:S" & LastRowCount
79. We format the Count of STS table:
"R8" to "R" & LastRowCount + 1
"S8" to "S" & LastRowCount + 1
SUM formula to =SUM(R[-" & LastRowCount & "]C:R[-1]C)
"R1:S8" to "R1:S" & LastRowCount + 1
"R8:S8" to "R" & LastRowCount + 1 & ":S" & LastRowCount + 1
80. Close the VBA window. Delete the already formatted excel file in the folder.
81. On the Original Book. Click on Macros>View Macros. Select Macro1 and click on Run
82. The excel is opened, formatted and closed. Open the formatted excel and verify if everything is as expected.
So far, we have hard coded the file location and Name in the VBA. We now remove that.
83. On the Original book, we remove any other sheets. Rename Sheet1 to Paths
Give Column Headings:
A1 = File Path
B1 = File Name (excluding extn)
Fill A1 and B1 with Yellow color and Bold them.
84. Get the File location of the .txt and place it in A2. (Right Click the .txt file and Click Properties. COPY the Location value)
Get the File Name (excluding .txt) and place in B2.
85. Click on View Tab. Click on Macros>View Macros. Select the Macro1 and click Edit
86. Delete any ChDir line
87. Create new variables:
Dim filePath As String
Dim fileName As String
88. Write the following code to COPY the values to filePath and fileName
filePath = Range("A2").Value
fileName = Range("B2").Value
89. Remove the hardcoded file in Workbooks.OpenText with the below:
filePath & "\" & fileName & ".txt"
Also, remove the hardcoded in ActiveWorkbook.SaveAs at the end of the code:
filePath & "\" & fileName & ".xlsx"
90. Now we need to be able to format multiple files in multiple locations when specified in the excel.
Create new variables:
Dim RowCountPath As Integer
Dim LastRowPath As Long
91. Determine the last row of path column with the following code:
Set shPath = ActiveSheet
Set StartCellPath = Range("A2")
LastRowPath = shPath.Cells(shPath.Rows.Count, StartCellPath.Column).End(xlUp).Row
92. Loop through all the rows using a For loop:
For RowCountPath = 2 To LastRowPath
93. Change the filePath and fileName to below:
filePath = Range("A" & RowCountPath).Value
fileName = Range("B" & RowCountPath).Value
94. Place the following code (before End Sub)to loop to the next value
Next RowCountPath
95. Save the VBA and close it. Give another file name (along with location) in Row3 and test the Macro1.
96. How about getting a message on how many files are formatted to excel. Edit the Macro1 and place the below code before End Sub (after Next)
MsgBox LastRowPath - 1 & " Files have been converted to Excel"
97. We don't always want to open the View Macros and select Run. We can create a button to Run the code whenever we want.
98. If Developer tab is not displayed. Go to Excel Options > Popular > Show Developer tab in the Ribbon should be checked.
On the Developer tab, click on Insert > Button (Form Control). Drag the icon to draw a rectangle anywhere on the excel. After generating the button, release the left click.
99. On the Assign Macro popup, select Macro1. Click OK button
100. Button 1 can be renamed to anything.
101. Verify by clicking on the Button. Macro is run successfully.
What if we want to format all the Text Files in a folder. We should only specify the path and the Macro should be able to pick up all the Text files within the Path.
102. Create a COPY of the Macro Workbook created. Append the file name with _FileNames
This is to ensure that you can have both the versions in separate files.
103. Open the Original Macro Workbook. Create a new sheet = FileNames.
104. COPY the following code in another Sub to generate the file names in the given path:
Sub GetFileName(filePath As String)
Dim fName As String
Dim rNum As Long
Dim LastRowName As Long
Worksheets("FileNames").Activate
'To clear any previous entries
ActiveSheet.Range("A:C").ClearContents
Range("A1").Value = filePath
fName = Dir(filePath & "\*.txt")
'fName contains the first .txt file in the filePath
rNum = 1
'Loop through the files till there are no more files
Do While fName <> ""
Range("B" & rNum).Value = fName 'Copy the fName into the B column
If rNum > 1 Then
Range("A" & rNum).Value = filePath 'Copy the filePath into A column. We need to avoid copying to A1 as it is already Copied
End If
rNum = rNum + 1
fName = Dir()
'fName contains the next .txt file in the filePath
Loop
'Splitting the fileName and removing the extension
Columns("B:B").Select
Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("B:B").EntireColumn.AutoFit
ActiveSheet.Range("C:C").ClearContents
'Selecting the cells to COPY
Set shPath = ActiveSheet
Set StartCellPath = Range("A1")
LastRowName = shPath.Cells(shPath.Rows.Count, StartCellPath.Column).End(xlUp).Row
'Copy from FileNames sheet to Paths sheet
ActiveSheet.Range("A1:B" & LastRowName).Copy Worksheets("Paths").Range("A2")
End Sub
105. Enter the following code to call the COPY sub (immediately after declaring variables):
Create the following variable:
Dim filePathCopy As String
Code:
filePathCopy = Range("A2").Value
Call GetFileName(filePathCopy)
Worksheets("Paths").Activate
106. Save this Workbook by appending with _Paths
We can combine both the above Macros into one as below:
107. COPY the code inside the For Loop into another Sub say, FormatExcel
FormatExcel (RowCountPath As Integer)
In the For loop, place the below call function:
Call FormatExcel(RowCountPath)
108. Copy the Macro1 sub in another Sub and remove the code relating to Paths.
Variable:
Dim filePathCopy As String
Code:
filePathCopy = Range("A2").Value
Call GetFileName(filePathCopy)
Worksheets("Paths").Activate
Rename the Sub as FileNames
109. Rename the Macro1 as Paths.
110. Close the Editor and on the Original Workbook, create another Control Button
Assign this to FileNames macro
111. Right click existing button and click Assign Macro. Select the Paths macro and click OK button
112. Delete any existing Excel files generated and Ready to test
113. Verify with both the buttons and options.
Additional Tips:
114. In case you want to save the excel as .xls format (prior to 2007 version), then we need to replace the SaveAs code with the below (we get Compatibility Warning messages and we want to avoid them)
ActiveWorkbook.CheckCompatibility = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:= _
filePath & "\" & fileName & ".xls", FileFormat _
:=xlExcel8, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
Application.DisplayAlerts = True
Here are the Macro files for your quick reference.
Hope you enjoyed converting CSV files to Excel and are ready for doing wonders with Excel VBA !!!
No comments:
Post a Comment