import multiple files into Excel

[Johny1900]

Hi everybody,

I need to import about 1500 HTML file into a DB (SQL Server). But (here is the big problem), I only need to import some data of each HTML file (always the same line/variable).
So I thought using Excel as you can say with part of the HTML file you want to import. It works great.
But I did it with one file. Is there a way you can select a whole directory and it does it for you?

Many thx

Johny

 

[Ithyan]

Glad to help!
I figured that if you were an SAP expert, you wouldn't have any trouble picking up VBA, but it's always useful to have a running start!
Cindy

Hi Cindy,

Is it possible to select multiple columns from multiple files(say 5 .xls files)
into one Excel file.
The problem is the coulmns are not in order. I mean from sheet1 we have to select Coulmns A,D,F,K and from Sheet2 we have to select Coulmns P,U,Z something like that.
Is it possible?

Thanks in advance,

Regards,
Ithyan.

 

[Ithyan]

Hi Cindy,
Is it possible to select multiple columns from multiple files(say 5 .xls files)
into one Excel file.
The problem is the coulmns are not in order. I mean from sheet1 we have to select Coulmns A,D,F,K and from Sheet2 we have to select Coulmns P,U,Z something like that.
Is it possible?
Thanks in advance,
Regards,
Ithyan.

 

[Johny1900]

Hi Cindy,
Is it possible to select multiple columns from multiple files(say 5 .xls files)
into one Excel file.
The problem is the coulmns are not in order. I mean from sheet1 we have to select Coulmns A,D,F,K and from Sheet2 we have to select Coulmns P,U,Z something like that.
Is it possible?
Thanks in advance,
Regards,
Ithyan.

Ithyan,

can you give an example what you want to achive. Also are the columns you want to select always in that order?

 

[Cindy Ellis]

Hello Ithyan, Welcome to the Board!
I presume by the context that you want to combine different columns of several Excel files into one? In order to do that with a macro, there would need to be a way for the macro to "decide" which columns to use. When you do this manually, how do you know which columns to choose? How do you want to arrange the data from the multiple files? For instance, if you have columns A, C, E from file 1, each with 50 rows, and P, U, and Z from the second file, each with 50 rows, do you want output that is 3 columns by 100 rows? Or 6 columns by 50 rows? If you can post a very brief example of the inputs and the expected output, it will be easier to help.
Cindy

 

[Ithyan]

Thank You Very Much Cindy for a Quick Reply. I need 6 columns by 50 rows. And rows differ from time to time,but the coulmns to be picked up is standard ones. So each time i can run the same macro.

Thanks&Regards,
Ithyan

 

[Ithyan]

Ithyan,

can you give an example what you want to achive. Also are the columns you want to select always in that order?

Hi Johny,
The columns are fixed.And my output is also fixed.
Please let me know for any more clarifications.

Thanks&Regards,
Dencil.

 

[Ithyan]

Hi Cindy&Johny,
Hope the below explains better.

I have a Excel Sheet(Say OG.xls) which has some data already in it with some 5000 rows with headings in the first row and Upto "AN" Columns.
This No of rows(5000) doesnt change for a whole year.
Now i have 5 XL files(Say A,B,C,D,E) and the data from these files has to be appended to this OG file just starting from 5001st row every time.
All these 5 files has different no of coloumns but identical to that of OG File.
I have to pull data from these files and place them in OG File.
From File A : Column A,B,C,D,E,F,G&H goes to Coloumn F,G,T,U,V,W,X&Y Of OG.xls File likewise for the other files too.
The second file data has to be appended right below the next row where the File A ends.(Say after filling the data from File A now the OG.xls has 5110 rows,
the File B data has to filled from 5111 st row of OG.xls.
The same follows for the other files too.
Each time the same operation is repeated by filling the data from 5001st row of OG.xls
How can we do this.
Please help me in this!!!
Please let me know for any clarifications.

 

[Cindy Ellis]

Hello Dencil,
Your explanation is much more clear now, but it's past midnight where I live and I've discovered that macros written after midnight don't usually work very well!
I'll take a look at it in the morning...it doesn't seem too complicated.
Cindy

 

[Ithyan]

Thank You Cindy.

 

[Ithyan]

Hi Cindy,
Hope and wish ur doing gr8.
How is it going on,need any clarifications from my side...
Please let me know...

Cheers,
Ithyan