Excel Macro-- How to Write a Path

[vrussell]

Please help!
How do I write this path? I have an Excel Workbook with 12 worksheets. I would like to combine all 12 worksheets into one workbook. The code used by a friend is:

[VBA]
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:\" 'Change as needed
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
[/VBA]
use vba tags when posting to make your code more readable. I have added them to this post for you...just select your code when posting and hit the button marked vba. Hope thats ok.

However, I can't get the path to work. Here's my path that needs to be rewritten for the macro to work.

D:\My Documents\Word\2006 Georgia Outshopping\Practice and the only file there is the Excel file I want to change called OutshoppingReg01.xls

Thank you for your help!!!!
Vicki

[lucas]

Hi Vicki,
This works fine for me, can you tell me what happens where you say you can't get the path to work?

[lucas]

I think you need a backslash at the end of your path
[VBA]
Path = "C:\Temp\test\" 'Change as needed
[/VBA]

[lucas]

You should probably put your files in a different directory under the root...instead of "C:\"

[jungix]

Also make sure you don't forget the " " around your path. This kind of stuff can make you lose a lot of time sometimes.

[vrussell]

Thank you!!!

[vrussell]

Thanks to everyone for your replies. I am a newbie, so thank you also for being patient with me!

I've tried everyone's suggestions. Actually the macro runs (spins) and the Excel file opens with Worksheet 1, 2, 3 added at the front (they are blank), then the rest of the 12 worksheets follow just as they were. In other words, the worksheets are not combined into one workbook. Any other suggestions?
Thank you,
Vicki

[lucas]

Hi Vicki,
I ran this again and used this line for the path and it worked. Note I left off the backslash at the end this time. Also I have a root drive of F where yours is C but just make sure your path is correct. If this continues to be an issue let us know.
[VBA]
Path = "F:\Temp\test" 'Change as needed
[/VBA]

the Excel file opens with Worksheet 1, 2, 3 added at the front (they are blank), then the rest of the 12 worksheets follow just as they were. In other words,

The three blank ones were probably already in the workbook before you started....delete all but one and give it a specific name. This script is also importing all sheets with data and blank sheets.

the worksheets are not combined into one workbook. Any other suggestions?

The worksheets are being combined into one workbook but from the statement above I'm led to believe you wish to combine them to one worksheet......can you clarify?

If importing the blank sheets is a problem I think there is a recent kb entry that imports worksheets with data only, no blanks. It has a browse to directory dialog instead of the hard coded path. Let me know if your interested and I will help you locate it.

[vrussell]

Yes, you are correct, I would like all 12 worksheets to be one worksheet, sorry! In oher words, I have 12 worksheets and would rather have 1 worksheet containing all the former tabs.

[lucas]

Ok Vickie, lets establish our steps.
import multiple worksheets from closed workbooks in a directory.
merge all of these sheets into one sheet

what about blank sheets?

[vrussell]

I just spoke with the person who told me this morning that the code would combine all 12 worksheets into one worksheet. She said, she gave me the wrong answer/code. I told her, no I wanted to put all 12 into one!

Anyway, I'm sorry I have wasted everyone's time. Thank you especially to Lucas and Anne. I hope it's correct to say your names. I'm headed home for a cold one! My head hurts! Again, I apologize for this confusion! Hope you all have a nice weekend!
Vicki