justearth
asked on
Find cells and bold their text in many csv files
Hello,
I have a directory that contains many csv files. I would like a script to go through the csv files and bold all the text in all cells that have a value of less than 0.7
I would appreciate annotated code and any other suggestions for this procedure. I am a beginner.
Thanks,
JE
I have a directory that contains many csv files. I would like a script to go through the csv files and bold all the text in all cells that have a value of less than 0.7
I would appreciate annotated code and any other suggestions for this procedure. I am a beginner.
Thanks,
JE
ASKER
Patrick,
Yes, okay. Great point. If a script could take the csv files and bold them and then save as an excel spreadsheet (.xls) would be fantastic. Hope thats clearer.
Thanks,
JE
Yes, okay. Great point. If a script could take the csv files and bold them and then save as an excel spreadsheet (.xls) would be fantastic. Hope thats clearer.
Thanks,
JE
I believe you mean import the CSV file into Excel, bold them as needed, and then save the Excel file.
Patrick(ab)
Patrick(ab)
ASKER
Patrick,
Yes that is what I mean. Sorry about my poor explanation. I am glad that someones besides me knows what I want.
Thanks,
JE
Yes that is what I mean. Sorry about my poor explanation. I am glad that someones besides me knows what I want.
Thanks,
JE
Patrick Matthews is the man for you on this one. I leave for China in a couple of hours so I'm going to give this one a miss.
Hi,
I have adapted code from http://www.xtremevbtalk.com/showthread.php?t=300278 to conditional format cells Bold if the value is less thean 0.7
Pls run the code, when the 'Open" window appears, navigate to your directory and click on one of the csv files. the code will then loop through all the csvs, apply the conditional formatting, then save the files as .xls workbooks
Regards
Dave
I have adapted code from http://www.xtremevbtalk.com/showthread.php?t=300278 to conditional format cells Bold if the value is less thean 0.7
Pls run the code, when the 'Open" window appears, navigate to your directory and click on one of the csv files. the code will then loop through all the csvs, apply the conditional formatting, then save the files as .xls workbooks
Regards
Dave
Sub ConvertCSV2XLS()
'code adapted from http://www.xtremevbtalk.com/showthread.php?t=300278
Dim fn As String
Dim i As Long
Dim CSVFiles() As String
Dim myDir As String
fn = Application.GetOpenFilename(filefilter:="CSV Files (*.csv),*.csv")
fn = Dir("*.csv")
myDir = CurDir
ReDim CSVFiles(0)
If fn <> "False" Then
CSVFiles(0) = fn
Do
fn = Dir
If fn <> "" Then
i = i + 1
ReDim Preserve CSVFiles(i)
CSVFiles(i) = fn
Else
Exit Do
End If
Loop While fn <> ""
End If
Application.ScreenUpdating = False
For i = 0 To UBound(CSVFiles)
Workbooks.Open Filename:=myDir & "\" & CSVFiles(i)
Application.DisplayAlerts = False
Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0.7"
Cells.FormatConditions(1).Font.Bold = True
ActiveWorkbook.SaveAs Filename:=myDir & "\" & Replace(CSVFiles(i), ".csv", ".xls"), FileFormat:=xlNormal
ActiveWindow.Close
Next
Application.ScreenUpdating = True
End Sub
ASKER
Dave,
I get the following error message when I run the .vbs script you provided:
Line: 3
Char: 12
Error: Expected end of statement
Code: 800A401
Please advise,
Thank you kindly,
JE
I get the following error message when I run the .vbs script you provided:
Line: 3
Char: 12
Error: Expected end of statement
Code: 800A401
Please advise,
Thank you kindly,
JE
JE
This is Excel VBA - not a Visual Basic script
Cheers
Dave
This is Excel VBA - not a Visual Basic script
Cheers
Dave
ASKER
Whoops,
Thanks.
Okay, now, it is only doing half of the files in the directory and those are done correctly. What other info might you need to troubleshoot?
Thanks,
JE
Thanks.
Okay, now, it is only doing half of the files in the directory and those are done correctly. What other info might you need to troubleshoot?
Thanks,
JE
JE - it's only doing half the csv files?
Cheers
Dave
Cheers
Dave
ASKER
Dave,
Yes,
No matter which csv file I open and insert the macro in, the macro starts with "c12*1.k.s.p.csv"( * =r or p)
and ends with "c14*1.k.s.p.csv" and leaves c5-7 untouched. See attached files for names of files in the directories I tried this on. After it 'finishes' it clears the macro from excel vba editor.
Please advise,
JE
JE1-output.txt
JE2-output.txt
Yes,
No matter which csv file I open and insert the macro in, the macro starts with "c12*1.k.s.p.csv"( * =r or p)
and ends with "c14*1.k.s.p.csv" and leaves c5-7 untouched. See attached files for names of files in the directories I tried this on. After it 'finishes' it clears the macro from excel vba editor.
Please advise,
JE
JE1-output.txt
JE2-output.txt
The code worked on my testing, but I have gone back to my own preferrred way of looping through a file set
Pls change
myDir = "c:\excelfiles"
to suit
Cheers
Dave
Pls change
myDir = "c:\excelfiles"
to suit
Cheers
Dave
Sub UseMyOwn()
myDir = "c:\excelfiles"
myfile = Dir(myDir & "\*.csv")
Application.ScreenUpdating = False
Do While myfile <> ""
Workbooks.Open Filename:=myfile
Application.DisplayAlerts = False
Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0.7"
Cells.FormatConditions(1).Font.Bold = True
ActiveWorkbook.SaveAs Filename:=myDir & "\" & Replace(myfile, ".csv", ".xls"), FileFormat:=xlNormal
ActiveWindow.Close
myfile = Dir
Loop
Application.ScreenUpdating = True
End Sub
ASKER
I tried using this new code with a blank workbook and inserting it into one of the csv files to be operated on. Both ways I get the attached error and highlighted code when I press debug.
Please advise,
Thanks,
JE
error-je-bold-xls.jpg
error-je-bold2-debug-xls.jpg
Please advise,
Thanks,
JE
error-je-bold-xls.jpg
error-je-bold2-debug-xls.jpg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
brettdj:
This one kinda of work, instead of half of the files it now completed the task on 2/3 of them. After it 'finishes' it clears the macro from excel vba editor.
Please advise,
Thanks for your continuing efforts,
JE
This one kinda of work, instead of half of the files it now completed the task on 2/3 of them. After it 'finishes' it clears the macro from excel vba editor.
Please advise,
Thanks for your continuing efforts,
JE
JE,
>After it 'finishes' it clears the macro from excel vba editor.
Are you running the code from one of the CSV files you are operating on? It should be run from an unaffected file.
Can you pls run this code, and then copy and paste the results from the Immediate window in the VBE back into an EE post.
Cheers
Dave
>After it 'finishes' it clears the macro from excel vba editor.
Are you running the code from one of the CSV files you are operating on? It should be run from an unaffected file.
Can you pls run this code, and then copy and paste the results from the Immediate window in the VBE back into an EE post.
Cheers
Dave
ASKER
>It should be run from an unaffected file.
That was my problem
Thanks,
It works splendidly.
Cheers,
JE
That was my problem
Thanks,
It works splendidly.
Cheers,
JE
No probs, thx for the grade :)
Cheers
Dave
Cheers
Dave
You do understand that CSV files are not formatted, right? If you save the files as CSV after the modification,
they will lose the formatting. I think you need to take a shot at more fully describing what you need to do.
Patrick