Combining files

[powerstar]

Hi,
Can anybody please help me in doing this?
I have to combine a number of excel files into one.
Number of columns does not change but number of rows change from file to file.
The names of the files are different .
All the files will be in a folder.

Iam running Microsoft Excel 2003.

thanks in advance!

[sanereddy]

Here it is, might need bit tweaking, I haven\'t tested the code on my pc. it\'s simple vbscript code.

put all the files that has to be merged in c:\\temp directory.
open a new workbook hit alt+f11 this opens vbe window select modules form insert menu, paste the code below, close the vbe window. in xl hit alt+f8 select scopy click run

Sub shcopy()
Dim xlBook As Excel.Workbook
Dim tBook As Excel.Workbook
Dim xlSht As Excel.Worksheet
Set tBook = ThisWorkbook
fName = Dir(\"c:\\temp\\*.xls\&quot
aa = 1
Do While (fName <> \"\&quot
Set xlBook = Workbooks.Open(\"c:\\temp\\\" & fName)
Set xlSht = xlBook.Sheets(1)
xlSht.UsedRange.Copy
tBook.Activate
Cells(aa, 1).Select
ActiveSheet.Paste
ActiveSheet.UsedRange.Select
aa = Selection.Rows.Count + 2
xlBook.Save
xlBook.Close
fName = Dir
Loop
Set xlBook = Nothing
Set xlSht = Nothing
Set tBook = Nothing
End Sub

if you would like to copy many tabs from into one spreadhseet within one excel file...
Sub CombineFilesInFolder()
Dim pathname As String
Dim filename As String
Dim rng As Range
dim tmprng as Range

Dim thiswrk As Workbook
Dim thissht As Worksheet

Dim wrk As Workbook
Dim sht As Worksheet

Set thiswrk = ThisWorkbook
Set thissht = thiswrk.Sheets(1)

Application.ScreenUpdating = False
pathname = \"\\\\Globasrvr\\globafone\\Billing\\OrangeBillingTools\\Orange_Excel_Sheets_From_Dean\\\"
filename = Dir(pathname & Application.PathSeparator & \"*.xls\&quot
Do Until filename = \"\"
Set wrk = Workbooks.Open(pathname & Application.PathSeparator & filename)
Set sht = wrk.Sheets(1)
Set tmprng = sht.UsedRange
Set rng = sht.UsedRange
rng.Copy thissht.Cells(thissht.UsedRange.Rows.Count + 1, 1)
wrk.Close False
filename = Dir()
Loop
Set sht = Nothing
Set wrk = Nothing

Application.ScreenUpdating = True
End Sub


Hope this helps.

[powerstar]

Sanereddy,
I copied your code and ran the macro.It didnt work.The sheet remains balnk.
iam sorry.I dont know anything about VB so i could not even make minor modifications to your code.
Iam a peoplesoft developer and iam generating these excel files using nVision.
Could you please check the macro for any modifications to be made?

thank you very much.

[powerstar]

I see 2 procedures in your code

1)Sub shcopy()
2)Sub CombineFilesInFolder()

But we r running ONLY shcopy() procedure.I dont see any call to the procedure
CombineFilesInFolder() from shcopy().
do we need to call it?

[sanereddy]

Download tthis excel file, put all the files in c:\\temp folder,

al+f8 to run,

there are 2 msgbox commands in the code, you can take them out after testing.

discussion board stripped few symbols from the code.

hope this helps.

vamsee

Post edited by: sanereddy, at: 2004/11/19 23:03

[powerstar]

I have used only shcopy() procedure but still it is not working.
Please help me.

[sanereddy]

donwload this and rename to .xls file. tested it, works. [file name=Merge.xls.txt size=24064]http://www.exforsys.com/components/com_simpleboard/uploaded/files/Merge.xls.txt[/file]

[powerstar]

What I want to do is not a one time process.
it needs to be done every month end.and it is done by end users who doesnt want to install anything on their machines.

Thank you so much for your help.

[sanereddy]

use this excel file for testing , there is no need to install any.

for logterm to do this on monthly basic, then basically we need to do this visual basic and compile as executable.

[powerstar]

I worked partially.
It copied ONLY one file and giving this error :400.

I really appreciate your help.