Import data in Excel. (Data-> Import external data)
Sub macro1 ()
'
'Macro1 macro
'Test for sunhaifeng
'Sunhf@cn.ibm.com
'
If fileexists ("C: \ bookfortestdata.csv") then
Kill "C: \ bookfortestdata.csv"
End if
Workbooks. Add
With activesheet. querytables. Add (connection: = "text; E: \ test.csv", destination _
: = Range ("A1 "))
. Name = "test"
. Fieldnames = true
. Rownumbers = false
. Filladjacentformulas = false
. Preserveformatting = true
. Refreshonfileopen = false
. Refreshstyle = xlinsertdeletecells
. Savepassword = false
. Savedata = true
. Adjustcolumnwidth = true
. Refreshperiod = 0
. Textfilepromptonrefresh = false
. Textfileplatform = 932
'Here, the code of my CVS file is shift-JIS. If you need another code, you can record it yourself.
. Textfilestartrow = 1
. Textfileparsetype = xldelimited
. Textfiletextqualifier = xltextqualifierdoublequote
. Textfileconsecutivedelimiter = false
. Textfiletabdelimiter = true
. Textfilesemicolondelimiter = false
. Textfilecommadelimiter = true
. Textfilespacedelimiter = false
. Textfilecolumndatatypes = array (1, 1, 1, 1)
. Textfiletrailingminusnumbers = true
. Refresh backgroundquery: = false
End
Activeworkbook. saveas filename: = "C: \ bookfortestdata.csv", fileformat: = xlcsv ,_
Createbackup: = false
Activeworkbook. Close savechanges: = true
Dim mychar
Dim strtext as string
Dim filenum as integer
Dim STR () as string
Dim count as integer
Dim count1 as integer
Count = 1
Open "C: \ bookfortestdata.csv" for input as #1
Do while not EOF (1)
Line input #1, strtext
STR = Split (strtext ,",")
Count1 = ubound (STR)
For I = 0 to count1
Worksheets (1). cells (I + 1, count). value = STR (I)
Next I
Count = count + 1
Loop
Close #1
If fileexists ("C: \ bookfortestdata.csv") then
Kill "C: \ bookfortestdata.csv"
End if
End sub
private function fileexists (fname) as Boolean
dim X as string
X = Dir (fname)
If x <> "" Then fileexists = true _
else fileexists = false
end function