vba powerpoint formatting % and $

Question

I am having a challenge with how MSOffice deals with number formats.

While I believe this is similar root cause to: Stop Excel from automatically converting certain text values to dates

It is different as this is not a date format and this involves both Excel and PowerPoint with VBA.

I have data that I am pulling out of a dB into CSV files and I am doing a .Replace on certain text markers (e.g. @@ReplaceText@@) in a PPT template. (There is a good post on the site on how to do this I can't seem to locate now)

There is one field I need to deal with which is tracking a metric, this field is text in my dB, but it can contain special characters - specifically $ and %.

e.g. I could see the following values in the CSV file: "increase market share","1234","$10","28%"

I want VBA to treat this all as text, so the % and $ characters are maintained...but... Excel reads the data as a number and keeps the $ or % sign. PowerPoint removes the $ or % sign and converts 28% to 0.28 and $10 to 10.

Per the above question, adding "=""28%""" to the .csv in Excel, will give me that exact literal text in PowerPoint.

Adding a preceding space or ' character works in forcing Excel to read the data as text string. But PowerPoint ignores it and behaves same as above. Eg 28% to 0.28.

I tried using FORMAT as below, but because the data is variable, I don't know which case to apply.

sCurrentText = Format(sCurrentText, "$#") or sCurrentText = Format(sCurrentText, "0.0%")

If statements don't work because the $ or % are not present in what VBA sees (e.g the $ or % character is already gone)

If sCurrentText Like "*$*" Then or If sCurrentText Like "*%" Then

So my question is how do I force VBA to take what is in the CSV file as text and ignore processing $ or % as special characters and just maintain them in the CSV?

I've expanded my original question to provide more detail to my problem as how these characters ($ and %) are dealt with by PowerPoint as well as Excel. — discoStew, Apr 28 '16 at 17:48

score 0 · Answer 1 · answered Apr 27 '16 at 17:05

You didn't specify what exactly you want to do with the data in the CSV file, but I've assumed you're trying to open the file in VBA.

If you are opening the CSV file using OpenText (as below) then Excel will automatically parse the data in the format it sees fit. eg:

Workbooks.OpenText fileName:="directory", DataType:=xlDelimited, Comma:=True

You can use a different method to open the CSV file if you want VBA to handle the data as just text which you can use as you see fit.

Sub OpenCSVFile()
Dim ff As Long, iRow As Long, iCol As Long
Dim FilePath As String
Dim FileBuffer As String            'Entire CSV file as one string
Dim LineSeparatedFile() As String   'Array of data separated into lines
Dim LineData() As String            'Array of comma separated values for that line

ff = FreeFile
Open FilePath For Binary Access Read As #ff
FileBuffer = Space$(LOF(ff))
Get #ff, , FileBuffer
Close #ff
LineSeparatedFile = Split(txtBuffer, vbCrLf)

For iRow = 0 To UBound(LineSeparatedFile)
    LineData = Split(LineSeparatedFile(i), ",")
    For iCol = 0 To UBound(LineData)
        'Code to do something with each entry.
        'Eg. print to cell as text
        ThisWorkbook.Sheets(1).Cells(iRow + 1, iCol + 1).NumberFormat = "@"
        ThisWorkbook.Sheets(1).Cells(iRow + 1, iCol + 1).Value = LineData(iCol)
    Next iCol
Next iRow
End Sub

This is a good utility that does take the data and put it as text in Excel. However, as stated above in my corrected question, PowerPoint handles this different and ignores the "read as text" marker that Excel accepts. — discoStew, Apr 28 '16 at 18:15

vba powerpoint formatting % and $

1 Answers1