RompStar
05-25-2005, 02:22 PM
Hi there :hi:
My first real post here :- )
Ok, on my local network \\
I have 5 different Excel.xls documents,
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
there the part -doc.xls is different for each document...
in each document there is a single sheet, each one names differently and of course I know their names...
EAch doc is exactly the same in desigh, has 4 columns, A, B, C, D
A: has Department Name
B: has Date
C: has Employee Name
D: Daily Status
A1:D1 has headers (see above) and everything below that starting at
A2:D2 is data...
So, there are 5 different documents, I simply want to merge them all into a single sheet, keep the headers and paste the data... only select data filled, ignore blank rows below last used...
I need to do this everyday.... so manual cut and paste takes a good 10 minutes and I don't want to waste my time, as I have so many things to do plus since I am learning VBA, I would like to learn how to do this for other things I am sure I will have to do...
So I guess I need a VBA script that will go and grab these files from the network and paste them into a Master Append document that holds them all in a single sheet...
That's my goal for now :- )
All the files that I want start with:
appendfile-*.xls so that's why I referenced it like that... so I need to grab all those workbook sheets and transfer them over to a single sheet, not individual sheet in a new workbook, any ideas ?
I tried to use this script that I found on your network...: I tried to use \*\ so it look in all the folders in that path, but I don't think that work, does that mean I have to spell out all 5 paths ?
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 = \\local\network\path\* (file:///localnetworkpath*)\ (file:///DIST-ROLAND)
FileName = Dir(Path & "\appendfile-*.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
:help
I'll buy u a beer if you help me :- ) :rotlaugh:
:beerchug:
My first real post here :- )
Ok, on my local network \\
I have 5 different Excel.xls documents,
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
appendfile-doc.xls
there the part -doc.xls is different for each document...
in each document there is a single sheet, each one names differently and of course I know their names...
EAch doc is exactly the same in desigh, has 4 columns, A, B, C, D
A: has Department Name
B: has Date
C: has Employee Name
D: Daily Status
A1:D1 has headers (see above) and everything below that starting at
A2:D2 is data...
So, there are 5 different documents, I simply want to merge them all into a single sheet, keep the headers and paste the data... only select data filled, ignore blank rows below last used...
I need to do this everyday.... so manual cut and paste takes a good 10 minutes and I don't want to waste my time, as I have so many things to do plus since I am learning VBA, I would like to learn how to do this for other things I am sure I will have to do...
So I guess I need a VBA script that will go and grab these files from the network and paste them into a Master Append document that holds them all in a single sheet...
That's my goal for now :- )
All the files that I want start with:
appendfile-*.xls so that's why I referenced it like that... so I need to grab all those workbook sheets and transfer them over to a single sheet, not individual sheet in a new workbook, any ideas ?
I tried to use this script that I found on your network...: I tried to use \*\ so it look in all the folders in that path, but I don't think that work, does that mean I have to spell out all 5 paths ?
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 = \\local\network\path\* (file:///localnetworkpath*)\ (file:///DIST-ROLAND)
FileName = Dir(Path & "\appendfile-*.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
:help
I'll buy u a beer if you help me :- ) :rotlaugh:
:beerchug: