CODEWITHSUNDEEP
excelvba
VanGoghComplex
VanGoghComplex

Reputation: 3

Excel to Text VBA precedes with question mark - how to fix?

I'm very new to VBA in Excel. I'm using this code I cobbled together from example snippets online to convert a column of cells in Excel to a text file:

Private Sub CommandButton1_Click()
Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
Dim FName As String
Dim FPath As String

Set fsT = CreateObject("ADODB.Stream"): 'Create Stream object
fsT.Type = 2: 'Specify stream type – we want To save text/string data.
fsT.Charset = "utf-8": 'Specify charset For the source text data.

FPath = "C:\WHIT\ParamGen"
FName = Sheets("Sheet1").Range("b49").Text

myFile = FPath & "\" & FName

Set rng = Range("B2: B42 ")

Open myFile For Output As #1

For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count

cellValue = rng.Cells(i, j).Value

If j = rng.Columns.Count Then
    Print #1, cellValue
Else
    Print #1, cellValue,
End If

    Next j
Next i

Close #1

End Sub

The problem is that the first cell in my Excel file contains this text:

@!=1

...and it shows up in the generated text file like this:

?@!=1

Everything else in the excel file gets written to the text file without issue, but that question mark messes up the import function in the software this file is being generated for.

Any ideas on getting this question mark to disappear?

Upvotes: 0

Views: 1909

Answers (3)

VanGoghComplex
VanGoghComplex

Reputation: 3

I found a solution. Excel was treating the @!=1 in the first cell as a function, but it wasn't a functional function. My best guess is that it was throwing an invisible character in there as it parsed it into a text file. Overwriting the offending cell with '@!=1 did the trick.

Upvotes: 0

GSD
GSD

Reputation: 1252

I ran this code with the first cell containing @!=1 and it wrote correctly to the text file as @!=1 (no ? added). Did you check to see if cell B2 contains any non-printable characters?

Upvotes: 0

Cyril
Cyril

Reputation: 6829

Have you tried removing the "?" with code in the file, such as:

If left(cellvalue,1)="?" then 
    application.substitute(cellvalue,"?","") 
end if

Upvotes: 1

Related Questions