Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
I am using excel 2003 and WindowsXp Professional
_______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Sub Auto_Open()
Dim MyPath As String Dim MyCell As String Dim Name As String ' puts the current in E1 ( ex. 10\04\2006) Cells(1, 5) = (Date) ' Inputs year 2006 of cell E1 in D5 Worksheets("output").Range("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5, 4).Value ' Create a folder On Error Resume Next MyPath = CurDir & "\" MkDir MyPath & "InvoicingPrg" 'Create subfolder MyPath = CurDir & "\InvoicingPrg\" MkDir MyPath & "Invoicing" & MyCell On Error GoTo 0 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Francesco" wrote in message ... I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
try this .. If Dir("C:\InvoicingPrg\Invoice2006", vbDirectory) < "" Then end sub '(or doWhatEver) else chdir "C:\InvoicingPrg" mkdir "Invoice2006" end if hth, matis -- matis ------------------------------------------------------------------------ matis's Profile: http://www.excelforum.com/member.php...o&userid=33326 View this thread: http://www.excelforum.com/showthread...hreadid=531483 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Hi Francesco,
delete the text in your macro from 'create a folder' to 'L1' and add in: Dim answer Dim fs Set fs = CreateObject("Scripting.FileSystemObject") answer = fs.folderexists("[your path to folder being checked for]") if answer = FALSE then fs.createfolder("[your path to folder you want to create]") this checks if your folder exists and if not creates it HTH Giz "Francesco" wrote: I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Hello
Thanks for the solution proposed, they solved my problem, it was wery nice from you all. one more question please Is there a nacro that checks how many drivers ( a:\ , C:\ , D:\ ......) are installed in the pc? Thanks again a lot Francesco "Gizmo63" wrote: Hi Francesco, delete the text in your macro from 'create a folder' to 'L1' and add in: Dim answer Dim fs Set fs = CreateObject("Scripting.FileSystemObject") answer = fs.folderexists("[your path to folder being checked for]") if answer = FALSE then fs.createfolder("[your path to folder you want to create]") this checks if your folder exists and if not creates it HTH Giz "Francesco" wrote: I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Gizmo63 the macro you wrote is perfect, may be you can suggest to me how to
insert it in a loop or a for next routine to fin out in which drive there is or there is not the folder and create or not it Thanks for again for help Francesco "Gizmo63" wrote: Hi Francesco, delete the text in your macro from 'create a folder' to 'L1' and add in: Dim answer Dim fs Set fs = CreateObject("Scripting.FileSystemObject") answer = fs.folderexists("[your path to folder being checked for]") if answer = FALSE then fs.createfolder("[your path to folder you want to create]") this checks if your folder exists and if not creates it HTH Giz "Francesco" wrote: I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Is this what you mean
Dim aryDrives Dim oFSO Dim sPath As String Dim i As Long aryDrives = Array("C:\", "D:\", "G:\") Set oFSO = CreateObject("Scripting.FileSystemObject") For i = LBound(aryDrives) To UBound(aryDrives) sPath = aryDrives(i) & "rest of path" If Not oFSO.folderexists(sPath) Then oFSO.createfolder sPath End If Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Francesco" wrote in message ... Gizmo63 the macro you wrote is perfect, may be you can suggest to me how to insert it in a loop or a for next routine to fin out in which drive there is or there is not the folder and create or not it Thanks for again for help Francesco "Gizmo63" wrote: Hi Francesco, delete the text in your macro from 'create a folder' to 'L1' and add in: Dim answer Dim fs Set fs = CreateObject("Scripting.FileSystemObject") answer = fs.folderexists("[your path to folder being checked for]") if answer = FALSE then fs.createfolder("[your path to folder you want to create]") this checks if your folder exists and if not creates it HTH Giz "Francesco" wrote: I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please I need some help to complete a VBA Macro
Hello,
Yes Bob Phillips that is exactly what I needed Thanks "Bob Phillips" wrote: Is this what you mean Dim aryDrives Dim oFSO Dim sPath As String Dim i As Long aryDrives = Array("C:\", "D:\", "G:\") Set oFSO = CreateObject("Scripting.FileSystemObject") For i = LBound(aryDrives) To UBound(aryDrives) sPath = aryDrives(i) & "rest of path" If Not oFSO.folderexists(sPath) Then oFSO.createfolder sPath End If Next i -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Francesco" wrote in message ... Gizmo63 the macro you wrote is perfect, may be you can suggest to me how to insert it in a loop or a for next routine to fin out in which drive there is or there is not the folder and create or not it Thanks for again for help Francesco "Gizmo63" wrote: Hi Francesco, delete the text in your macro from 'create a folder' to 'L1' and add in: Dim answer Dim fs Set fs = CreateObject("Scripting.FileSystemObject") answer = fs.folderexists("[your path to folder being checked for]") if answer = FALSE then fs.createfolder("[your path to folder you want to create]") this checks if your folder exists and if not creates it HTH Giz "Francesco" wrote: I am using excel 2003 and WindowsXp Professional _______ I prepared a macro to creade a folder and a subfolder but I do not know How to complete it What I wrote is: Sub Auto_Open() Dim MyPath as String Dim MyCell As String Dim Name AS String ' puts the current in E1 ( ex. 10\04\2006) Cells(1,5) = (Date) ' Inputs year 2006 of cell E1 in D5 WorkSheets("output").Range ("D5").Value = "=Year(E1)" ' MyCell takes the value of D5 MyCell = Sheets("output").Cells(5,4).Value ' Create a folder On Error GoTo L1 MyPath = CurDir & "\" MKDir MyPath & "InvoicingPrg" L1: 'Note: Up to here the Macro works fine and creates the folder in C:\ named "InvoicingPrg" and no errors occur even if the folder exists already. What I need is to finf out if within the folder "C:\InvoicingPrg" exists a foder named "Invoice2006" ( Name = "invoice" & MyCell ), if yes exit the sub otherwise creats the folder "Invoice2006". I do not now how to do this second part provided, I hope, that the first part of the Macro is correct. Thankyou all Francesco |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Closing File Error | Excel Discussion (Misc queries) | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |