import CSV into excel via VBA

[DoctorGonzo]

Hi All,

I'm trying to write a macro that will open a CSV file, delimit it, copies the data and pastes it in a specific sheet of a specific workbook.

I've tried this several ways without success but here is the
most successful attempt yet -

----
Sub cutpaste()
'
' cutpaste Macro
'

'
Worksheets("Lassie_Report").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Activate

Application.CutCopyMode = False
Workbooks.OpenText Filename:= _
"D:\lassie data files\lassie_time.csv.txt", Origin:= _
xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote _
, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=True _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1),
Array(8, 1), Array(9, 1)), _
TrailingMinusNumbers:=True
Range("A2:I500").Select
Selection.Copy
Windows("LASSIE Agent log we 29.05.05.xls").Activate
ActiveSheet.Paste
Range("A1").Select
End Sub
--------------------------------------------------

The VBA code above does work but 2 of the columns have dates in them and
Excel imports them in USA format (MM/DD/YYYY). To correct this it
requires the following line -

'Local:=True' command

but I've tried everything I can think of with out success.

Can anyone help?

Thanks!! Gonzo

 

[Loomah]

Hi
You can open a csv file directly without having to import it

ie
workbooks.open "C:\MyFolder\MyFile.csv"

should do the trick of opening the file then do your copying etc.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[macropod]

Hi Gonzo,

If you really want to do this by code, check out the MSKB article at:

http://support.microsoft.com/default.aspx?scid=kb;en-us;120596

Although the code there imports each row of the text file into column A, it is fairly trivial to add a text-to-columns function for each row as you go, or to do it for the whole sheet at the end.

Cheers