RE: [ExcelVBA] Opening a CSV file and populating cells just like Excel does

 

Hi Steve

Try Data/Get external data/From Text. (that is based on Excel 2010 - can't
recall how to do it in obsolete versions.)

If this does what you want, record a macro of doing it.

Mine recorded this

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\David\Documents\Business\Clients\Etheridge\Staff
List.csv", _
Destination:=Range("$A$1"))
.Name = "Staff List"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

Best Regards
David Grugeon
Excel VBA Group Moderator

-----Original Message-----
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf
Of noskosteve
Sent: Wednesday, 6 June 2012 1:48 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Opening a CSV file and populating cells just like Excel
does

I wanna' be just like my daddy...

I want to open a CSV file into an already open Sheet the same way Excel
opens it as a new, complete Workbook.

It is a simple CSV file with records (lines) that have either text or
numbers in the fields (with commas between, of course) and CR at the end.
There are no quotes around the strings (they aren't Write strings).
Typical records:
2,Anthem,442.525000,,5.000000,Tone,114.8,,,,,,,
3,Bank One,146.820000,-,0.600000,Tone,159.8,,,,,,,

As you see, there can even be blank fields, thus blank cells.

I already can and do open tab delimited files, but I use Input # and
normally get the individual fields into individual variables by manually
(code) parsing the resulting whole record (line) strings.

However, I want to open the file at the users command (a button push) and
have the values put into adjacent cells just the way it happens when you
open a CSV file using Excel's Open. [Recording this operation shows that it
simply opens another complete Workbook.]

Help says that Line Input # is for reading Print # lines.
and that
Input # is for reading Write # lines...
but that the fields are brought into individual variables.

I don't know if this can be done automatically, or if I must bite the bullet
and use code to parse the values and put them in cells.

I didn't know where to start looking.
I spoze' I could do a regular Open a CSV Workbook, copy to the desired
Sheet, then close the CSV Workbook, but it would seem since Excel does it,
VBA should be able to make that happen as well.
Ya' know . something like "Application.OpenCSVFile = xlJustLikeExcel
Filename = "whatever" Destination=Sheet4"
(;-)
--
73, Steve

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 4

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___