CSV Import Issue (Newbie)

[John V]

The routine below is used to import the contents of several files and paste
those contents into a worksheet. All worked fine until the source files
changed from tab delimited to CSV files (nothing I can do about the change).
I changed two lines of code to recognize .csv file instead of a .txt file,
then imported.

The resulting destination worksheet has unexpected results. 1) All text
cells are now preceeded by a single quote. 2) Any commas embedded in a source
cell cause the text to be parsed into two or more destination cells, and 3)
non printing characters remain in the destination cells. None of these
phenomena appear to occur when I simply open the csv file in Excel, but it is
the copy/paste routine below that seems to be the culprit (I think).

Any help much appreciated.
John

Code starts here:

Sub Firstattempt()
Dim rng, rng1 As Range
Dim FNames(1 To 100, 1 To 2) As Variant
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim FN As String ' For File Name
Dim ThisRow As Long
Dim MyFileLocation As String
MyFileLocation = ThisWorkbook.Path & "\*.csv"
' Changed from Tab to Comma delimited 9/06
FN = Dir(MyFileLocation)
FNum = 0
Do Until FN = ""
FNum = FNum + 1
Workbooks.OpenText Filename:=ThisWorkbook.Path & "\" & FN, _
DataType:=xlDelimited, Comma:=True
' Changed from Tab to Comma delimited 9/06
ActiveCell.CurrentRegion.Copy

Workbooks(FN).Close SaveChanges:=False
Worksheets("Raw Data").Activate
Set rng = Worksheets("Raw Data").Cells(Rows.Count, 1).End(xlUp)(2)
Set rng1 = rng.Offset(1, 0)

If FNum = 1 Then
Set rng = Range("A1")
Set rng1 = Range("a2")
End If

'Range("myRange").Columns(1).Value = 0
rng.Activate
ActiveCell.PasteSpecial
If FNum <> 1 Then
rng.Activate
ActiveCell.EntireRow.Delete
End If
rng1.Activate
FNames(FNum, 1) = Left(FN, Len(FN) - 4)
Do While True
If ActiveCell = "" Then Exit Do
ActiveCell.Value = FNames(FNum, 1)
ActiveCell.Offset(1, 0).Activate
FNames(FNum, 2) = FNames(FNum, 2) + 1
Loop

FN = Dir
Loop
Worksheets("Files Imported").Activate
Range("a1:b" & FNum).Value = FNames
Application.ScreenUpdating = True

End Sub

 

[John V]

Phenomenon 3) above was in the source document already, but other results are
confirmed.

 

[Michael Bauer [MVP - Outlook]]

Am Thu, 28 Sep 2006 14:43:02 -0700 schrieb John V:

John, your chance for a good answer is better in an Excel group.

 

[John V]

LOL! My bad. Thanks for being so kind. One might have said, "Hey goober!
You're in the wrong place! No wonder your code doesn't work."