How to import multiple file with different sheets

[VaishnaviGinni]

Hi,

I have 12 months files and in all the files there are 6 sheets and data are starting from 3rd row. So how to import the files in one go.

Thanks, 

Reeta Gour

[RogerS]

The attached macro will accept data from the directory tool (or any other way to obtain the location of the 12 files).   Point the directory tool at the folder with the 12 files are and it will read in all the file paths.  The macro then will get all the sheet names and then read in all the sheets 1 at a time fore each files.  In you case it will read in all 72 inputs (12*6) and stack the data with the same column names.  It also allows you to put in the # of rows before the header (I believe this will be 2 for your use case  .If other sheets exist in the workbooks you can add a filter before the dynamic input in the macro to filter out sheets that do not meet your criteria.  Let me know if you need help modifying.

Please mark as accepted solution if this is helpful.

Thx.

[VaishnaviGinni]

Hi Shanker,

At step 5 what is the file you have used in macro named as "SAP Generated file.xlsx". I need that file to replicate the same in my workflow.

Please it will good if you share the entire workflow and macro as a package so that all the dependent files will be there.

Thanks, 

Reeta Gour

[ShankerV]

Hi @VaishnaviGinni 

Please find the package attached.

I have used just sample files to check whether it is reading from Row 3, so you can use your file which can be consisting of any number of sheets and schema.

Reach out if you have any further questions, happy to support to get your issue fixed.

Many thanks

Shanker V

[yohana_Ocean]

hi @BrandonKO , your macro actually takes all the data from all the sheets, irrespective of the scheme or type, and stacks one after the other. However, I need to configure the macro such that it considers first row as data as well not the heading. I am unable to find where should I make the edit in your macro.