Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"

Tanquen

Board Regular
Joined
Dec 14, 2017
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Right now I'm opening the CSV file in Notepad just to save it with ANSI encoding so the "°F" is not changed to "°F". It would be nice if there was a way to do this when bringing the CSV in with VBA.

Also, the CSV includes the units with the value in one cell. I have script to split them up but Excels auto format removes the percent units "%" and changed the value to "0.75" vs. "75".

Some something like "480 V" is left alone while "75 %" has text removed and the value changed. Is there no way to stop Excel from change values in cells?

VBA Code:
Sub Import_BMS_CSV()
    Dim ws As Worksheet
    Dim csvFilePath As Variant
    Dim lastRow As Long
    Dim rngF As Range, rngH As Range
    Dim cell As Range
    Dim fileTimestamp As Date
    Dim formattedTimestamp As String

    ' Set the worksheet where you want to paste the data
    Set ws = ThisWorkbook.Sheets("BMS")

    ' Prompt the user to select the CSV file
    csvFilePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File")

    ' Check if the user selected a file or canceled the dialog
    If csvFilePath = False Then
        MsgBox "No file selected. Import canceled.", vbExclamation
        Exit Sub
    End If

    ' Get the file timestamp
    fileTimestamp = FileDateTime(csvFilePath)

    ' Round the timestamp to the nearest minute
    fileTimestamp = Round(fileTimestamp * 1440, 0) / 1440

    ' Format the timestamp to "2024-08-06T15:50:00.000Z"
    formattedTimestamp = Format(fileTimestamp, "yyyy-mm-dd\THH:MM:SS") & ".000Z"

    ' Place the formatted timestamp in cell J2
    ws.Range("J2").Value = formattedTimestamp


    ' Define the range to clear (A9:O and beyond)
    ws.Range("A9:O" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).ClearContents

    ' Import the CSV file starting at row 9
    With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A9"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .Refresh
    End With

    ' Make row 9 bold
    ws.Rows(9).Font.Bold = True

    ' Find the last row in the imported data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Set the range for columns F and H from row 10 to the last row
    Set rngF = ws.Range("F10:F" & lastRow)
    Set rngH = ws.Range("H10:H" & lastRow)

    ' Loop through each cell in column F and replace "DCAIG" or "DCA" with "MCP"
    For Each cell In rngF
        If InStr(cell.Value, "DCAIG") > 0 Or InStr(cell.Value, "DCA") > 0 Then
            cell.Value = Replace(cell.Value, "DCAIG", "MCP")
            cell.Value = Replace(cell.Value, "DCA", "MCP")
        End If
    Next cell

    ' Loop through each cell in column H and replace "DCAIG" or "DCA" with "MCP"
    For Each cell In rngH
        If InStr(cell.Value, "DCAIG") > 0 Or InStr(cell.Value, "DCA") > 0 Then
            cell.Value = Replace(cell.Value, "DCAIG", "MCP")
            cell.Value = Replace(cell.Value, "DCA", "MCP")
        End If
    Next cell
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
I've add this code at the end to repair the cells after import but it just makes a mess as now the next step will not find the value units in those cells and so on. I guess I could add it back in for these and then...
VBA Code:
    ' Find the last row in column I
    lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
    
    ' Loop through each cell in column I
    For Each cell In ws.Range("I2:I" & lastRow) ' Adjust the starting row if needed
        If cell.NumberFormat = "0%" Or cell.NumberFormat = "0.00%" Then
            ' Convert the percentage to a number and remove the percentage format
            cell.Value = cell.Value * 100
            cell.NumberFormat = "General"
        End If
    Next cell
 
Upvote 0

Forum statistics

Threads
1,223,375
Messages
6,171,707
Members
452,418
Latest member
kennettz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top