Copy worksheets of files o different directories into one workbook

[rence19]

hello friends,

kindly help me with this.i need a macro for my project.

i want to have a single file which contains all worksheets from multiple workbooks of the same filename but different worksheets. this files have different directories.

say:

file1.xls (worksheet names here is "aaa,abc") in d:\\my_file
file1.xls(worksheet names here is "bbb,bdg") in c:\\my_saved_files
.
.
.file1.xls(worksheet names here is "zzz,yyy" ) in d:\\my_other_file
.
.

appreciate all the help!

 

[bertie]

Try this:

fname = Trim(ThisWorkbook.Worksheets("Sheet1").Cells(5, 4).value)

The TRIM function removes spaces at the beginning and end of the cells contents.

Two things to note. You will need to test this cell contains a value:

If fname = "" then
  [COLOR=green] 'message[/COLOR]
   Exit Sub
End If

Sheet names cannot contain special characters like: commas, quotations, etc. If this could arise you may need code to remove special charatcers from the cells value.

 

[rence19]

hi mate,

As I understand, every time I need to have the consolidated file, I repeatedly do same routine for every different sets of "file1.xls". That i will open new excel workbook>> alt-f11>> and paste the code in "ThisWorkbook".

I need it to work in such a way that:
I will place the code in "MODULE" in my workbook named "my_macro_files.xls".

In this "my_macro_files.xls" I have a command button and drop-down list. This drop-down list have the list of my different filenames for "file1.xls" .

In the command button, i will "assign the macro" that every I select from drop- down list (this is for variable "file1.xls") and click the command button, i will get same results.

I tried to use the following as suggested:

fname = Trim(ThisWorkbook.Worksheets("Sheet1").Cells(5, 4).value)
aPath = Array("c:\temp\" & fname & ".xls", _
"c:\temp\sub-folder1\" & fname & ".xls", _
"c:\temp\sub-folder2\" & fname & ".xls")

and still ended up having "my_macro_files.xls" saved in the directory and not the "file1.xls" file that I usually obtain when i do:

"open new excel workbook>> alt-f11>> and paste the code in "ThisWorkbook"


appreciate your kindness and assistance.

 

[bertie]

Open a new Excel workbook.
Press Alt+F11.
Click Insert => Module
Copy and paste the code in post #9 into this module.

Save the file and test the code until you are satisfied that it meets your needs.

Once you are happy that the code does what you want:
right click the module you inserted and select Export File,
i.e., save the module to your hard drive.

Open the file where you store your other macros.
Right click Modules and select Import Files, i.e., import the code module into your macro workbook.

The ImportWorksheets procedure will now be available.

 

[rence19]

hi Bertie,

This works perfect for me now.

Just one last query. In the consolidated file, there appears: sheet1,sheet2 and sheet3 which are not to be included. How do i manage to delete those unnecessary sheets?


thanks!!!!

 

[rence19]

hi bertie,

just two more additional query

1. The workbook "my_macro_files.xls" closes when i click the command button.
although i have the consolidated file correctly. How not to auto close "my_macro_files.xls" it so that i can perform for other files without opening "my_macro_files.xls" again and again?

2. i checked the consolidated file and found out that i contains the macro.
would it be possible that the consolidated file has no macro contained in it?

please advice.

thanks thanks!

 

[bertie]

I have amended the code to add a new workbook and copy the worksheets there. This will take care of both points in your last post.

As for excluding worksheets see the IF statement (highlighted). I have not tested this part of the code, worksheet names comflict with test files, but it will give you an idea how to handle this.


Edit: I have commented out the KILL statement so you can test the code.

[COLOR=darkblue]Sub[/COLOR] ImportWorksheets2()
   [COLOR=darkblue]Dim[/COLOR] aPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]    [COLOR=green]'array of file paths[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] wb [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] wbTemp [COLOR=darkblue]As[/COLOR] Workbook
   [COLOR=darkblue]Dim[/COLOR] wsTemp [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]        [COLOR=green]'loop index[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]     'file to open
   [COLOR=darkblue]Dim[/COLOR] counter [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]     [COLOR=green]'append to sheet name for unique name[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] fName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
 
   [COLOR=green]'==============================[/COLOR]
   [COLOR=green]'populate an array of filenames[/COLOR]
   [COLOR=green]'===============================[/COLOR]
   fName = ThisWorkbook.Sheets("Sheet1").Cells(5, 4).Value
   [COLOR=green]'array of file paths[/COLOR]
   aPath = Array("c:\temp\" & fName & ".xls", _
                 "c:\temp\sub-folder1\" & fName & ".xls", _
                 "c:\temp\sub-folder2\" & fName & ".xls")
 
   [COLOR=green]'cater for the file doesn't exist[/COLOR]
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
 
   [COLOR=green]'==================================[/COLOR]
   [COLOR=green]'add a new workbook with one sheet[/COLOR]
   [COLOR=green]'=================================[/COLOR]
   Workbooks.Add 1
   [COLOR=darkblue]Set[/COLOR] wbTemp = ActiveWorkbook
   [COLOR=darkblue]Set[/COLOR] wsTemp = wbTemp.Sheets("Sheet1")
 
   [COLOR=green]'============================[/COLOR]
   [COLOR=green]'copy sheets to new workbook[/COLOR]
   [COLOR=green]'============================[/COLOR]
   [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](aPath) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](aPath)
      [COLOR=green]'open each workbook in the array[/COLOR]
      [COLOR=green]'and copy the worksheets[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = Workbooks.Open(aPath(i))
      [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ws [COLOR=darkblue]In[/COLOR] wb.Worksheets
[COLOR=red]       '========================[/COLOR]
[COLOR=red]       'exclude worksheets[/COLOR]
[COLOR=red]       '========================[/COLOR]
         [COLOR=darkblue]If[/COLOR] ws.Name <> "xxx" And ws.Name <> "yyy" [COLOR=darkblue]Then[/COLOR]
            counter = counter + 1
            ws.Copy After:=wbTemp.Sheets(wbTemp.Sheets.Count)
            ActiveSheet.Name = ws.Name & "_" & counter
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
      [COLOR=darkblue]Next[/COLOR] ws
 
      [COLOR=green]'close the workbook[/COLOR]
      wb.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
 
      [COLOR=green]'delete the data source[/COLOR]
     [COLOR=green]'Kill aPath(i)[/COLOR]
      [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
 [COLOR=green]'=======================[/COLOR]
 [COLOR=green]'close the new workbook[/COLOR]
 [COLOR=green]'=======================[/COLOR]
   fName = "c:\my_database\" & fName & ".xls"
   [COLOR=darkblue]With[/COLOR] wbTemp
      .SaveAs fName
      .Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
 
   [COLOR=green]'tidy up[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wbTemp = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] ws = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wb = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[rence19]

hello mate.

i just arrived from a long vacation trip.
just tested the code this day and i suit well on my need.

THANK YOU SO MUCH!!!!

CHEERS!!