-
Mar 19th, 2009, 01:43 PM
#1
Thread Starter
Hyperactive Member
[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:
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
-
Mar 19th, 2009, 02:42 PM
#2
Thread Starter
Hyperactive Member
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
-
Mar 19th, 2009, 03:17 PM
#3
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
-
Mar 19th, 2009, 03:22 PM
#4
Thread Starter
Hyperactive Member
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.
-
Mar 19th, 2009, 03:34 PM
#5
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|