check each cell in a column and convert to text

[gauti14]

Hi
I have in column C a list of numbers ranging from 100 to 2000, for all 3 digit numbers i want them to be displayed as 0100 (with a 0 added at the beginning)
as the format is important for other macros to work well.. Any help is appreciated thanks!

Also, the macro has to be run over an entire folder,i currently have this code that converts csv files to xls, if you can add to this code to take care of number conversion , that would be great.

Sub CSVToXls()
Dim myfile, mypath, workfile
Dim fso As Object
Dim folderName As String
Set fso = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = "Select A Folder!"
   .ButtonName = "Confirm"
   If .Show = -1 Then
      folderName = .SelectedItems(1)
      
   Else
      MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
      Exit Sub
   End If
End With
    

If folderName = "" Then
MsgBox "You Didn't Select A Folder."
Else

End If

Application.DisplayAlerts = False
myfile = ActiveWorkbook.Name
mypath = folderName & "\"
workfile = Dir(mypath & "*.CSV")

Do While workfile <> ""
   Application.StatusBar = "Now working on " & workfile
   Workbooks.Open Filename:=mypath & workfile


    ActiveWorkbook.SaveAs Filename:=mypath & _
      Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=xlNormal
   ActiveWorkbook.Close
   Windows(myfile).Activate
   workfile = Dir()
Loop

Application.StatusBar = False

End Sub

[gauti14]

Also, in the above code, i get csv to xls conversion but i have both files in my folder, is there a way to delte the original csv files?

[JBeaucaire]

To "Format" a column with a 4 digit display is pretty easy, it's the kind of thing you could turn on the macro recorder and record yourself doing that manually, then slip the code snippet you pull from the recorded macro into your other macros(s) as needed.

The KILL command can be used to delete a file, since you already have the path and filename, you're almost there.

See if you can work those two things out from these two hints. Post back with what you're tried if what you come up with doesn't work.

[gauti14]

i got this:

Sub CSVToXls()


    Dim myfile, mypath, workfile
    Dim fso As Object
    Dim folderName As String, FiletoDelete As String
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Application.ScreenUpdating = False
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select A Folder!"
        .ButtonName = "Confirm"
            If .Show = -1 Then
                folderName = .SelectedItems(1)
            Else
                MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
                Exit Sub
            End If
    End With
    
    If folderName = "" Then
        MsgBox "You Didn't Select A Folder."
    End If


    Application.DisplayAlerts = False
    myfile = ActiveWorkbook.Name
    mypath = folderName & "\"
    workfile = Dir(mypath & "*.CSV")
    
    Do While workfile <> ""
        Application.StatusBar = "Now working on " & workfile
        Workbooks.Open Filename:=mypath & workfile
        Columns("C").NumberFormat = "0000"


        ActiveWorkbook.SaveAs Filename:=mypath & _
        Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4), FileFormat:=xlNormal
        ActiveWorkbook.Close
        
        FiletoDelete = mypath & workfile
        On Error Resume Next
        Kill FiletoDelete
        On Error GoTo 0
        
        Windows(myfile).Activate
        workfile = Dir()
    Loop


 End Sub

and it works :D thanks!!
but when i proceed to next step of my task copying the cell value to be used somewhere else,again copies the 3 digit number...can w e convert this to text

[JBeaucaire]

You said you wanted to FORMAT the column C to display 4 digits, which I'm sure your highlighted code actually does. But it's just a display trick, the value in the cell is still 3 digits.

You can perform the same "trick" on the fly in your other code to take the value in the cell and "format" it as you use it elsewhere. For instance, using a single sample value in C2:

Dim MyCstr as String

MyCstr = Format(Range("C2").Value, "0000")

'now you could use MyCstr, it's a text string

[gauti14]

I tried something like this:

 For Each c In Cells(lr + 2, 1).CurrentRegion.Offset(1)
        If Application.CountIf(.Range("C1:C" & lr), c.Value) > 1 Then
            Sheets.Add after:=Sheets(ThisWorkbook.Sheets.Count)
            c.Value = Format(c.Value, "0000")
            ActiveSheet.Name = c.Value
            .Range("A1:J" & lr).AutoFilter 3, c.Value
            .Range("A1:J" & lr).Copy ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2)
            .AutoFilterMode = False
        End If
    Next
    .Cells(lr + 2, 1).CurrentRegion.ClearContents
End If

Still having same issue..can u correct me if somethings wrong

[JBeaucaire]

After you have converted the column C itself to text, I'm thinking the code above would work.

Columns(3).NumberFormat = "@"

[gauti14]

Great this works..but once i do this @ conversion , the 0000 formatting is lost in the original sheer

[JBeaucaire]

That is correct. But your macro will be able to do what it needs to do.