Please I need some help to complete a VBA Macro

[Guest]

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)

 

[matis]

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


hth,
mati

 

[Guest]

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

 

[Guest]

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

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

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

 

[Guest]

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

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

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)

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

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

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

 

[Guest]

Hello,
Yes Bob Phillips that is exactly what I needed
Thanks

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)

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

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



:

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