Problem importing multiple CSVs into Excel

[MarcLodge]

Hi,
Vista, Excel 2007.
I wish to copy a number of CSV files, all of a particular name, into a worksheet. The data in the CSVs looks like this:

"EXP008 ","LS ","27/10", -402
"EXP008 ","LS ","28/10", -402
"EXP008 ","LS ","29/10", -22
"EXP008 ","LS ","30/10", -22
"EXP008 ","LS ","02/11", 5
"EXP008 ","LS ","03/11", 5
"EXP008 ","LS ","04/11", 5
"EXP008 ","LS ","05/11", 5
"EXP008 ","LS ","06/11", 5
"EXP008 ","LS ","09/11", 5

When I open the CSVs on their own, they are fine, but my macro when bringing them into the worksheet recognises some of the 3rd columns as dates and changes them, and others not as dates and doesn't. So I get this:

EXP008 LS 27/10 -402
EXP008 LS 28/10 -402
EXP008 LS 29/10 -22
EXP008 LS 30/10 -22
EXP008 LS 11-Feb 5
EXP008 LS 11-Mar 5
EXP008 LS 11-Apr 5
EXP008 LS 11-May 5
EXP008 LS 11-Jun 5
EXP008 LS 11-Sep 5

I don't wish it to recognise any of the data as a date, and to just leave it be. My code so far is:

Sub TEST()
Set cell_to_paste_next_dataset = Cells(1, 1)
Set active_workbook = ActiveWorkbook

Application.DisplayAlerts = False

'delete and re add sheet so that data is always pasted at start
Worksheets("EXP008 Input Data").Delete
Worksheets.Add().Name = "EXP008 Input Data"
Set active_sheet = Worksheets("EXP008 Input Data")
Range("A1").Select

'OR YOU CAN SPECIFY A CUSTOM PATH
File_Path = "H:\Exposure Reporting Spreadsheet"

strName = Dir(File_Path & "\" & "EXP008*.CSV")  'CHANGE FILE EXTENSION TO OTHER EXCEL BASED EXTENSION IF YOU WANT

Do While strName <> vbNullString

    If active_workbook.Name <> strName And strName <> "" Then
        Workbooks.OpenText Filename:=File_Path & "\" & strName, startrow:=1, _
            DataType:=Excel.XlTextParsingType.xlDelimited, _
            TextQualifier:=Excel.XlTextQualifier.xlTextQualifierDoubleQuote, _
            Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2))
        Set dataset_workbook = ActiveWorkbook
        Range(ActiveCell.SpecialCells(xlLastCell), Cells(1)).Copy
        active_sheet.Activate
        Cells(ActiveCell.SpecialCells(xlLastCell).Row + 1, 1).Select
        ActiveSheet.Paste
        dataset_workbook.Close
    End If
    strName = Dir
Loop

'Now deselect the last file's range
Sheets("EXP008 Input Data").Select
Range("A1").Select

End Sub

Can anybody help out and let me know where I'm going wrong.

Thanks in advance.

Marc

 

[SkipVought]

Hi,

When I open the CSVs on their own...

THERE is your problem.

Rather than using the OPEN method, IMPORT each using Data > Import External Data > Import Data...

There you can specify each field's data characteristics and avoid the date coinversions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sidetech]

TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone

 

[MarcLodge]

Skip, are you saying that the Workbooks.Open method will not work?

sidetech, I changed the code to read

Workbooks.OpenText Filename:=File_Path & "\" & strName, startrow:=1, _            DataType:=Excel.XlTextParsingType.xlDelimited, _            TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, _            Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2))

and it made not a blind bit of difference.

Marc

 

[MarcLodge]

Hi all,
found the answer and thought I'd post back in case anybody has this problem again in the future. If you add
Local:=True
to the Workbooks.Open statement, it leaves the field in the local date format.

Marc