re Merge

[Mark Roshak]

I used the link below and went through the steps. I
modified the script as shown. Where my excel files are in
a folder called test on the c drive. When I hit run
nothing happens what should I change to make it work?


http://www.vbaexpress.com/kb/getarticle.php?kb_id=221

_______________________________________________________
Option Explicit

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'test
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" &
FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets
(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub
_________________________________________________________

-----Original Message-----
Try this, roshak:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=221

**** Hope it helps! ****

~Dreamboat
Excel VBA Certification Coming Soon!
www.VBAExpress.com/training/
www.Brainbench.com Word Test Developer 2000,2002,2003
********************************



.

..

 

[Lady Layla]

According to what you have listed here, you set the path to C:\ -- If the files
are in test, shuldnt it be set to C:\test?


: I used the link below and went through the steps. I
: modified the script as shown. Where my excel files are in
: a folder called test on the c drive. When I hit run
: nothing happens what should I change to make it work?
:
:
: http://www.vbaexpress.com/kb/getarticle.php?kb_id=221
:
: _______________________________________________________
: Option Explicit
:
: Sub CombineFiles()
:
: Dim Path As String
: Dim FileName As String
: Dim Wkb As Workbook
: Dim WS As Worksheet
:
: Application.EnableEvents = False
: Application.ScreenUpdating = False
: Path = "C:\" 'test
: FileName = Dir(Path & "\*.xls", vbNormal)
: Do Until FileName = ""
: Set Wkb = Workbooks.Open(FileName:=Path & "\" &
: FileName)
: For Each WS In Wkb.Worksheets
: WS.Copy After:=ThisWorkbook.Sheets
: (ThisWorkbook.Sheets.Count)
: Next WS
: Wkb.Close False
: FileName = Dir()
: Loop
: Application.EnableEvents = True
: Application.ScreenUpdating = True
:
: End Sub
: _________________________________________________________
:
:
: >-----Original Message-----
: >Try this, roshak:
: >
: >http://www.vbaexpress.com/kb/getarticle.php?kb_id=221
: >
: >**** Hope it helps! ****
: >
: >~Dreamboat
: >Excel VBA Certification Coming Soon!
: >www.VBAExpress.com/training/
: >www.Brainbench.com Word Test Developer 2000,2002,2003
: >********************************
: >: >> I have 30 different excel 2003 documents used for data
: >> entry I want to merge them into one document. What is
: the
: >> best way to do that?
: >
: >
: >.
: >
: .
:
:

 

[Sarah Lu [MSFT]]

Hi Mark,

You need to make the following change:

Path = "C:\" 'test

needs to be changed to:

Path = "C:\test"

If you have any other question regarding programming, please post it in
microsoft.public.excel.programming or the MSDN newsgroup which can be
accessed below:

http://msdn.microsoft.com/newsgroups/default.asp


Have a nice day!

Best regards,

Sarah Lu
Microsoft Online Partner Support
Get Secure! - <www.microsoft.com/security>
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.