Solved

Find cells and bold their text in many csv files

Posted on 2008-10-09
18
695 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:justearth
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 22684674
JE,

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
0
 

Author Comment

by:justearth
ID: 22684691
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 22684947
I believe you mean import the CSV file into Excel, bold them as needed, and then save the Excel file.
Patrick(ab)
0
 

Author Comment

by:justearth
ID: 22684983
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 22685086
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.
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22685835
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


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

Open in new window

0
 

Author Comment

by:justearth
ID: 22687547
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22689718
JE

This is Excel VBA - not a Visual Basic script

Cheers

Dave
0
 

Author Comment

by:justearth
ID: 22690276
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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Dave Brett
ID: 22690298
JE -  it's only doing half the csv files?

Cheers

Dave
0
 

Author Comment

by:justearth
ID: 22690390
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22690514
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
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

Open in new window

0
 

Author Comment

by:justearth
ID: 22690694
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
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 22690855
my bad .. copied the wrong version of my code
 Sub UseMyOwn()

    mydir = "c:\excelfiles"

    myfile = Dir(mydir & "\*.csv")

    Application.ScreenUpdating = False
 

    Do While myfile <> ""

        Workbooks.Open (mydir & "\" & 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

Open in new window

0
 

Author Comment

by:justearth
ID: 22692451
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
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22694598
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
0
 

Author Comment

by:justearth
ID: 22695537
>It should be run from an unaffected file.

That was my problem

Thanks,
It works splendidly.

Cheers,
JE
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 22695557
No probs, thx for the grade :)

Cheers

Dave
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
DBF to ... Converter 5 49
How to generate a random directed graph 5 37
File not loading into PowerPivot 4 9
Using 'range' instead of 'select' in macro 8 24
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now