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!

 

[siddharth]

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

My model should be capable of importing all the data from the 30 datafiles at the click of a button.This model should be able to do this when placed in the same folder as the 30 data files.How to do this?

 

[dave3009]

Yes, I understand that bit. You didn't answer my questions though, did you!! How on earth do you expect me to offer help when you won't answer a simple couple of questions?

 

[siddharth]

All the 30 individual .xls files are saved in a single format Sheet.xls and this sheet is located on the desktop.

thanks and regards.

 

[siddharth]

All the 30 individual .xls files are saved in a single format Sheet.xls and this sheet is located on the desktop.

thanks and regards.

hello dave,

I was myself not clear of the real problem.The problem is that I've 23 Excel Workbooks that have some data.In addition, there is a workbook containing some other data.This workbook is called Data.xlsx.I want to build a model capable of importing all the data from the 23 data files at the click of a button into this Data.xlsx file.This model should be able to do this when placed in the same folder as the 23 data files.

Could you please guide me as to what Macro code will work here.

Kind Regards,

siddharth

 

[ravishankar]

Hi
Try these codes.save the workbook inside the folder with 30+ files.

Sub Collate()
Dim z  As Long, e As Long, g As Long
Dim f As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
x = Cells(Rows.Count, 3).End(xlUp).Row + 2
Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close False
Cells(x, 2) = Cells(e, 1)
Range("C" & x).PasteSpecial xlPasteAll
End If
Next e
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "collating is complete."
End Sub

Attach it to a button and run the macro.
Ravi

 

[siddharth]

Re: Importing data from 23 datafiles at one click

I tried running the code but couldn't get it.The message that appears is that colllating is complete .

The problem is that I've 23 Excel Workbooks all in one file Flowdata.xlsx( that have some data ) .In addition, there is a second workbook in the same folder as the Flowdata.xlsx file and contains some other data.This second workbook is called Data.xlsx.I want to build a model capable of importing all the data from the 23 data files at the click of a button into this Data.xlsx file .That is, on clicking the macro button located on the Data.xlsx file all 23 datafiles from Flowdata.xlsx gets imported to the Data.xlsx file.This model should be able to do this when placed in the same folder as the 23 data files.

thanks and regards,

sdidharth

 

[siddharth]

Hi
Try these codes.save the workbook inside the folder with 30+ files.

Sub Collate()
Dim z  As Long, e As Long, g As Long
Dim f As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
x = Cells(Rows.Count, 3).End(xlUp).Row + 2
Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close False
Cells(x, 2) = Cells(e, 1)
Range("C" & x).PasteSpecial xlPasteAll
End If
Next e
Application.ScreenUpdating = True
Application.DisplayAlerts = True
MsgBox "collating is complete."
End Sub

Attach it to a button and run the macro.
Ravi

<table id="post2130034" class="tborder" width="100%" align="center" border="0" cellpadding="6" cellspacing="0"><tbody><tr valign="top"><td class="alt1" id="td_post_2130034" style="border-right: 1px solid rgb(255, 255, 255);"> I tried running the code but couldn't get it.The message that appears is that colllating is complete .

The problem is that I've 23 Excel Workbooks all in one file Flowdata.xlsx( that have some data ) .In addition, there is a second workbook in the same folder as the Flowdata.xlsx file and contains some other data.This second workbook is called Data.xlsx.I want to build a model capable of importing all the data from the 23 data files at the click of a button into this Data.xlsx file .That is, on clicking the macro button located on the Data.xlsx file all 23 datafiles from Flowdata.xlsx gets imported to the Data.xlsx file.This model should be able to do this when placed in the same folder as the 23 data files.

thanks and regards,

sdidharth
<!-- / 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>

 

[ravishankar]

Hi
you seem to have mixed up workbooks, worksheets, datafiles, folder atc. If you have 23 worksheets in a single workbook, my macro won't work for you
Ravi

 

[ravishankar]

Hi
try replacing with this code
f = Dir(Cells(1, 2) & "*.xlsx")
Ravi

 

[siddharth]

Hi
try replacing with this code
f = Dir(Cells(1, 2) & "*.xlsx")
Ravi

Thanks very much for that it worked out and was really helpful.Could you please assist me in developing the Macro code that will select the column G($G$1 : $G$130) in each of the 69 sheets of an Excel workbook and will count the number of sheets ( out of 69 sheets ) in which the text written is " Not Available " in the range $G$1 : $G$130

Kind Regards,

siddharth