Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move File
I have a macro that goes into a folder, grabs all excel documents and
combines into one workbook. I want to move that particular file after its combine to the master file. Basically, I have two folders 1. New 2 Old I use the code below. Once I use the file I would like to move it to the Old Folder. So, the next day it will be ready for the next group of files. Thanks Application.EnableEvents = False Application.ScreenUpdating = False Path = "c:\new" ''Change as needed FileName = Dir(Path & "\*.xls", vbNormal) Do Until FileName = "" Set Wkb2 = Workbooks.Open(FileName:=Path & "\" & FileName) For Each WS In Wkb2.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt) Next WS Wkb2.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move File
You can insert a code like this at the beginning:
Dim fso Set fso = CreateObject("Scripting.FileSystemObject") PathOld = "c:\old\" 'Change as needed And then after you close the file: Wkb2.Close False fso.MoveFile Path & "\" & FileName, PathOld FileName = Dir() Be sure to have backup of your files before testing the code, just in case. Hope this helps, Miguel. "THE_RAMONES" wrote: I have a macro that goes into a folder, grabs all excel documents and combines into one workbook. I want to move that particular file after its combine to the master file. Basically, I have two folders 1. New 2 Old I use the code below. Once I use the file I would like to move it to the Old Folder. So, the next day it will be ready for the next group of files. Thanks Application.EnableEvents = False Application.ScreenUpdating = False Path = "c:\new" ''Change as needed FileName = Dir(Path & "\*.xls", vbNormal) Do Until FileName = "" Set Wkb2 = Workbooks.Open(FileName:=Path & "\" & FileName) For Each WS In Wkb2.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt) Next WS Wkb2.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move File
I think we may be close... However I get an error saying file already exist.
However there are no files in the folder.. Any thoughts? Thanks "Miguel Zapico" wrote: You can insert a code like this at the beginning: Dim fso Set fso = CreateObject("Scripting.FileSystemObject") PathOld = "c:\old\" 'Change as needed And then after you close the file: Wkb2.Close False fso.MoveFile Path & "\" & FileName, PathOld FileName = Dir() Be sure to have backup of your files before testing the code, just in case. Hope this helps, Miguel. "THE_RAMONES" wrote: I have a macro that goes into a folder, grabs all excel documents and combines into one workbook. I want to move that particular file after its combine to the master file. Basically, I have two folders 1. New 2 Old I use the code below. Once I use the file I would like to move it to the Old Folder. So, the next day it will be ready for the next group of files. Thanks Application.EnableEvents = False Application.ScreenUpdating = False Path = "c:\new" ''Change as needed FileName = Dir(Path & "\*.xls", vbNormal) Do Until FileName = "" Set Wkb2 = Workbooks.Open(FileName:=Path & "\" & FileName) For Each WS In Wkb2.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt) Next WS Wkb2.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Move File
I have tried the code, and I cannot get the same error. This is the code I
used: Sub test() Application.EnableEvents = False Application.ScreenUpdating = False Dim fso Set fso = CreateObject("Scripting.FileSystemObject") PathOld = "c:\old\" 'Change as needed Path = "c:\new" ''Change as needed Filename = Dir(Path & "\*.xls", vbNormal) Do Until Filename = "" Set Wkb2 = Workbooks.Open(Filename:=Path & "\" & Filename) For Each WS In Wkb2.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Cou nt) Next WS Wkb2.Close False fso.MoveFile Path & "\" & Filename, PathOld Filename = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub Miguel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Move excel file to desk top? | Excel Discussion (Misc queries) | |||
Excel Outlook Attachments opening after several errors everytime | Setting up and Configuration of Excel | |||
Hyperlinks - Move file to local drive, all links break | Excel Discussion (Misc queries) | |||
Links picking up values from an older version of linked file | Links and Linking in Excel | |||
How do you open a template at startup? | Excel Discussion (Misc queries) |