Coffinated
asked on
How to automate the follwoing procedure in MS Excel 2003
I need to modify similar CSV in Excel files every day, columns are the same, the only variation is the data itself. When I record a macro it'll work on one file, but not on all of them. Here are the steps I take to modify the CSV file:
1a. Open input.csv file
2a. Sort by column D
3a. Select and copy all rows with "ACC90" In column D
4a. Paste it one line below the block of data
Work with pasted data in the second block:
1. Rename all instances of "ACC90" to "ACC9A" in Column D
2. Copy formulas from another spreadsheet, paste them in first row, column M in the new block
3. Paste columns M-P for all records down
4. Copy columns M,N for all records (row X to 1)
5. Delete values in row M,N in empty line between two blocks of data
6. Select all values in column N (new block)
7. Rename all instances of "B000?" in column N to "B0006"
8. Copy columns M,N and paste as data to columns D,H
9. Copy formulas from column P,Q in the first row of the second block
10. Paste this formula to every row below where either:
- Row in column J has a value
- Row in column P == 0
11. Copy all data in columns P,Q between first and last row in the second block
12. Paste this data as value in first row, column I in the second block
13. Copy all data in column O between first and last row in the second block
15. Paste it as value to first row in column G
16. Delete all entries in columns M-Q
17. Sort all by column B
18. Save as .csv
19. exit
I need the macro to dynamically find correct fields/ranges, for example step #3a may select A1:K9 or A4:k99 etc... Step 4a should find the last row with data and paste the selection two rown down (leaving one empty line between blocks).
input.csv - the original file
final.csv - modified file
Formulas from formula.xls are:
M1=+CONCATENATE(0,F1)
N1=+CONCATENATE("00",H1)
O1=+ROUND(G1/0.15*0.07,2)
P1 - blank
Q1=+ROUND(J1/0.15*0.07,2)
M2=+CONCATENATE(0,F2)
N2=+CONCATENATE("00",H2)
O2=+ROUND(G2/0.15*0.07,2)
P2=+ROUND(I2/0.15*0.07,2)
Q2 - blank
M3,4 N3,4 O3,4 P3,4 are the same as M-P in row 2
Thank you
final.txt
1a. Open input.csv file
2a. Sort by column D
3a. Select and copy all rows with "ACC90" In column D
4a. Paste it one line below the block of data
Work with pasted data in the second block:
1. Rename all instances of "ACC90" to "ACC9A" in Column D
2. Copy formulas from another spreadsheet, paste them in first row, column M in the new block
3. Paste columns M-P for all records down
4. Copy columns M,N for all records (row X to 1)
5. Delete values in row M,N in empty line between two blocks of data
6. Select all values in column N (new block)
7. Rename all instances of "B000?" in column N to "B0006"
8. Copy columns M,N and paste as data to columns D,H
9. Copy formulas from column P,Q in the first row of the second block
10. Paste this formula to every row below where either:
- Row in column J has a value
- Row in column P == 0
11. Copy all data in columns P,Q between first and last row in the second block
12. Paste this data as value in first row, column I in the second block
13. Copy all data in column O between first and last row in the second block
15. Paste it as value to first row in column G
16. Delete all entries in columns M-Q
17. Sort all by column B
18. Save as .csv
19. exit
I need the macro to dynamically find correct fields/ranges, for example step #3a may select A1:K9 or A4:k99 etc... Step 4a should find the last row with data and paste the selection two rown down (leaving one empty line between blocks).
input.csv - the original file
final.csv - modified file
Formulas from formula.xls are:
M1=+CONCATENATE(0,F1)
N1=+CONCATENATE("00",H1)
O1=+ROUND(G1/0.15*0.07,2)
P1 - blank
Q1=+ROUND(J1/0.15*0.07,2)
M2=+CONCATENATE(0,F2)
N2=+CONCATENATE("00",H2)
O2=+ROUND(G2/0.15*0.07,2)
P2=+ROUND(I2/0.15*0.07,2)
Q2 - blank
M3,4 N3,4 O3,4 P3,4 are the same as M-P in row 2
Thank you
Sub Test()
'
' Test Macro
' Macro recorded 12/16/2009 by coffinated
'
'
ChDir "C:\Documents and Settings\coffinated\Desktop"
Workbooks.OpenText Filename:= _
"C:\Documents and Settings\coffinated\Desktop\input.txt", Origin:=437, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1)), TrailingMinusNumbers:=True
Columns("C:C").EntireColumn.AutoFit
Columns("A:K").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:K23").Select
Selection.Copy
Selection.End(xlDown).Select
Range("A41").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=27
Range("D41").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ACC9A"
Range("D41").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("M41").Select
Workbooks.Open Filename:= _
"C:\Documents and Settings\coffinated\Desktop\formulas.xls", Origin:= _
xlWindows
Range("M1:Q4").Select
Selection.Copy
Windows("input.txt").Activate
ActiveSheet.Paste
Range("M44:P44").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("M44:P63"), Type:=xlFillDefault
Range("M44:P63").Select
Range("M41:N41").Select
Selection.AutoFill Destination:=Range("M1:N41"), Type:=xlFillDefault
Range("M1:N41").Select
ActiveWindow.SmallScroll Down:=-15
Range("M40:N40").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Application.WindowState = xlMinimized
Range("H41:H63").Select
Selection.Replace What:="B000?", Replacement:="B0006", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("M:M").Select
Range("M31").Activate
Selection.Copy
Columns("F:F").Select
Range("F31").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("N:N").Select
Range("N31").Activate
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.SmallScroll Down:=-45
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=30
Range("P41:Q41").Select
ActiveWindow.SmallScroll ToRight:=-7
Range("O35").Select
Application.CommandBars("Task Pane").Visible = False
Range("N42").Select
ActiveWindow.SmallScroll ToRight:=-2
Range("P42").Select
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Application.CutCopyMode = False
Range("P41:Q41").Select
Selection.Copy
Range("P46").Select
ActiveSheet.Paste
Range("P52").Select
ActiveSheet.Paste
Range("P54").Select
ActiveSheet.Paste
Range("P56").Select
ActiveSheet.Paste
Range("P62").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("P41:Q63").Select
Selection.Copy
Range("I41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("O41").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Range("G41").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("M:Q").Select
Range("M31").Activate
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.LargeScroll ToRight:=-1
Columns("A:K").Select
Range("A31").Activate
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveWindow.SmallScroll Down:=-57
Range("A1").Select
ActiveWorkbook.Save
ActiveWindow.Close
ActiveWindow.Close
End Sub
final.txt
Sorry my mistake...shoudl have read better....am working on it.
It appears that the Input and Final File are both the same.
Could you re-provide the Input file?
Could you re-provide the Input file?
ASKER
bromy2004:
You're right input and output are the same, my mistake.... Attached is correct input.txt
input.txt
You're right input and output are the same, my mistake.... Attached is correct input.txt
input.txt
I'm a bit confused about Steps 6 and 7.
Step 6 is Selecting Column N (Which Contains the Formula =CONCATENATE("00",H1) )
Step 7 is Replacing "B000?" with B0006.
but all the values in Column N would be "00B000x" so would it still replace the B000? with B0006?
Step 6 is Selecting Column N (Which Contains the Formula =CONCATENATE("00",H1) )
Step 7 is Replacing "B000?" with B0006.
but all the values in Column N would be "00B000x" so would it still replace the B000? with B0006?
Actually i'm getting a bit confused with a few of the Steps.
Could you outline what you want done, But not how you are doing it at the moment....if you understand what i mean.
For example, rather than say paste the formulas to the cells,
say "for all of Column F add a "0" in front."
Because VBA doesn't need to put formulas into another cell as we can edit the cell directly
(i.e
***Range("A1")="0" + Range("A1")***
is the same as
***=CONCATENATE("0",A1)***
in Excel, and then Copy Pasting to another Cell.
Understand?
Could you outline what you want done, But not how you are doing it at the moment....if you understand what i mean.
For example, rather than say paste the formulas to the cells,
say "for all of Column F add a "0" in front."
Because VBA doesn't need to put formulas into another cell as we can edit the cell directly
(i.e
***Range("A1")="0" + Range("A1")***
is the same as
***=CONCATENATE("0",A1)***
in Excel, and then Copy Pasting to another Cell.
Understand?
Try and send us the excel sheet with comments for easier understanding.
ASKER
bromy2004:
Step 7: Replacing "B000?" with B0006 - there may be different accounts (B0002, B0002...B0005) starting with B000, all of them should be converted to B0006.
The formulas simply add "0" to columns M,N, and calculate percentage of the original value.
I am going to record a short video showing all steps needed to do the process, that should give you an idea of what needs to be done.
Step 7: Replacing "B000?" with B0006 - there may be different accounts (B0002, B0002...B0005) starting with B000, all of them should be converted to B0006.
The formulas simply add "0" to columns M,N, and calculate percentage of the original value.
I am going to record a short video showing all steps needed to do the process, that should give you an idea of what needs to be done.
ASKER
Attached are SWF and HTML files, download both and open excel.swf.html with your browser.
excel.swf.html
excel.swf
excel.swf.html
excel.swf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
bromy2004:
Run-time error '438':
Object does not support this property or method
Debug points to first line of "With" loop.
With ActiveWorkbook.Worksheets(
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(1, 2), Cells(MaxRow - 1, 2)), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range(Cells(1, 4), Cells(MaxRow - 1, 4)), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange ActiveSheet.UsedRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
I am running it using Excel 2003 if that makes any difference.
Run-time error '438':
Object does not support this property or method
Debug points to first line of "With" loop.
With ActiveWorkbook.Worksheets(
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(1, 2), Cells(MaxRow - 1, 2)), SortOn:=xlSortOnValues, Order:=xlAscending
.SortFields.Add Key:=Range(Cells(1, 4), Cells(MaxRow - 1, 4)), SortOn:=xlSortOnValues, Order:=xlAscending
.SetRange ActiveSheet.UsedRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
I am running it using Excel 2003 if that makes any difference.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I'll try the new code first thing Monday morning, I should have mentioned excel 2003.
ASKER
Sorry for the delay, the code looks good with exception of adding 0 and 00
'Add 0 to Column F
Cells(i, 6).Value = "0" + Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).Value = "00" + Cells(i, 8).Value
It also doesn't work in Excel 2003, original values are not changed.
'Add 0 to Column F
Cells(i, 6).Value = "0" + Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).Value = "00" + Cells(i, 8).Value
It also doesn't work in Excel 2003, original values are not changed.
Hi Coffinated,
change that section to
'Add 0 to Column F
Cells(i, 6).Value = "'0" + Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).Value = "'00" + Cells(i, 8).Value
change that section to
'Add 0 to Column F
Cells(i, 6).Value = "'0" + Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).Value = "'00" + Cells(i, 8).Value
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@WarCrimes,
my change adds an apostrophe in front.
forcing it to stay as text, regardless if the user changes the formatting.
my change adds an apostrophe in front.
forcing it to stay as text, regardless if the user changes the formatting.
Forgot to change the second one to Cells(i, 8). Sorry bout that
'Add 0 to Column F
Cells(i, 6).NumberFormat = "@"
Cells(i, 6).Value = "0" & Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).NumberFormat = "@"
Cells(i, 8).Value = "00" & Cells(i, 8).Value
bromy's code does the same thing by putting the singel quote in front of the 0.....forgot about that one. Always nice to know multiple ways to do things though, just in case.
'Add 0 to Column F
Cells(i, 6).NumberFormat = "@"
Cells(i, 6).Value = "0" & Cells(i, 6).Value
'Add 00 to Column H
Cells(i, 8).NumberFormat = "@"
Cells(i, 8).Value = "00" & Cells(i, 8).Value
bromy's code does the same thing by putting the singel quote in front of the 0.....forgot about that one. Always nice to know multiple ways to do things though, just in case.
@bromy
Yup, we were posting basically the same time.
Cheers,
WC
Yup, we were posting basically the same time.
Cheers,
WC
It seems to be a simple cleanup...just might take a few mins.