Importing data from 30 datafiles at one click

[siddharth]

HI,

I want to import data from 30 different datafiles at the click of a button.Also,I want that this button be placed in the same folder as the 30 data files.How could I do that with VBA Code or any other technique in Excel 2007.

Thank you in advance for looking at this!

 

[ravishankar]

Hi
Are these datafiles excel type? if yes what is its layout. how would you like to see the consolidated output of 30+ files
ravi

 

[siddharth]

hi ,

The data is in the form of 30 execl files and want to jave all these files open by clicking a button which I want to be placed on a single sheet may be by attaching a Macro to that button.

Suppose that the 30 datafiles are named:- Sheet1,Sheet2,.....,Sheet30 and I want to create a new sheet 31 on which a Macro button is located and I can click that button to open all 30 datafiles.

siddharth.

 

[siddharth]

hi ,

The data is in the form of 30 excel files and want to have all these files open by clicking a ( macro) button which I want to be placed on a single sheet by attaching a Macro to that button.

Assume that the 30 datafiles are named:- Sheet1,Sheet2,.....,Sheet30 and I want to create a new sheet 31 on which a Macro button is located and I can click that button to open all 30 datafiles.

How can I do this ?

siddharth.

 

[dave3009]

Assuming your datafiles are named as you state and your sheet31 is saved in the same folder

Dim fPath As String
Dim i As Integer
fPath = ThisWorkbook.Path
For i = 1 To 30
    Workbooks.Open fPath & "\Sheet" & i & ".xls"
Next i

In you commandbutton code

 

[siddharth]

hello Dave,

thanks for the reply.

I'm using MS Office Excel 2007 and I'm getting a RunTime Error with this code.

If I've saved all 30 Excel datafiles( Sheet 1, Sheet 2,....Sheet 30 )on my desktop by the name say " Import.xls " and want that when I open the " Import.xls " file then the 31st Sheet named say " Import " gets opened on which the Macro is linked .

many thanks
siddharth

 

[siddharth]

<table id="post2127634" class="tborder" width="100%" align="center" border="0" cellpadding="6" cellspacing="0"><tbody><tr valign="top"><td class="alt1" id="td_post_2127634" style="border-right: 1px solid rgb(255, 255, 255);"> hello ,

thanks for the reply.

I'm using MS Office Excel 2007 and I'm getting a RunTime Error with this code.

If I've saved all 30 Excel datafiles( Sheet 1, Sheet 2,....Sheet 30 )on my desktop by the name say " Import.xls " and want that when I open the " Import.xls " file then the 31st Sheet named say " Import " gets opened on which the Macro is linked .

many thanks
siddharth
<!-- / message --> </td> </tr> <tr> <td class="alt2" style="border-style: none solid solid; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255); border-width: 0px 1px 1px;">

</td> <td class="alt1" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(255, 255, 255) rgb(255, 255, 255) -moz-use-text-color; border-width: 0px 1px 1px 0px;" align="right"> <!-- controls -->

</td></tr></tbody></table>

 

[dave3009]

Right you've confused me.

The 'data files' are these sheets in a book or seperate xls. More importantly are you saving as xls or xlsx, xlsm?

 

[siddharth]

hello,

I'm using MS Office Excel 2007 and this code is giving error.I want to import all the 30 datafiles at the click of a button.Also, I want this to be placed in the same folder as the 30 data files.

 

[dave3009]

Do you mean

1. You have 30 individual .xls files saved in format Sheetx.xls
2. You want to open these and save each one to an assigned page in Import.xls.
3. All files are saved only as .xls