Import csv into excel - 2007 problem

[kitackers]

My work have recently upgraded from excel 2003 to 2007 (very progressive!!) and, amongst other problems, I had a macro that imported 2 csv files into an excel workbook that worked fine in 2003 but won't in 2007;

When I run;

Sub Import()

Dim oCSV As Workbook
Set oCSV = Workbooks.Open("F:\WebCCD\ICCQuoteData.csv")
oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
oCSV.Close False
Set oCSV = Workbooks.Open("F:\WebCCD\ICCSaleData.csv")
oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)
oCSV.Close False
Set oCSV = Nothing

End Sub

I get a runtime error 1004 at the;

oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1)

line.

Help please!!??

 

[SkipVought]

Hi,

First, this is not an IMPORT. An IMPORT would actually be better than OPENING a text file in Excel. But be as it may,

Sub Import()
   
   Dim oCSV As Workbook, lRow as long
    Set oCSV = Workbooks.Open("F:\WebCCD\ICCQuoteData.csv")
    
    if ThisWorkbook.Worksheets(1).[A1].currentregion.rows.count = 1 then
      lRow = 2
    else
      lRow = ThisWorkbook.Worksheets(1).[A1].end(xldown).offset(1).Row
    end if
 
    oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1).Cells(lRow, "A")

    oCSV.Close False
        Set oCSV = Workbooks.Open("F:\WebCCD\ICCSaleData.csv")
     if ThisWorkbook.Worksheets(1).[A1].currentregion.rows.count = 1 then
      lRow = 2
    else
      lRow = ThisWorkbook.Worksheets(1).[A1].end(xldown).offset(1).Row
    end if
 
    oCSV.Sheets(1).Copy ThisWorkbook.Worksheets(1).Cells(lRow, "A")

    oCSV.Close False
    Set oCSV = Nothing
End Sub

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