Merging Data from many workbooks in to one workbook

[Rajesh Kr Joshi]

Hi,

I got a macro code to merge data from diffrent files into one workbook. Currently when we run the code every time it make a new workbook and fetch the files basis given path. I wants, instead of making a new file it should update an existing file (I will assigne the macro to a button), and it should start merging the data from sheet2, because sheet1 is reserve for my dashboard.

Attached is the file with code.
Code source: http://www.rondebruin.nl/fso.htm

Thanks
Rajesh

[Excel Fox]

Search for the code
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1) with


If ThisWorkbook.Worksheets.Count > 1 Then
Set BaseWks = ThisWorkbook.Worksheets(2)
Else
Set BaseWks = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.Count))
End If

[Rajesh Kr Joshi]

Hi, Implemented the below solution, while running the code getting dbug screen and highlighting the below line:


If Not sh Is Nothing Then
sh.Copy After:=BaseWks.Parent.Sheets(BaseWks.Parent.Sheets .Count)

seems needs to make changes to this line as well.

Thanks
Rajesh

[Excel Fox]

can you post the error description?

Is it by any chance, this..


Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook.

If yes, try using the attached Excel 2007-2010 file.

[Rajesh Kr Joshi]

This code is working fine :) . Let me implement this in the real sheet that i am working on. Will update the results.

Thanks for the help.
Rajesh

[Rajesh Kr Joshi]

Hi, This code keeps adding sheets (makes dupilcate) , if i run it for more then one time. Is it possible to over write the exixting sheets. My scenario is - I have the source workbooks with unique names and i wants to update the destination file (the file with code) every time i run the code. On more thing while fetching the data if there is an excel 2003 file it only the name in the sheet same as the source file name , but if the file is a excel 2007, it shows sourcefilename.xlsx.

Thanks
Rajesh

[Excel Fox]

Replace my last piece of code with



Application.DisplayAlerts = False
Do While ThisWorkbook.Worksheets.Count > 1
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Del ete
Loop
Application.DisplayAlerts = True
Set BaseWks = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Sh eets(ThisWorkbook.Sheets.Count))

[Rajesh Kr Joshi]

Hi, I replaced the code, when you run the code it seems it is doing somthing ,but it is dosent add the data, it shows only one blank sheet.
Is it possible fot you to implement in the file that i attached and send.

Thanks
Rajesh

[Rajesh Kr Joshi]

Hi Experts, awaiting for your kind assistance.

Thanks
Rajesh

[Excel Fox]

Working on this now

[Excel Fox]

Try this in place of my last code



Application.DisplayAlerts = False
Do While ThisWorkbook.Worksheets.Count > 2
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Del ete
Loop
Application.DisplayAlerts = True
Set BaseWks = ThisWorkbook.Worksheets(2)

[Rajesh Kr Joshi]

Hi, -

This seems woking fine. However the file i am copying contains password (same password for all files), and this code gives me an debug option about the protected sheets. and highlights .value=.value
Where shoud i define the password in this code to resolve this.

[Excel Fox]

Write
.parent.unprotect "passwordhere" right before that line

[shaneallen16]

Hi in the sheet to which their is a link there is a sheet which does exaclty that, Each teacher makes a copy punch his data in and than any one sheet is used to import all of the other data where ever it has been saved usually it is done from a USB stick

[Rajesh Kr Joshi]

Hi,

Can you please mention the link only to the file. There are several links in your post.

Thanks
Rajesh