Please I need some help to complete a VBA Macro

[Francesco]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>

[matis]

try this ..
If Dir("C:\InvoicingPrg\Invoice2006", vbDirectory) <> "" Then
end sub '(or doWhatEver)
else
chdir "C:\InvoicingPrg"
mkdir "Invoice2006"
end if


hth,
matis

[Gizmo63]

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
>
>
>

[Francesco]

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
> >
> >
> >

[Francesco]

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
> >
> >
> >

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> > >
> > >
> > >

[Francesco]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > > >
> > > >
> > > >
>
>
>