I need to import numerous .csv files into a single Excel spreadsheet.
Currently I open the .csv, press Alt & A to select all data and then copy / paste the date into the spreadsheet
Is there a method to drag & drop .csv files into a spreadsheet?
Thanks
Last edited by TimTDP; 11-03-2011 at 02:28 PM.
Perhaps a VBA help...
'CSVs To Sheets
I have a macro that may be "ready to use" for importing all CSV files in a folder into a single Excel sheet, each CSV filename will be listed on the sheet next to the data that it came from.
CSVs to 1 Sheet
How/Where to install the macro:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Edit the parts in red as needed for your workbook/CSVs
6. Get out of VBA (Press Alt+Q)
7. Save as a macro-enabled workbook
The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Hi Jerry
I have looked at your macro and it is great.
However I want to place the new file to the right, rather than below.
So the new file will always be placed in row 2, with the file name in row 1 (one cell only)
Can you assist?
Last edited by TimTDP; 11-02-2011 at 01:25 PM.
I've added a "Part 2" to the published page for your scenario. Try that version.
'CSVs To One Sheet (Side By Side)
I have a macro that may be "ready to use" for importing all CSV files in a folder into a single Excel sheet, each CSV filename will be listed above the data, the data imported into adjacent columns.
CSVs to 1 Sheet - Part 2
Works like a bomb.
Thank you very much
enjoy the steak!
Thanks for the feedback!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
I have messed something up!
I have altered the code as follows (to change the csv slightly) as follows:
ActiveSheet.UsedRange.Copy wsMstr.Cells(1, NextCol) gives me the error:
Please Login or Register to view this content.
Application-defined or object-defined error.
I get this if I hover the mouse over wsMstr.Cells(1, NextCol)
What have I done wrong?
You must religiously take OUT the selecting and activating you get from recorded code, merging all the rows down into fully addressed direct commands.
Try this:
Please Login or Register to view this content.
Thanks
Same error on line:
Range("A1:A" & KountRows).EntireRow.Copy wsMstr.Cells(1, NextCol)
When you DEBUG and that line is highlighted, hover your mouse over KountRows and NextCol, what the values?
KountRows = 5115
NextCol = 0
You can't paste data into column 0. Doesn't exist.
The original full macro set the "NextCol" earlier the code than what you shared above. What did you change/omit? That variable must be set prior to entering the the DO LOOP section. Let me see your full edited version.
Thanks. Don't know why but I set NextCol = 0
However, Excel now tells me I can't paste because the copy & paste areas are not the same!
Run time error 1004
Hmmm..... still waiting.Originally Posted by JBeaucaire
Please Login or Register to view this content.
Last edited by TimTDP; 11-05-2011 at 09:45 AM.
Try:
Please Login or Register to view this content.
unfortunately not
Same error
If KountRows = 0 then this will never work. Why is your data in the opened workbook showing nothing in column A?
I have created an entry in A1, but still get the same error
I have attached my files. Could you have a look at them?
The files with numeric names are the data files I want to import.
The first import work, and this is the result I want for all imported files.
Many thanks
You had messed up the copy/paste of the "else" part of the first section. Also, I changed that copy/paste command to only copy the number of columns needed instead of trying to copy the entire row, that seems to have resolved it.
Please Login or Register to view this content.
Thank you very much
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks