Export as CSV file!

[Adeel1]

Hello All

Hello All
any one can add below part in code.

when i run code i want one dialog box for pre number for column A, which number i will paste/write in that will become first digit of column A data for example i write 1 than 1 will be leading number of each cell if i leave blank than only column A data will copy as it is(only will remove dashes and spaces already in code) & total count of cell will be 11.
14311234560
14311234561
14311234562

second in column C sam file if there is only three letter than its will export with leading 0 only in sample like 0555, 0495

here exported file sample!

grv.csv Sam file sample!​ sam with code.xlsm​

Option Explicit

Sub test()
    Dim cn As Object, rs As Object, x, i As Long, temp As String, rng As Range
    Application.ScreenUpdating = False
    With Sheets("sheet1").Cells(1).CurrentRegion
        Set rng = .Columns(.Columns.Count + 1)
        Columns("R:R").NumberFormat = "@"
    End With
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    With cn
        .Provider = "Microsoft.Ace.OLEDB.12.0"
        .Properties("Extended Properties") = "Excel 12.0;HDR=Yes;"
        .Open ThisWorkbook.FullName
    End With
    rs.Open "Select Null As `Cutomer ID`, Format( Replace( Replace(`nums`,' ',''),'-',''),'000000000000') " & _
            "As `Mobile Number`,Null As `Customer Name`,Null As `CNIC`,Null As `Email Address`, " & _
            "Null As `Package Plan`,Null As `Bolton1`,Null As `Buldles`,'Send For Activation' As " & _
            "`Connection Status`,Null As `Access Level`,`CL` As `Credit Limit`,Null As `Natureof Sales`, " & _
            "'Waiver' As `Deposit Amount/Waiver Code`,`Action` As `Special Line Level Info`,`Num Price` As " & _
            "`Special Number Charge Type`,Null As `Hand Set`,'Gift Voucher' As `Special Number Charges`, " & _
            "`Imsi` As `ICCID`, Null As `Verified`, Null As `Comments`, Null As `Sales Feedback`," & _
            "Null As `SPOCMSISDN`,`ID` As`Sales ID` From `Sheet1$`;", cn, 3
    x = rs.GetString(2, , ",", vbCrLf)
    For i = 0 To rs.Fields.Count - 1
        temp = temp & "," & rs.Fields(i).Name
    Next
    Open ThisWorkbook.Path & "\grv.csv" For Output As #1
        Print #1, Mid$(temp, 2) & vbCrLf & x
    Close #1
    rng.Clear
    Application.ScreenUpdating = True
    Set cn = Nothing: Set rs = Nothing
End Sub

Adeel

 

[Adeel1]

any one can help! please