Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro to create a folder and copy files
Hello,
Any help would be greatly appreciated: I am attempting to write a macro to do the following once a specific .xls file is opened: 1) Prompt the user to enter a folder name 2) Close that .xls file 3) Create a folder with the user entered name in a specific location 4) Copy the .xls file into the folder 5) Copy three (3) other .doc files to that folder 6) Open the copied .xls file As I said, any assistance would be greatly appreciated. Thank you, Wes |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro to create a folder and copy files
Sub DoStuff()
Dim sDir As String Dim aryDirs Dim i As Long Dim tmp As String sDir = InputBox("Supply folder name") aryDirs = Split(sDir, "\") tmp = aryDirs(LBound(aryDirs)) For i = LBound(aryDirs) + 1 To UBound(aryDirs) tmp = tmp & "\" & aryDirs(i) On Error Resume Next MkDir tmp On Error GoTo 0 Next i ThisWorkbook.SaveCopyAs tmp & "\" & ThisWorkbook.Name FileCopy "C:\Test\File 1.xls", tmp & "\" & "File 1.xls" FileCopy "C:\Test\File 2.xls", tmp & "\" & "File 2.xls" FileCopy "C:\Test\File 3.xls", tmp & "\" & "File 3.xls" End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "GainesvilleWes" <u32072@uwe wrote in message news:6e644b95c332c@uwe... Hello, Any help would be greatly appreciated: I am attempting to write a macro to do the following once a specific .xls file is opened: 1) Prompt the user to enter a folder name 2) Close that .xls file 3) Create a folder with the user entered name in a specific location 4) Copy the .xls file into the folder 5) Copy three (3) other .doc files to that folder 6) Open the copied .xls file As I said, any assistance would be greatly appreciated. Thank you, Wes |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Macro to create a folder and copy files
Bob,
You sir are a king among men. Many, many, many thanks. Bob Phillips wrote: Sub DoStuff() Dim sDir As String Dim aryDirs Dim i As Long Dim tmp As String sDir = InputBox("Supply folder name") aryDirs = Split(sDir, "\") tmp = aryDirs(LBound(aryDirs)) For i = LBound(aryDirs) + 1 To UBound(aryDirs) tmp = tmp & "\" & aryDirs(i) On Error Resume Next MkDir tmp On Error GoTo 0 Next i ThisWorkbook.SaveCopyAs tmp & "\" & ThisWorkbook.Name FileCopy "C:\Test\File 1.xls", tmp & "\" & "File 1.xls" FileCopy "C:\Test\File 2.xls", tmp & "\" & "File 2.xls" FileCopy "C:\Test\File 3.xls", tmp & "\" & "File 3.xls" End Sub Hello, Any help would be greatly appreciated: [quoted text clipped - 10 lines] Thank you, Wes |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a copy of a folder having five files in it, & rename | Excel Worksheet Functions | |||
Save copy of folder with linked files | Excel Worksheet Functions | |||
Macro to copy range from Excel files in folder | Excel Discussion (Misc queries) | |||
create a list of worksheet names (from a single folder, or open files) | Excel Discussion (Misc queries) | |||
Copy files from spreadsheet into folder | Excel Discussion (Misc queries) |