Importing data from 30 datafiles at one click

siddharth

Board Regular
Joined
Nov 18, 2009
Messages
76
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!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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

Board Regular
Joined
Nov 18, 2009
Messages
76
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

Board Regular
Joined
Nov 18, 2009
Messages
76
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016

ADVERTISEMENT

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

Code:
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

Board Regular
Joined
Nov 18, 2009
Messages
76
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

Board Regular
Joined
Nov 18, 2009
Messages
76

ADVERTISEMENT

<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;">
user_online.gif
</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 -->
progress.gif
</td></tr></tbody></table>
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Board Regular
Joined
Nov 18, 2009
Messages
76
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,538
Messages
5,596,750
Members
414,097
Latest member
FaeFen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top