Importing CSV files with VBA Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1621529%22%20slang%3D%22en-US%22%3EImporting%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621529%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20new%20on%20this%20forum.%20Maybe%20my%20questions%20are%20already%20posted%20there%20or%20another%20forum%2C%20but%20after%20many%20research%20on%20Internet%20I%20haven't%20found%20good%20answers.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%3EMy%20project%3A%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20just%20want%20to%20import%204%20CSV%20files%20into%20an%20%3CSTRONG%3Eexisting%20XLSM%20workbook%3C%2FSTRONG%3E%2C%20and%20each%20into%20a%20separate%20sheet%20that%20has%20been%20named%20beforehand.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EFor%20example%3A%20file1.csv%20in%20sheet1%2C%20file2.csv%20in%20sheet2%2C%20file3.csv%20in%20sheet3%20and%20file4.csv%20in%20sheet4%3C%2FSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EFirst%20question%3A%3CBR%20%2F%3EI%20would%20like%20to%20import%20these%20files%20by%20UserForm%20and%20ListBox%2C%20with%20a%20CommandButton.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIs%20it%20possible%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESecond%20question%3A%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EIf%20the%20previous%20operation%20is%20possible%2C%20%3CSPAN%3Eis%20it%20possible%20to%20display%20only%20the%20name%20of%20the%20imported%20files%20on%20ListBox%3F%3C%2FSPAN%3E%20(full%20path%20displayed%20by%20default)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThanks%20in%20advance%20for%20your%20answers%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1621529%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621635%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776089%22%20target%3D%22_blank%22%3E%40SebInfoFR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%20is%20VBA%20really%20necessary%20for%20this%20task%3F%20I%20mean%2C%20Excel%20has%20Power%20Query%20feature%20that%20you%20can%20specify%20a%20folder%20where%20your%20CSV%20files%20are%20stored%20and%20you%20can%20manage%20all%20them%20more%20easily%20than%20by%20coding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Juliano-Petrukio_0-1598806075628.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F215464i4B2E932AD499F8F1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Juliano-Petrukio_0-1598806075628.png%22%20alt%3D%22Juliano-Petrukio_0-1598806075628.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621693%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20Juliano%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3C%2FP%3E%3CP%3EIn%20my%20first%20post%2C%20I%20forgot%20some%20details.%20My%20apologies.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20XLSM%20file%20will%20finally%20used%20by%20my%20workteam%20(and%20me)%20every%20month%20(I%20work%20in%20an%20IT%20support).%20And%20I%20have%204%20exported%20files%20every%20month%20from%20our%20AD%20(by%20PowerShell).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20m%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Ey%20idea%20is%20to%20use%20VBA%20with%20a%20UserForm%20for%20the%20import%20and%20layout%20of%20these%20files%2C%20using%20CommandButton%2C%20for%20quickly%20processing%20of%20AD%20data%20later.%3CBR%20%2F%3E%3CSPAN%3EOur%20main%20job%20is%20to%20resolve%20user%20issues%2C%20but%20we%20are%20also%20responsible%20for%20the%20entry%20and%20exit%20of%20employees%2C%20with%20different%20procedures.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20started%20creating%20my%20XLSM%20file%20in%20consultation%20with%20my%20manager%2C%20for%20faster%20management%20of%20employee%20outings%2C%20while%20respecting%20our%20procedures.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EIt%20is%20for%20this%20purpose%20that%20I%20thought%20I%20would%20use%20VBA%20Excel%20for%20quick%20layouts%20(I%20think%20it%20is%20possible%2C%20but%20I'm%20not%20sure).%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EWell%2C%20I%20just%20need%20to%20know%20if%20it%20is%20possible%20to%20import%20csv%20files%20into%20each%20sheet%20by%20their%20names%20because%20I'm%20blocking%20on%20that.%20I%20can%20manage%20afterwards%20for%20the%20layout.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EDo%20you%20think%20it%20is%20possible%20by%20VBA%3F%20Or%20with%20Power%20Query%20fast%20enough%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1621821%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1621821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F776089%22%20target%3D%22_blank%22%3E%40SebInfoFR%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20it's%20possible%20to%20import%20CSV%20files%20into%20your%20worksheets.%20Though%20I'm%20afraid%20I%20don't%20follow%20why%20you%20want%20to%20read%20the%20filenames%20into%20a%20listbox%20first%20(you%20could%20add%20a%20command%20button%20to%20a%20worksheet%20and%20just%20get%20the%20filenames%20and%20copy%20the%20data%20in)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20file%20with%20a%20userform%20and%20some%20basic%20code%20to%20read%20filenames%20into%20a%20listbox.%20Also%2C%20I%20added%20a%20button%20to%20import%20two%20files.%20Though%20these%20two%20buttons%20are%2C%20at%20present%2C%20independent%20of%20each%20other%20(the%20import%20button%20will%20also%20have%20you%20browse%20for%20the%20files%20-%20I%20don't%20know%20what%20mechanism%20you%20plan%20to%20use%20for%20the%20import%20function%20to%20open%20the%20files%20since%20the%20listbox%20will%20only%20have%20the%20file%20name%20and%20not%20the%20path).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1622221%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1622221%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20JMB17%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply%2C%20and%20thanks%20for%20your%20file.%20%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EI%20will%20visualize%20it%20as%20soon%20as%20I%20have%20time%20(working%20this%20week%2C%20I%20might%20have%20a%20lot%20of%20work).%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20keep%20you%20informed%20of%20the%20continuation.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EFor%20my%202nd%20question%2C%20it%20was%20just%20to%20show%20only%20the%20file%20names%20in%20ListBox%2C%20to%20make%20it%20more%20intuitive%20for%20my%20workteam.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EBut%20if%20that%20is%20not%20possible%2C%20it's%20not%20important.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638060%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%20JMB17%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20are%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20checked%20and%20tested%20your%20VBA%20code%20in%20my%20XLSM%20file%2C%20and%20it%20works%20perfectly.%3C%2FP%3E%3CP%3EEventually%2C%20I%20have%20deleted%20ListBox%20and%20others%20CommandButtons%20on%20my%20original%20UserForm.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%2C%20thank%20you%20very%20much%2C%20y%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3Eou%20helped%20me%20a%20lot%20on%20the%20part%20where%20I%20was%20blocking%20for%20a%20long%20time.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHave%20a%20nice%20day.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638918%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638918%22%20slang%3D%22en-US%22%3EYou're%20welcome%2C%20I'm%20glad%20to%20hear%20you%20are%20able%20to%20move%20forward%20on%20your%20project.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1639016%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20files%20with%20VBA%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1639016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20and%20you%20help%20me%20on%20another%20point%3A%20my%20first%20MultiSelect%3A%3DTrue%20(on%20Application.GetOpenFileName)%20of%20my%20code%20didn't%20work%20because%20I%20had%20declared%20the%20variable%20%22fNames%22%20(taking%20the%20example%20of%20your%20XLSM)%20As%20%3CSTRONG%3EString%3C%2FSTRONG%3E%20(I%20had%20only%20learned%20this%20option).%3CBR%20%2F%3EI%20hadn't%20thought%20to%20explore%20other%20options%20availabled%2C%20including%20As%20%3CSTRONG%3EVariant%3C%2FSTRONG%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20still%20quite%20a%20beginner%2C%20so%20I%20have%20a%20lot%20to%20learn%20about%20VBA%2C%20and%20Excel%20in%20general.%3C%2FP%3E%3CP%3EI%20think%20this%20software%20is%20really%20powerful%20and%20efficient.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello everyone,

 

I'm new on this forum. Maybe my questions are already posted there or another forum, but after many research on Internet I haven't found good answers.

 

My project:
I just want to import 4 CSV files into an existing XLSM workbook, and each into a separate sheet that has been named beforehand.

For example: file1.csv in sheet1, file2.csv in sheet2, file3.csv in sheet3 and file4.csv in sheet4

 

First question:
I would like to import these files by UserForm and ListBox, with a CommandButton.

Is it possible?

 

Second question:

If the previous operation is possible, is it possible to display only the name of the imported files on ListBox? (full path displayed by default)

 

Thanks in advance for your answers

 

7 Replies
Highlighted

@SebInfoFR 

Hello, is VBA really necessary for this task? I mean, Excel has Power Query feature that you can specify a folder where your CSV files are stored and you can manage all them more easily than by coding.

 

Juliano-Petrukio_0-1598806075628.png

 

Highlighted

@Juliano-Petrukio 

Hello Juliano,

 

Thank you for your reply.

In my first post, I forgot some details. My apologies.

 

My XLSM file will finally used by my workteam (and me) every month (I work in an IT support). And I have 4 exported files every month from our AD (by PowerShell).

 

Well, my idea is to use VBA with a UserForm for the import and layout of these files, using CommandButton, for quickly processing of AD data later.
Our main job is to resolve user issues, but we are also responsible for the entry and exit of employees, with different procedures.
I started creating my XLSM file in consultation with my manager, for faster management of employee outings, while respecting our procedures.

 

It is for this purpose that I thought I would use VBA Excel for quick layouts (I think it is possible, but I'm not sure).

 

Well, I just need to know if it is possible to import csv files into each sheet by their names because I'm blocking on that. I can manage afterwards for the layout.

 

Do you think it is possible by VBA? Or with Power Query fast enough?

Highlighted

@SebInfoFR 

 

Yes, it's possible to import CSV files into your worksheets. Though I'm afraid I don't follow why you want to read the filenames into a listbox first (you could add a command button to a worksheet and just get the filenames and copy the data in)?

 

I attached a file with a userform and some basic code to read filenames into a listbox. Also, I added a button to import two files. Though these two buttons are, at present, independent of each other (the import button will also have you browse for the files - I don't know what mechanism you plan to use for the import function to open the files since the listbox will only have the file name and not the path).

 

 

 

 

Highlighted

@JMB17 

Hello JMB17,

 

Thanks for your reply, and thanks for your file. I will visualize it as soon as I have time (working this week, I might have a lot of work).
I keep you informed of the continuation.

For my 2nd question, it was just to show only the file names in ListBox, to make it more intuitive for my workteam. But if that is not possible, it's not important.

Highlighted

@JMB17 

 

Hello JMB17,

 

I hope you are well.

 

I have checked and tested your VBA code in my XLSM file, and it works perfectly.

Eventually, I have deleted ListBox and others CommandButtons on my original UserForm.

 

Really, thank you very much, you helped me a lot on the part where I was blocking for a long time.

 

Have a nice day.

Highlighted
You're welcome, I'm glad to hear you are able to move forward on your project.
Highlighted

@JMB17 

 

Yes, and you help me on another point: my first MultiSelect:=True (on Application.GetOpenFileName) of my code didn't work because I had declared the variable "fNames" (taking the example of your XLSM) As String (I had only learned this option).
I hadn't thought to explore other options availabled, including As Variant.

 

I'm still quite a beginner, so I have a lot to learn about VBA, and Excel in general.

I think this software is really powerful and efficient.