How to Import Multiple CSV Files into Separate Worksheets in the Same Workbook in Excel
Вставка
- Опубліковано 12 вер 2024
- In this tutorial you will learn how to import multiple CSV files into separate worksheets in the same workbook in Excel. Instead of combining the CSV files into one worksheet, you can use a Visual Basic Script in Excel to import multiple CSV files into different tabs. The VB Script will name each Microsoft Excel tab with the same name as the CSV file. Once you have imported the CSV files into the worksheet they can be saved and formatted like any other Excel workbook.
Visual Basic code: It would not let me put it in the description so I put it as a pinned comment.
Support me with your amazon purchases: melcompton.com....
Check out these programs I use for UA-cam
Bluehost discount: melcompton.com....
Canva Pro Trial: melcompton.com....
Adobe: melcompton.com...
Filmora: melcompton.com....
Follow me on social media:
Facebook: / therealmelco. .
Twitter: @melissaecompton
Instagram: @melissaacompton
LinkedIn: / melissa-c. .
This description contains affiliate links and I may be paid a small commission should you purchase using these links.
#melissacompton #msexcel #excel #importcsvfiles
VISUAL BASIC CODE:
Sub CombineCsvFiles()
'updated by MelCompton
Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = "|"
xFilesToOpen = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "MelCompton VBA for Excel", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "MelCompton VBA for Excel"
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "MelCompton VBA for Excel"
Resume ExitHandler
End Sub
Hi Melissa,
thanks for the code.
But it imports the csf files in a new excel file.
i want to import the csv in the same workbook where i am saving the vba code.
can you please help
@@RahulYadav-qn7jv Hi! I would not recommend doing it in the same workbook. It is easy to accidently change the VB code and then it not work or do something crazy. I recommend saving the VB code in a separate workbook and naming it template to keep this from happening.
@@MelCompton Actually i am using your code as a part of big macro. I want to import all CSV file in a specific Excel sheet.
HI Melissa, First thanks for the code is really helpful.
Will be possible to import to the same excel sheet where I execute the Macro? I want to use your macro as one of the steps in larger data preparation and the goal is to have one template that can trigger the base of the action in steps trigger by buttons.... if you can support me on this will be awesome! thanks.,
Is it possible to pull the csv data in with a specific Table Format? Like it does when you import a csv manually.
Amazing. This just saved me a thousand dollars of time. So grateful! Thank you!!
Thank you so much! I am glad it helped!
This was perfect, exactly what I needed! Your 5 minute video saved me hours of manual work
where can I find the code?
Hello! It wouldn’t let me put it in the description so it’s pinned in a comment.
This was incredible, and so easy to follow along with! Thank you for this!
You are very welcome! I am glad this helped you!
Well cone Melissa! You made this super easy to follow and helped me import 16 CSV's in less than 5 minutes!
THANK YOU SO MUCH this worked perfect I have been trying to figure out how to do this for awhile..
This was the exact thing I needed and I really searched so many yt videos but ..Finally ..I got this...Thanks Melissa for making this.
Man that was a Great. First time somebody went through all the steps .I was able to do it first try on split screen. I even made a small button on the template ( new for me too ) to run the macro.
very nice, my daughter had the need for this for college and I helped her find it.
Thanks this saved me pulling out my remaining few hairs !
🥰
You are very welcome! I'm happy it helped you!
Well done! I was trying to figure out a good way to combine data for the data analytics certification, and this helped me so much. Thank you!
Thank you! I'm glad it helped!
You just saved me hours worth of work!
YOU'RE A LIFE SAVER! THANK YOU!!
You are very welcome! I am glad this helped you!
Exactly what I needed. Thank you!
boom, 382 CSV files imported into different files. thanks, this worked great
Thank you! I'm glad it helped!
Awesome video. So useful and made my job 1k times easier. Nice job
Thank you so very much Melissa. What an amazingly clear and consise video
Thank you! I'm happy it helped!
you rock, thank you Melissa!
I sooooo neeed this. Thank you!
Thank you for making this available. I am trying to get it to run for Office for Mac. I can save the macro but when I try to run it it throws an error: "Methos 'GetOpenFilename' of object '_Application' failed". Any ideas?
Same here...
Thank you so much for such an amazing tutorial and providing code as well.
Thank you! Glad it helped!
Thanks @MelCompton this was exactly what i needed!
I love this macro so much! It is a life saver!👏
I am so glad it helped and thank you for the super!!!
Melissa! Thank you, this is a huge help!!
Thank you! Glad it was helpful!
Thank you! this will save me hours of work!
Thank you! Glad it helped!
This was so great! Thank you so much!
Thank you! I'm happy it helped!
Thank you! Thank you so much!
this is extremely helpful! thanks for your efforts!!
awesome! this saved so much time! Thanks!
Thank you! I'm glad it helped!
Thank you very much, this was really helpful.
Thanxxxxxx ALOT !
You are very welcome! I am glad this helped you!
thank you for this!
Absolute lifesaver, thank you for code.
Thank you! Glad it helped!
Thank you so much for this! This is amazing!
This is amazing.... Thank you so much for posting this.
Thank you so much. Saved hours of work 🙏
Glad it helped!
@@MelCompton It doesn't work if files have multiple sheets in them. Does it??
thank you very much Melissa
Thank you! Glad it helped!
this video is a blessing...Thank you
Thank you
You are very welcome! I'm happy it helped you!
Thank you melissa
Hello! Thank you! I'm happy it helped!
thanks for your share, your macro is helpful
THANK YOU SO MUCH
☺IT HELPED ME A LOT
It was very useful, thank you.
You are a life saviour!!!
Thank you! Glad it helped!
That was super helpful - thank you
Very useful. Thanks
So helpful, thanks!
I can not thank you enough. Awesome content!
Perfect, thank you.
Thanks Melissa what a good piece of basic programming
Is it also possible that a specific folder is opened and that I can browse from there and then choose the csv files.
Regards,
Michel.
This is the closest thing I've found to what I am looking for! However instead of opening into a new sheet I'd like subsequent CSVs to open in the adjacent columns. Each CSV has two columns of data so for example the first CSV imports into columns A & B, the second csv imports into columns C & D, the third into E &F and so on. Is there a way to modify this code to have it import my data like that? Thank you for your help!
It doesn't work for me unfortunately, I can not select my files when running the macro even though I have txt. files. Could you help me, please
Thank you very much!
Thank you! I'm glad it helped!
Great 👌👌👌👌👌👌
You are awesome...love you...thanks for the code...
where can I find the code?
Hello! It wouldn’t let me put it in the description so it’s pinned in a comment.
Thanks for this, how do I achieve same result in Google Sheets?
Unfortunately this is not possible in Google Sheets.
THANK YOU!
Thank you for the code. The delimiter does not seem to be used in the code. I need to set the delimiter to something specific. Can you help please?
Hi! CSV is Comma Separated Values. What is your delimiter?
Thank you so much 😍
Thanks Melissa, works perfectly to what I need except is there a way to delimite using semicolons or comma? I am a noob in VBA, so appreciate your help!
Thanks melissa.
Thank you. I’m trying to run it on mac but am getting an error message “method’getopenfilename’ of object’_Application’failed
Same
I've experienced same error....
Hello! This is not compatible on a MAC. Unfortunately Microsoft doesn't fully code for MAC's so some functionality is missing. Hopefully they will change that in the future.
Hello! This is not compatible on a MAC. Unfortunately Microsoft doesn't fully code for MAC's so some functionality is missing. Hopefully they will change that in the future.
Hello! This is not compatible on a MAC. Unfortunately Microsoft doesn't fully code for MAC's so some functionality is missing. Hopefully they will change that in the future.
Is there a visual basic code to help power query load Csv files from a folder location automatically as opposed to having the user individually load csv files from power query?
Hi! This is a great suggestion! I will add it to my list to research and make a tutorial.
This works great. Confused by the variable: xDelimiter = "|". I have pipe (|) delimited text files and wondering how you change the file delimiter?
How can we copy the code down? Thank you so much!
Great tutorial! Thank you.
I have a slightly different need. I need to import data from multiple csv files onto ONE worksheet. In other words, make one sheet where the information from each new file is appended to the information imported from the previous file.
How would I adjust this code to accomplish that?
Thanks for considering!
This looks great!
Althoughi have one issue, we use semi colon separated csv. Any tipps how I could adjust the macro to work with those?
did you get solution for this?
I love you. It is awesome
How can I make this VBA code to be applied in all the excel file whenever I open a new excel file
Now if I did this and changed 150 csv files into 1 workbook, how do I give them format to not have all the information in just 1 column?
did the steps for me its just creating a new sheet, any idea why?
How can we do the same function but with .xls files?
Very informative? Can i do same import from text files as well ?
Hi. Yes it should work the same way. :)
@@MelCompton When I navigate to the folder with my text files, there are no files to select. Any ideas why I am not seeing my files?
Hello Melissa,
Thankyou for the video, it is well informative.
But in my case, instead of . csv files, i have . out files.
How to deal with it. Can you please help me here.
Thank you! This is great, though my CSV files' data with leading zeros are losing their leading zeros. For example, Zip Codes with leading zeros are importing, but leading zeros are gone once in Excel. Is there a way to make all CSV columns being imported with the macro to be text? Again, my thanks!
Yes. It will take some extra VBA code. I will get it written and tutorial put out as soon as I can.
Hi @melissa, Firstly Thank you for the code and guide can you share the changes for Mac as this doesnt work in Mac
Hello! Unfortunately this is not compatible with MAC.
Hi Melisa thank you... I am not able to find the pinned comment could you please help
Hello! I re-pinned the comment. You should be able to see it now.
Perfect😀
I am continuously getting a type mismatch error.. Can you help ?
Hi Melissa, how would the code change if I wanted to add the tabs to an existing workbook
Hello! I will pop out a quick short and explain how to do this as soon as I can.
Hey , I tried this code but there is an error showing up that says " That name is already taken" in a melcompton vba dailogue box. Is there any solution to this. Kindle let me know
Hi..thank you but can we add 2 csv files in single worksheet. Pls help
Hello! Yes you can. I am putting together a quick tutorial on how to do this!
Very helpful! However, I need to import multiple Excel files into corresponding sheets in my existing report template. How can I do that?
Hello! Are you wanting to import multiple CSV's or actual Excel files (.xlxs)?
Thanks for the video. Im trying to do the same in macbook but im getting an error "Method 'GetOpenFilename' of object '_Application' failed". Could you please help me with it? Advance Thanks.
yeah, same with you. I just try in MacBook get error like this
VB on a MAC is not supported by Microsoft. The version of MS Office for the MAC is limited and unfortunately one of the key functions that is not compatible is VB. Hopefully one day Microsoft and Apple (the silicon behind the MAC) will play nice, but we will have to see. :)
@@MelCompton thank you for responding, do you have any other method?
I get the error: “That name is already taken. Try a different one.”
How do I fix this?
HI Melissa, where can I find the code?
Hello! It wouldn’t let me put it in the description so it’s pinned in a comment.
Hello Mellisa, where do i go to get the vba code? Thank you.
Hello! It wouldn’t let me put it in the description so it’s pinned in a comment.
Thank You for this Tutorial. However, I do have few .txt files that have leading zero's in one of the cell. Those are getting trimmed in excel. So in the VB script is there any code we can add so that when macro imports the data from .txt to excel in text format (rather than general) so that leading zero's are preserved?
If the .csv files are saved with the columns formatted with the leading zero's it should not be stripping them. You can also try using the text functiion to preserve the zero's before saving the .csv. An example would be =TEXT(A1,"00000) then copy and paste special. I will look and see if there is something I can add to the code to keep this from happening on import. But, if there are gone at the time of saving the .csv file then the code won't see them.
Hi Melissa,
It is throwing an error : Object variable or With block variable not set
after having loaded a few files into the Excel workbook.
Please help
The error was thrown because i had opened one of the files.
Thanks again, Melissa for your beautiful work.
Thank you! I'm glad it helped!
👌👌👌👌👌👌👌👌👌👌
Is it possible to Import Multiple excel Files into Separate Worksheets in the Same Workbook in Excel?
By replacing, csv files to the xlsx it is easily achievable. Thanks.
Thank you melissa
give me your linkdln pls the above link isn't working