Need help writing macro for CSV file

[DKRbella0814]

Each morning, I pull electronic production data (from CNC machines) onto a MBD viewer (access database viewer) and export the filtered data to excel, saving the file as a CSV file.

Since this is a repetitive task, I would like to write a macro that formats the data within the CSV file into its desired form.

Because I am downloading a new CSV file each day, I figured that the best way to go about programming a macro with the desired formatting steps was to record and save the macro in a blank workbook. Then, each morning when I want to reformat the new CSV file, I would also open the XLS file (containing the formatting macro) and run the macro in the CSV workbook.

However, this approach has not worked for me and I am not sure if this is due to the fact that the macro file is a XLS file and the file needing formatted is a CSV file.

The steps which need completed in order to reformat the CSV file upon opening the file are listed below:

1) Select Row 1 and insert row
2) Select Cols A:N and autofit
3) Select Col D and insert column
4) Select Col F and insert column
5) Select Col G and insert column
6) Select Col C - Select 'Data' menu - Select 'Text to Columns' - Select 'Fixed Width' - Adjust to 10' - Finish
7) Select Col E - Select 'Data' menu - Select 'Text to Columns' - Select 'Fixed Width' - Adjust to 10' - Finish
8) Select Col C and E - Reformat Dates to mm/dd/yy and 'Center' justify
9) In row 1, insert Col headers:

B = Machine
C = Cycle Start Dt
D = Cycle start Time
E = Cycle End Date
F = Cycle End Time
G = Cycle Time (Min)
I = Good Cycles (True or False)
J = Downtime between Cycles
K = Description (JN and Operation)

10) In Col G - the following formula needs written and copied down to G5000 and then reformatted to time format hh:mm

=if(or(isblank(F2), isblank(D2), iserror(F2-D2)), " ", (F2-D2))

11) In Col J - the following formula needs written and copied down to J5000 and then reformatted to time format hh:mm

=if(or(isblank(D3), isblank(F2), iserror(D3-F2)), " ", (D3-F2))

12) In col I (Good Cycles), Conditional format the cells in col I:2 to I:5000 to do the following:

If active cell in Col I = TRUE , format fill Green
If active cell in Col I = FALSE, format fill RED
If active cell is blank = do not format

13) Select col A:N and auto fit



This is the macro code which I have tried to write but is not working. Can someone please help me out???

Sub Format()
'
' Format Macro
' Macro recorded 5/18/2011 by BMC
'

'
Rows("1:1").Select
Selection.insert Shift:=xlDown
Columns("A:N").Select
Columns("A:N").EntireColumn.AutoFit
Columns("D:D").Select
Selection.insert Shift:=xlToRight
Columns("G:G").Select
Selection.insert Shift:=xlToRight
Columns("F:F").Select
Selection.insert Shift:=xlToRight
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("c1:D1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(19, 1)), TrailingMinusNumbers _
:=True
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("e1:F1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(19, 1)), TrailingMinusNumbers _
:=True
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("C:C").Select
Selection.NumberFormat = "mm/dd/yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B1").Select
ActiveCell.FormulaR1C1 = "Machine"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Cycle St. Dt."
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cycle St. Time"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Cycle End Dt."
Range("F1").Select
ActiveCell.FormulaR1C1 = "Cycle End Time"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Cycle Time (Min)"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Good Cycles"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Downtime"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Description"
Columns("G:G").ColumnWidth = 7.29
ActiveCell.FormulaR1C1 = "Cycle Time (Min)"
Columns("A:N").Select
Columns("A:N").EntireColumn.AutoFit
Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(ISBLANK(RC[-3]),ISBLANK(RC[-1])),"""",ABS(RC[-1]-RC[-3]))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G5000"), Type:=xlFillDefault
Range("G2:G5000").Select
Columns("G:G").Select
Selection.NumberFormat = "h:mm;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("I2").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="TRUE"
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=FALSE"
Selection.FormatConditions(2).Interior.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(I2)"
Selection.Copy
Range("I3:I5000").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("J2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("J3").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(ISBLANK(RC[-6]),ISBLANK(R[-1]C[-4]),ISERROR(RC[-6]-R[-1]C[-4])),"""",(RC[-6]-R[-1]C[-4]))"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J4999"), Type:=xlFillDefault
Range("J3:J4999").Select
Range("N4997").Select


End Sub

 

[Trevor G]

Try this out.

I haven't tested the Loop through each cell but the rest works.

Sub challenge1()
'Look to answer question 1 to 5
Rows(1).Insert
Columns("A:N").EntireColumn.AutoFit
Columns("D").Insert
Columns("F").Insert
Columns("G").Insert

'Look to answer question 9
Range("B1") = "Machine"
Range("C1") = "Cycle Start Dt"
Range("D1") = "Cycle start Time"
Range("E1") = "Cycle End Date"
Range("F1") = "Cycle End Time"
Range("G1") = "Cycle Time (Min)"
Range("I1") = "Good Cycles (True or False)"
Range("J1") = "Downtime between Cycles"
Range("K1") = "Description (JN and Operation)"
'Look to answer question 10
Range("G3").FormulaR1C1 = _
"=IF(OR(ISBLANK(R[-1]C[-1]), ISBLANK(R[-1]C[-3]), ISERROR(R[-1]C[-1]-R[-1]C[-3])), "" "", (R[-1]C[-1]-R[-1]C[-3]))"
Range("G3").Select
Selection.AutoFill Destination:=Range("G3:G500"), Type:=xlFillDefault
'Look to answer question 11
Range("J3").FormulaR1C1 = _
"=IF(OR(ISBLANK(RC[-6]), ISBLANK(R[-1]C[-4]), ISERROR(RC[-6]-R[-1]C[-4])), "" "", (RC[-6]-R[-1]C[-4]))"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J500"), Type:=xlFillDefault
'Look to answer question 12
Range("I2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "True" Then
ActiveCell.Interior.Color = vbGreen
ElseIf ActiveCell.Value = "False" Then
ActiveCell.Interior.Color = vbRed
End If
ActiveCell.Offset(1, 0).Select
Loop
'Look to answer question 13
Columns("A:N").EntireColumn.AutoFit
End Sub

 

[DKRbella0814]

Will this work by writing the code in an xls file and running the program on a CSV file? Or, do I have to save the CSV file to an xls file so that the macro file and the data file are both xls files?

 

[DKRbella0814]

Can someone help me figure out the code to address Steps 6, 7, & 8??

 

[Trevor G]

Add this to the end of the code I created for you. This should run on your CSV file.

If the overall code is to run on any open CSV file, I would suggest you place the code in Personal Workbook and once you open the CSV file, you can use Alt + F8 selecting the macro and Run.

Sub Macro1()
'Question 6 & 8 Answered
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy"
With Selection
.HorizontalAlignment = xlCenter
End With

'Question 7 & 8 Answered
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy"
With Selection
.HorizontalAlignment = xlCenter
End With
Range("A1").Select
End Sub

 

[DKRbella0814]

Thank you for your help to this point. The program is running almost perfect, except I would like to add 2 lines of code in the identified areas below to convert the data to the Time Format hh:mm


Sub Format()

'Look to answer question 1 to 5
Rows(1).Insert
Columns("A:N").EntireColumn.AutoFit
Columns("D").Insert
Columns("F").Insert
Columns("G").Insert

'Question 6 & 8 Answered
Columns("C:C").Select
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy"
With Selection
.HorizontalAlignment = xlCenter
End With

'Question 7 & 8 Answered
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True
Selection.NumberFormat = "mm/dd/yy"
With Selection
.HorizontalAlignment = xlCenter
End With


'Look to answer question 9
Range("B1") = "Machine"
Range("C1") = "Cycle Start Dt"
Range("D1") = "Cycle start Time"
Range("E1") = "Cycle End Date"
Range("F1") = "Cycle End Time"
Range("G1") = "Cycle Time (Min)"
Range("I1") = "Good Cycles (True or False)"
Range("J1") = "Downtime between Cycles"
Range("K1") = "Description (JN and Operation)"
'Look to answer question 10
Range("G2").FormulaR1C1 = _
"=IF(OR(ISBLANK(R[-1]C[-1]), ISBLANK(R[-1]C[-3]), ISERROR(R[-1]C[-1]-R[-1]C[-3])), "" "", (R[-1]C[-1]-R[-1]C[-3]))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G500"), Type:=xlFillDefault
Convert all data values to time format hh:mm

'Look to answer question 11
Range("J3").FormulaR1C1 = _
"=IF(OR(ISBLANK(RC[-6]), ISBLANK(R[-1]C[-4]), ISERROR(RC[-6]-R[-1]C[-4])), "" "", (RC[-6]-R[-1]C[-4]))"
Range("J3").Select
Selection.AutoFill Destination:=Range("J3:J500"), Type:=xlFillDefault
Convert all data values to time format hh:mm

'Look to answer question 12
Range("I2").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "True" Then
ActiveCell.Interior.Color = vbGreen
ElseIf ActiveCell.Value = "False" Then
ActiveCell.Interior.Color = vbRed
End If
ActiveCell.Offset(1, 0).Select
Loop
'Look to answer question 13
Columns("A:N").EntireColumn.AutoFit

End Sub





Also, it would be helpful if someone can explain what the ARRAY means in this line of code so that I understand what the program is doing:

FieldInfo:=Array(Array(0, 1), Array(10, 1)), TrailingMinusNumbers:=True

And what the references [-1] [-3] are pointing to:

'Look to answer question 10
Range("G2").FormulaR1C1 = _
"=IF(OR(ISBLANK(R[-1]C[-1]), ISBLANK(R[-1]C[-3]), ISERROR(R[-1]C[-1]-R[-1]C[-3])), "" "", (R[-1]C[-1]-R[-1]C[-3]))"

 

[Trevor G]

Code for your time format is shown here:

Range("G2:G200").NumberFormat = "h:mm"

And what the references [-1] [-3] are pointing to:

From the cell which has the formula it referes to the row and columns. Easiest way to work this out directly is to record a macro where you select a cell and do a simple calculation, then look at the code it will help you understand it.

The Array in this case is refering to the Data to Text option and setting to 10 wide as per your request.

I hope this helps you achieve your goal...

 

[DKRbella0814]

I was able to make the adjustments that I needed to. Thank you so very much for your help!

 

[Trevor G]

Pleased to read you have a completed solution...