VBA code combine multiple workbooks into one with filename included from selected folder

[Roux11]

Hi,

I would like to seek help in creating a macro to combine multiple workbooks into one (append) and add a column to include their filenames. The macro should first ask to browse the folder where the excel files are located.

Thank you in advance!

Ron

[vba_php]

to browse the folder where the files are located, you are going to want to use the FILE DIALOG object in VBA. you can find the documentation on it here: file dialog method in VBA. regarding your question about combining multiple workbooks into one, you have not given any detailed information about what you want to do. there are multiple sheets in each workbook and the only thing you said was that you wanted to append them. so, what exactly does that mean?

[Roux11]

There is only 1 sheet in each workbook all with the same columns and I need to append all of them in one workbook.

[vba_php]

have you ever used code before in excel? someone might give you the code, but if you want a wonderful start, take a look at the following links for objects you could make very good use of. this is the official documentation from microsoft, just like all other languages have official documentation for their own communities:

fileDialog object

workbook object

capturing a file name of an excel file selected with the ''fileDialog'' object

excel's range object

[Roux11]

Thank you. I am a novice at creating VBA codes. I did this in power query unfortunately data in one of the columns is being truncated that is why I am looking for a vba code to do this instead.

[vba_php]

if I'm not mistaken power query can only pull data from one dataset at a time regardless if it's an internal or external source correct?

I'm currently on a phone posting this but maybe I can put together some code that can do something like what you want later on but hopefully someone else is not busy and maybe can give you a start here. I try not to write all the coat someone needs a rather just a replica so they can learn it so they don't have to ask the same question a second time.

[Roux11]

Power query can pull from different workbooks and sheets both internal and external. I just cannot seem to find out why it truncates data when it is way below the limit of 32k characters. Max character I have in a cell is 1k+. I am pressed for time now so I would appreciate if I can get a complete code. I found one but it only copies the sheets from different workbooks and creates a new tabs in the master file where it should be collated. It does not append it like how I need it.

[vba_php]

well, that seems strange, as according to this article, the max cell length is a simple power of 2, which of course it should be:

https://communities.sas.com/t5/SAS-P...et/td-p/199915

how pressed for time are you? generally, I don't think that makes folks happy because they help for free. however, can you adopt the following code and finish it by figuring out how to copy your column data and file names? I am opening all of the workbooks for you and cycling through all the sheets in each workbook. you can figure out the rest with the macro recorder:

Please Login or Register  to view this content.

please note that my code does not take into account the index lower and upper bounds. so, if it errors out, just change those bounds of the counter variables. you can take it from here.