Results 1 to 5 of 5

Thread: [RESOLVED] Import CSV into existing Excel Worksheet with VBA

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Posts
    394

    Resolved [RESOLVED] Import CSV into existing Excel Worksheet with VBA

    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?

    vb Code:
    1. Private Sub btnImportBase_Click()
    2.  
    3. Dim oXLApp As Excel.Application
    4. Dim oXLBookTemp As Excel.Workbook
    5. Dim oXLBookCurrent As Excel.Workbook
    6. Dim oXLSheetControl As Excel.Worksheet
    7. Dim oXLSheetBase As Excel.Worksheet
    8. Dim oXLSheetTemp As Excel.Worksheet
    9. Dim iBase As Integer
    10. Dim sBase As String
    11. Dim aBase(5) As String
    12. Dim iLastRow As Integer
    13. Dim iLastCol As Integer
    14. Dim iCol As Integer
    15. Dim iRow As Integer
    16.  
    17.  
    18. aBase(1) = "Base1"
    19. aBase(2) = "Base2"
    20. aBase(3) = "Base3"
    21. aBase(4) = "Base4"
    22. aBase(5) = "Base5"
    23.  
    24. Set oXLBookCurrent = ThisWorkbook
    25. Set oXLSheetControl = oXLBookCurrent.Worksheets("Control")
    26.  
    27. Set oXLApp = New Excel.Application
    28.         oXLApp.Visible = True   'Visible for debug only
    29.  
    30. For iBase = 1 To 5
    31.     sBase = oXLSheetControl.Cells(8 + iBase, 2).Value  'Contains path of file to open
    32.     If Not sBase = "" Then   'If path exists
    33.            
    34.         Set oXLBookTemp = oXLApp.Workbooks.Open(sBase)  'Open CSV file
    35.         Set oXLSheetTemp = oXLBookTemp.Worksheets(1)    'Define worksheet
    36.        
    37.         'define worksheet in current book based on index iBase
    38.         Set oXLSheetBase = oXLBookCurrent.Worksheets(aBase(iBase))
    39.  
    40.         ' find last used row and column
    41.         iLastRow = oXLSheetTemp.Cells(Application.Rows.Count, 1).End(xlUp).Row
    42.         iLastCol = oXLSheetTemp.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    43.        
    44.        
    45.         'This works but is VERY slow
    46.         For iRow = 1 To iLastRow
    47.           For iCol = 1 To iLastCol
    48.             oXLSheetBase.Cells(iRow, iCol) = oXLSheetTemp.Cells(iRow, iCol)
    49.           Next iCol
    50.         Next iRow
    51.        
    52.         'This also works but I have to hard code the range
    53.         oXLSheetTemp.Range("A:AA").Copy
    54.         oXLSheetBase.Range("A1").PasteSpecial Paste:=xlPasteValues
    55.     End If
    56. Next iBase
    57.  
    58. oXLApp.Quit
    59.  
    60. Set oXLSheetTemp = Nothing
    61. Set oXLSheetBase = Nothing
    62. Set oXLBookCurrent = Nothing
    63. Set oXLBookTemp = Nothing
    64. Set oXLApp = Nothing
    65.  
    66. End Sub

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Posts
    394

    Re: Import CSV into existing Excel Worksheet with VBA

    I did the hard part and suppressed the "data on the cilp board" prompt using.

    oXLApp.DisplayAlerts = False

  3. #3
    Head Hunted anhn's Avatar
    Join Date
    Aug 2007
    Location
    Australia
    Posts
    3,669

    Re: Import CSV into existing Excel Worksheet with VBA

    Try this without knowing last row and last column:
    Code:
            oXLSheetTemp.UsedRange.Copy
            oXLSheetBase.Range("A1").PasteSpecial Paste:=xlPasteValues
    • Don't forget to use [CODE]your code here[/CODE] when posting code
    • If your question was answered please use Thread Tools to mark your thread [RESOLVED]
    • Don't forget to RATE helpful posts

    • Baby Steps a guided tour
    • IsDigits() and IsNumber() functions • Wichmann-Hill Random() function • >> and << functions for VB • CopyFileByChunk

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Posts
    394

    Re: Import CSV into existing Excel Worksheet with VBA

    Thanks Anhn. That fixed the copy/paste problem.

    Now to get the file open loop to work.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2005
    Posts
    394

    Re: Import CSV into existing Excel Worksheet with VBA

    Never mind that on the file open problem. It helps if I use the filename that actually exists. Looks like I need a little error checking before release. Don't want an error if the user types the wrong file name.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width