Copying an opened CSV to another existing Workbook

Question

Browse for an select a csv file, open that csv file, copy an entire sheet from that file not working - data is pasting to a new file instead

I want to create VBA code that will allow the user to browse for an select a .csv file, open it, copy an entire sheet from that file (it will have only one sheet in it, always), and paste that entire sheet into a sheet in the primary excel workbook called 'dec'.

Note that I'm not getting an error when the code runs. When I run it, I am able to browse for and select the .csv file. The data appears to be copied. But, it is pasting the data into a new workbook instead of into "This Workbook." And then, my code itself is getting pasted into the tab I designated (the 'dec' tab). I'm obviously doing something dumb ,but I can't see it today.

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your Stripe download", FileFilter:="csv Files (*.csv*),*csv*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Copy
ThisWorkbook.Worksheets("dec").PasteSpecial xlPasteValues
OpenBook.Close SaveChanges:=False

End If
Application.ScreenUpdating = True
End Sub

You're copying the worksheet, not the data on the worksheet. You want something more like OpenBook.Sheets(1).UsedRange.Copy then ThisWorkbook.Worksheets("dec").Range("A1").PasteSpecial xlPasteValues — Tim Williams
Something you could try, instead of copying and pasting the sheet, you could to OpenBook.Sheets(1).Move after:=ThisWorkbook.Worksheets("dec"). Obviously you can change which page it puts it after — Marx
@AllenW.Marx - a workbook needs at least one sheet (and a CSV has only one), so I don't think you can Move the sheet to another workbook. — Tim Williams
@TimWilliams You can. It closes the workbook when the last (or only) page is moved out of the workbook. — Marx

Dy.Lee Dy.Lee · Accepted Answer · 2020-02-29T01:44:23

Do like this. But the data will not be separated.

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim vDB As Variant

    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your Stripe download", FileFilter:="csv Files (*.csv*),*csv*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        vDB = OpenBook.Sheets(1).UsedRange
        'ThisWorkbook.Worksheets("dec").PasteSpecial xlPasteValues
        OpenBook.Close SaveChanges:=False
        ThisWorkbook.Worksheets("dec").Range("a1").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
    End If
    Application.ScreenUpdating = True
End Sub

Copying an opened CSV to another existing Workbook

1 Answers