CODEWITHSUNDEEP
excelvbacsvxlsm
JossephA
JossephA

Reputation: 1

VBA - The code to complete 0 is missing after converting a file to csv format

I have 2 commands to add 0 to number (8 digits, 9 digits required), and 0 addition to cell phone numbers (9 digits beginning with 5 digits need to be 10 digits):

1.Add 0 to 8 digits :

Sub Add_Zeros()
    Selection.NumberFormat = "@"
    For Each CL In Selection.Cells
        If CL <> "" Then CL.Value = Application.Rept("0", (9 - Len(CL))) & CL
    Next
End Sub

2.Add 0 to 9 digits :

Sub Add_Zeros()
    Selection.NumberFormat = "@"
    For Each CL In Selection.Cells
        If CL <> "" Then CL.Value = Application.Rept("0", (10 - Len(CL))) & CL
    Next
End Sub

**1.**First, can I upgrade these codes and combine them as follows?:

A.The first condition: If there are only 8 digits you will add 0 at the beginning (Finally there should be 9 digits)

B.The second condition: If there are 9 digits and the first digit on the left is 5 you add 0 at the beginning (Finally there should be 10 digits)

C.Something else how do I return the command back (ie before the change) to cancel? Do you have a way to insert this into another code?

**2.**In addition, I'm looking for a way to run a code (if it's a VBA or something else) that will convert my Xltm file (after running all the commands) to a .csv file and save me the 0 (zeros) I add.

That the final code for converting the file would be built like this:

A. Make Xltm for csv and keep me the 0 (zero) according to the following law (which is the way to keep the zeros):

1.Click on Data Tab | From Text

2.Select the Csv from the file slection dialog box

3.In text Import Wizard (STEP 1), select 'Delimited' and hit next.

4.In text Import Wizard (STEP 2), select 'Comma' and hit next.

5.In text Import Wizard (STEP 3), select all columns and click on 'text' in the 'column data format'

6.Click finish

7.Select the cell where you want to import the data and click 'ok'

B. After that save me the file as Xlsx.

*C.If you can add another action in the code that also:

Change the extension of the Xlsx again to csv, it is excellent (not save as csv but only change the extension of the file)

The file is attached Link :

Work file

(check that extension Xltm)

Thanks in advance,

Blessed be from heaven

Upvotes: 0

Views: 215

Answers (1)

Sam
Sam

Reputation: 5721

Regarding question 1:

I'm not following your explanation fully, but this will hopefully get you started:

Sub Add_Zeros()
    Selection.NumberFormat = "@"
    For Each CL In Selection.Cells
        If CL <> "" Then CL.Value = ZeroPad(CL) ' Move the logic to a function for better readability
    Next
End Sub


Function ZeroPad(Value)
    ZeroPad = Value ' Make sure you have a return value
    If Len(Value) <= 8 Then 'Pad with zeroes up to 9 chars
        ZeroPad = Right("000000000" & Value, 9)
    End If
    If Left(Value, "5") Then ' I didn't understand your question here. Just guessing
        ZeroPad = Right("000000000" & Value, 10)
    End If
End Function

Upvotes: 1

Related Questions