Retrieving the characters lost

[Alogon]

Hello everybody.

When I convert a txtfile into xlsfile, a cell that must contain the text "-
John" (ignore quotation marks) become a formula "=- John" (ignore quotation
marks). In the worksheet, Excel displays the value "#Name?" (ignore quotation
marks) at that cell and displays "=- John" (ignore quotation marks) at the
edition bar.

Using VBA, how can I get back the original characters "- John" ?

Thank you in advance.

 

[Gary''s Student]

When you import the .txt file into Excel (wether manually or
programmatically), just specify that the field is Text rather than General:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/21/2009 by James Ravenswood
'

'
Range("B7").Select
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Owner\Desktop\dummy.txt",
Destination:=Range( _
"A1"))
.Name = "dummy"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Alogon]

Just on target, Gary.

Congratulations from Brazil.

Thank you very much.

Nathan

 

[Jake]

Error Importing Text File Into Excel (getting #Name for some values)

If using VBA you will want to use the property of the QueryTables.Add method
shown below :

..TextFileColumnDataTypes = Array(1, 1, 1) 'for incoming data of 3 columns

each value in the array represents the datatype to assign to the incoming
data field. It seems you are getting the #Name error because you have not
specified the data types of the incoming text columns and they are all
defaulting to 'General' which means Excel is treating the incoming data as
numbers when infact it should be treated as text.

Regards,

Jake

http://www.Excel-Expert.co.uk
http://www.OfficeSoftworks.com
htto://www.OfficeSoftworks.co.uk
http://www.MacroExpert.co.uk
http://www.ExcelProgrammer.co.uk
http://www.MSExcelExpert.com
http://www.AccessExpert.co.uk