[RESOLVED] Import CSV into existing Excel Worksheet with VBA

[campster]

I should know how to do this but haven't played in Excel for some time. I'm trying to open a CSV file and copy the contents into a worksheet in my current workbook. I'm having two problems.

The first is the copy and paste. I have two methods that work (kind of). I can write each cell one at a time but this is very slow. I can also do a copy and paste but I'm having to hard code the range. I've found the last column but not sure how to use that number in the range. Also when the code tries to close the CSV file after the copy, I get a prompt because of the data stored on the clipboard.

The second problem is reading in multple CSV files. The code below "works" for one file but throws an error if I try to open a second file. It throws the error on the line that tries to open the file. (run-time error '1004' Application-defined or object-defined error)

I'm sure that I'm over complicating this but can anyone point me in the right direction?

Private Sub btnImportBase_Click()
 
Dim oXLApp As Excel.Application
Dim oXLBookTemp As Excel.Workbook
Dim oXLBookCurrent As Excel.Workbook
Dim oXLSheetControl As Excel.Worksheet
Dim oXLSheetBase As Excel.Worksheet
Dim oXLSheetTemp As Excel.Worksheet
Dim iBase As Integer
Dim sBase As String
Dim aBase(5) As String
Dim iLastRow As Integer
Dim iLastCol As Integer
Dim iCol As Integer
Dim iRow As Integer
 
 
aBase(1) = "Base1"
aBase(2) = "Base2"
aBase(3) = "Base3"
aBase(4) = "Base4"
aBase(5) = "Base5"
 
Set oXLBookCurrent = ThisWorkbook
Set oXLSheetControl = oXLBookCurrent.Worksheets("Control")
 
Set oXLApp = New Excel.Application
        oXLApp.Visible = True   'Visible for debug only
 
For iBase = 1 To 5
    sBase = oXLSheetControl.Cells(8 + iBase, 2).Value  'Contains path of file to open
    If Not sBase = "" Then   'If path exists
            
        Set oXLBookTemp = oXLApp.Workbooks.Open(sBase)  'Open CSV file
        Set oXLSheetTemp = oXLBookTemp.Worksheets(1)    'Define worksheet
        
        'define worksheet in current book based on index iBase
        Set oXLSheetBase = oXLBookCurrent.Worksheets(aBase(iBase))
 
        ' find last used row and column
        iLastRow = oXLSheetTemp.Cells(Application.Rows.Count, 1).End(xlUp).Row
        iLastCol = oXLSheetTemp.Cells(1, Application.Columns.Count).End(xlToLeft).Column
        
        
        'This works but is VERY slow
        For iRow = 1 To iLastRow
          For iCol = 1 To iLastCol
            oXLSheetBase.Cells(iRow, iCol) = oXLSheetTemp.Cells(iRow, iCol)
          Next iCol
        Next iRow
        
        'This also works but I have to hard code the range
        oXLSheetTemp.Range("A:AA").Copy
        oXLSheetBase.Range("A1").PasteSpecial Paste:=xlPasteValues
    End If
Next iBase
 
oXLApp.Quit
 
Set oXLSheetTemp = Nothing
Set oXLSheetBase = Nothing
Set oXLBookCurrent = Nothing
Set oXLBookTemp = Nothing
Set oXLApp = Nothing
 
End Sub