Creating directories!

[Leslie Isaacs]

Hello All

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Rick B]

The newsgroup to which you posted your question is an Access newsgroup. We
answer questions about the database application MICROSOFT ACCESS. This
is not a Windows newsgroup.

You should probably get in the habit of looking at a few posts before you
add a new thread to a newsgroup. This newsgroup has nothing to do with your
question.

I'm betting if you search the appropriate newsgroup, or google, you might
even find an answer to your question without having to ask someone to repeat
it for you.

Rick B

 

[J. Clay]

In VBA help, look up FileSystemObject and its CreateFolder method.

HTH,
J. Clay

 

[Joel]

Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel

 

[Douglas J. Steele]

Rick: Why are you assuming this isn't related to Access?

Re-read the following (especially the 2nd last word) from the original post:

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

The mention of "table" means that it stands a good chance of being Access
related.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The newsgroup to which you posted your question is an Access newsgroup.
We
answer questions about the database application MICROSOFT ACCESS. This
is not a Windows newsgroup.

You should probably get in the habit of looking at a few posts before you
add a new thread to a newsgroup. This newsgroup has nothing to do with
your
question.

I'm betting if you search the appropriate newsgroup, or google, you might
even find an answer to your question without having to ask someone to
repeat
it for you.

Rick B

Hello All

I need to create a new directory on my PC (under My Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Rick

Thank you for your reply.
Sorry: I should have said that the folder names that I need are in fact the
values of the field [member_name] in the table [members] in my A97 mdb.
Many thanks
Les.

The newsgroup to which you posted your question is an Access newsgroup. We
answer questions about the database application MICROSOFT ACCESS. This
is not a Windows newsgroup.

You should probably get in the habit of looking at a few posts before you
add a new thread to a newsgroup. This newsgroup has nothing to do with your
question.

I'm betting if you search the appropriate newsgroup, or google, you might
even find an answer to your question without having to ask someone to repeat
it for you.

Rick B

Hello All

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello J. Clay

Thank you for your reply.

I have looked at FileSystemObject and its CreateFolder method in VBA Help,
as you suggested, and it certainly looks like this is what I need, but I
don't understand how to use it. Where the syntax is given as:

object.CreateFolder(foldername)

and it says that the 'object' is the name of a FileSystemObject, I'm not
sure what to put.

The values that I want to use as folder names would in fact be the
concatenation (I think that's the right word!) of two fields in my Members
table - i.e. [member_serial]&[member_name]. Should the FileSystemObject be
the name of the table - perhaps preceded by the path and name of the mdb? So
I am guessing (wildly!) that the command I need is something like:

C:\Documents and Settings\lisaacs\club
admin.mdb:[tables]![members].CreateFolder([member_serial]&[member_name])

Hope you don't mind my ignorance, and that you can help me finish this off.

Many thanks,
Les

In VBA help, look up FileSystemObject and its CreateFolder method.

HTH,
J. Clay

Hello All

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to create
directories with names taken from my access table? And can they all (around
120 of them) be created at once, or must it be one at a time (in which case
I might as well do it manually!)?

Thanks for your continued help.
Les.

Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel

Hello All

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Rich Skruch]

Hello J. Clay

Thank you for your reply.

I have looked at FileSystemObject and its CreateFolder method in VBA Help,
as you suggested, and it certainly looks like this is what I need, but I
don't understand how to use it. Where the syntax is given as:

object.CreateFolder(foldername)

and it says that the 'object' is the name of a FileSystemObject, I'm not
sure what to put.

The values that I want to use as folder names would in fact be the
concatenation (I think that's the right word!) of two fields in my Members
table - i.e. [member_serial]&[member_name]. Should the FileSystemObject be
the name of the table - perhaps preceded by the path and name of the mdb? So
I am guessing (wildly!) that the command I need is something like:

C:\Documents and Settings\lisaacs\club
admin.mdb:[tables]![members].CreateFolder([member_serial]&[member_name])

Hope you don't mind my ignorance, and that you can help me finish this off.

Many thanks,
Les

You need to create the FileSystemObject, as in:

Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
set f = fs.CreateFolder("C:\My Folder")


I've included a short example. Change "C:\Temp\" to whatever base
folder you need.

Rich.

-----------------

Public Sub MakeFolders()

Dim sFolder as String
Dim rst As DAO.Recordset
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")

Set rst = CurrentDb.OpenRecordset("members")

Do While Not rst.EOF

sFolder=rst![member_serial] & "-" & rst![Member_Name]
Set f = fs.CreateFolder("C:\Temp\" & sFolder)
rst.MoveNext

Loop

rst.Close
Set f = Nothing
Set rst = Nothing
Set fs = Nothing

End Sub

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.

Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel

Hello All

I need to create a new directory on my PC (under My Documents>Members) for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the filed name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

.... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive - I
even copied and pasted the path (C:\New Folder) from the address bar at the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.

Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My

Documents>Members)
for

each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Douglas J. Steele]

Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive -
I
even copied and pasted the path (C:\New Folder) from the address bar at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Douglas

Many thanks for persevering with this!

I took the Chr$(34) out, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub

... but I still get the same error.

In case the problem was somethinbg to do with the fact that my field name in
Practices has a space (which is why I put the square brackets round it), I
tried another field name instead of [prac name] - one without the space, and
removed the square brackets around it, but again I got the same error.

What else can it be?
Hope you'll stay with me on this!

Les.

Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive -
I
even copied and pasted the path (C:\New Folder) from the address bar at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Rich

Many thanks for your help - your code worked a treat!

I'm just curious (trying to learn!): what is the reason for
Set f = Nothing
Set rst = Nothing
Set fs = Nothing
Surely even without these statements, f, rst & fs would be re-set each time
the procedure is called?

Thanks again
Les.

Hello J. Clay

Thank you for your reply.

I have looked at FileSystemObject and its CreateFolder method in VBA Help,
as you suggested, and it certainly looks like this is what I need, but I
don't understand how to use it. Where the syntax is given as:

object.CreateFolder(foldername)

and it says that the 'object' is the name of a FileSystemObject, I'm not
sure what to put.

The values that I want to use as folder names would in fact be the
concatenation (I think that's the right word!) of two fields in my Members
table - i.e. [member_serial]&[member_name]. Should the FileSystemObject be
the name of the table - perhaps preceded by the path and name of the mdb? So
I am guessing (wildly!) that the command I need is something like:

C:\Documents and Settings\lisaacs\club
admin.mdb:[tables]![members].CreateFolder([member_serial]&[member_name])

Hope you don't mind my ignorance, and that you can help me finish this off.

Many thanks,
Les

You need to create the FileSystemObject, as in:

Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
set f = fs.CreateFolder("C:\My Folder")


I've included a short example. Change "C:\Temp\" to whatever base
folder you need.

Rich.

-----------------

Public Sub MakeFolders()

Dim sFolder as String
Dim rst As DAO.Recordset
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")

Set rst = CurrentDb.OpenRecordset("members")

Do While Not rst.EOF

sFolder=rst![member_serial] & "-" & rst![Member_Name]
Set f = fs.CreateFolder("C:\Temp\" & sFolder)
rst.MoveNext

Loop

rst.Close
Set f = Nothing
Set rst = Nothing
Set fs = Nothing

End Sub

 

[Douglas J. Steele]

Works fine for me...

First, make sure that your query is returning what you think it should.
Replace

MkDir "C:\New Folder" & rsCurr![prac name]

with

Debug.Print "C:\New Folder\" & rsCurr![prac name]

then look in the Immediate window (Ctrl-G) to see that it's correct.

Next, try going into the immediate window and manually run

MkDir "C:\New Folder\Leslie"

and see whether it creates a folder for you.

BTW, assuming you want this to produce subfolders underneath C:\New Folder,
you need a terminating slash:

MkDir "C:\New Folder\" & rsCurr![prac name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Many thanks for persevering with this!

I took the Chr$(34) out, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub

.. but I still get the same error.

In case the problem was somethinbg to do with the fact that my field name
in
Practices has a space (which is why I put the square brackets round it), I
tried another field name instead of [prac name] - one without the space,
and
removed the square brackets around it, but again I got the same error.

What else can it be?
Hope you'll stay with me on this!

Les.

Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive -
I
even copied and pasted the path (C:\New Folder) from the address bar at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.




message
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM
Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to
create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My
Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure
there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Hello Rich

Having said that your code worked a treat, that was when I was on my 'other' PC: now I'm back on the main PC it won't work!!

The code I now have is:
Public Sub MakeFolders()

Dim sFolder As String
Dim rst As DAO.Recordset
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")
Set rst = CurrentDb.OpenRecordset("practices")

Do While Not rst.EOF

sFolder = rst![prac name]
Set f = fs.CreateFolder("C:\Clients\" & sFolder)
rst.MoveNext

Loop

rst.Close
Set f = Nothing
Set rst = Nothing
Set fs = Nothing

End Sub

.... and this keeps giving me:
Run-time error 52
Bad command or file number
with the following line:
Set f = fs.CreateFolder("C:\Clients\" & sFolder)
highlighted in yellow.

I even tried using:
Set f = fs.CreateFolder("C:\" & sFolder)
for the destination, but got the same result.

Hope you can advise.
Many thanks
Les

Hello J. Clay

Thank you for your reply.

I have looked at FileSystemObject and its CreateFolder method in VBA Help,
as you suggested, and it certainly looks like this is what I need, but I
don't understand how to use it. Where the syntax is given as:

object.CreateFolder(foldername)

and it says that the 'object' is the name of a FileSystemObject, I'm not
sure what to put.

The values that I want to use as folder names would in fact be the
concatenation (I think that's the right word!) of two fields in my Members
table - i.e. [member_serial]&[member_name]. Should the FileSystemObject be
the name of the table - perhaps preceded by the path and name of the mdb? So
I am guessing (wildly!) that the command I need is something like:

C:\Documents and Settings\lisaacs\club
admin.mdb:[tables]![members].CreateFolder([member_serial]&[member_name])

Hope you don't mind my ignorance, and that you can help me finish this off.

Many thanks,
Les

You need to create the FileSystemObject, as in:

Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
set f = fs.CreateFolder("C:\My Folder")


I've included a short example. Change "C:\Temp\" to whatever base
folder you need.

Rich.

-----------------

Public Sub MakeFolders()

Dim sFolder as String
Dim rst As DAO.Recordset
Dim fs, f

Set fs = CreateObject("Scripting.FileSystemObject")

Set rst = CurrentDb.OpenRecordset("members")

Do While Not rst.EOF

sFolder=rst![member_serial] & "-" & rst![Member_Name]
Set f = fs.CreateFolder("C:\Temp\" & sFolder)
rst.MoveNext

Loop

rst.Close
Set f = Nothing
Set rst = Nothing
Set fs = Nothing

End Sub

 

[Douglas J. Steele]

I'll answer for Rich.

It's considered good practice to clean up after yourself, in an attempt to
prevent memory leaks. You're correct that the objects in question should be
deleted from memory when the code finished, but many of us prefer to ensure
it happens, rather than hope it will!

 

[Leslie Isaacs]

Douglas

This is getting strange!

This works fine
MkDir "C:\New Folder\Leslie"
and this worked fine:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
Debug.Print "C:\New Folder\" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub
(looking in (Ctrl-G) showed all the expected names)

But this doesn't work:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder\Leslie"
rsCurr.MoveNext
Loop
End Sub
I get:
Run-time error '75'
Path/file access error

If I try it with:
MkDir "C:\New Folder\" & rsCurr![prac name]
I get:
Run-time error '76'
Path not found
I very much hope something in all this means something to you, because I'm bewildered!
Many thanks again
Les.

Works fine for me...

First, make sure that your query is returning what you think it should.
Replace

MkDir "C:\New Folder" & rsCurr![prac name]

with

Debug.Print "C:\New Folder\" & rsCurr![prac name]

then look in the Immediate window (Ctrl-G) to see that it's correct.

Next, try going into the immediate window and manually run

MkDir "C:\New Folder\Leslie"

and see whether it creates a folder for you.

BTW, assuming you want this to produce subfolders underneath C:\New Folder,
you need a terminating slash:

MkDir "C:\New Folder\" & rsCurr![prac name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Many thanks for persevering with this!

I took the Chr$(34) out, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub

.. but I still get the same error.

In case the problem was somethinbg to do with the fact that my field name
in
Practices has a space (which is why I put the square brackets round it), I
tried another field name instead of [prac name] - one without the space,
and
removed the square brackets around it, but again I got the same error.

What else can it be?
Hope you'll stay with me on this!

Les.

Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive -
I
even copied and pasted the path (C:\New Folder) from the address bar at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.




message
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM
Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to
create
directories with names taken from my access table? And can they all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My
Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm sure
there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Douglas J. Steele]

The Error 75 is probably because C:\New Folder\Leslie already exists. Try
deleting the folder, then running

Sub makedirs()

MkDir "C:\New Folder\Leslie"

End Sub

just to see whether it's a VBA issue or not.


Sorry, but I'm out of ideas. As I said, it works fine for me.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas

This is getting strange!

This works fine
MkDir "C:\New Folder\Leslie"
and this worked fine:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
Debug.Print "C:\New Folder\" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub
(looking in (Ctrl-G) showed all the expected names)

But this doesn't work:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder\Leslie"
rsCurr.MoveNext
Loop
End Sub
I get:
Run-time error '75'
Path/file access error

If I try it with:
MkDir "C:\New Folder\" & rsCurr![prac name]
I get:
Run-time error '76'
Path not found
I very much hope something in all this means something to you, because I'm
bewildered!
Many thanks again
Les.

Works fine for me...

First, make sure that your query is returning what you think it should.
Replace

MkDir "C:\New Folder" & rsCurr![prac name]

with

Debug.Print "C:\New Folder\" & rsCurr![prac name]

then look in the Immediate window (Ctrl-G) to see that it's correct.

Next, try going into the immediate window and manually run

MkDir "C:\New Folder\Leslie"

and see whether it creates a folder for you.

BTW, assuming you want this to produce subfolders underneath C:\New
Folder,
you need a terminating slash:

MkDir "C:\New Folder\" & rsCurr![prac name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Many thanks for persevering with this!

I took the Chr$(34) out, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub

.. but I still get the same error.

In case the problem was somethinbg to do with the fact that my field
name
in
Practices has a space (which is why I put the square brackets round it),
I
tried another field name instead of [prac name] - one without the space,
and
removed the square brackets around it, but again I got the same error.

What else can it be?
Hope you'll stay with me on this!

Les.

Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the
filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM
Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C drive -
I
even copied and pasted the path (C:\New Folder) from the address bar
at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.




message
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM
Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to
create
directories with names taken from my access table? And can they
all
(around
120 of them) be created at once, or must it be one at a time (in which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My
Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm
sure
there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Leslie Isaacs]

Douglas

Thanks for trying!
Finally, I discivered that the problem was that the names of one of the
folders I was tring to create contained a "."
Having removed that, all is great!!!
Thanks again
Les.

The Error 75 is probably because C:\New Folder\Leslie already exists. Try
deleting the folder, then running

Sub makedirs()

MkDir "C:\New Folder\Leslie"

End Sub

just to see whether it's a VBA issue or not.


Sorry, but I'm out of ideas. As I said, it works fine for me.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Douglas

This is getting strange!

This works fine
MkDir "C:\New Folder\Leslie"
and this worked fine:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
Debug.Print "C:\New Folder\" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub
(looking in (Ctrl-G) showed all the expected names)

But this doesn't work:
Sub makedirs()
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder\Leslie"
rsCurr.MoveNext
Loop
End Sub
I get:
Run-time error '75'
Path/file access error

If I try it with:
MkDir "C:\New Folder\" & rsCurr![prac name]
I get:
Run-time error '76'
Path not found
I very much hope something in all this means something to you, because I'm
bewildered!
Many thanks again
Les.

Works fine for me...

First, make sure that your query is returning what you think it should.
Replace

MkDir "C:\New Folder" & rsCurr![prac name]

with

Debug.Print "C:\New Folder\" & rsCurr![prac name]

then look in the Immediate window (Ctrl-G) to see that it's correct.

Next, try going into the immediate window and manually run

MkDir "C:\New Folder\Leslie"

and see whether it creates a folder for you.

BTW, assuming you want this to produce subfolders underneath C:\New
Folder,
you need a terminating slash:

MkDir "C:\New Folder\" & rsCurr![prac name]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hello Douglas

Many thanks for persevering with this!

I took the Chr$(34) out, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM Practices")
Do While rsCurr.EOF = False
MkDir "C:\New Folder" & rsCurr![prac name]
rsCurr.MoveNext
Loop
End Sub

.. but I still get the same error.

In case the problem was somethinbg to do with the fact that my field
name
in
Practices has a space (which is why I put the square brackets round it),
I
tried another field name instead of [prac name] - one without the space,
and
removed the square brackets around it, but again I got the same error.

What else can it be?
Hope you'll stay with me on this!

Les.



Oops, sorry. I didn't bother testing before posting.

Turns out you don't need the Chr$(34) after all.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Douglas

Thank you for your reply.

I tried the code that you suggested, with a slight change to the
filed
name
and path, so now I have:

Sub makedirs()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT [prac name] FROM
Practices")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
rsCurr.MoveNext
Loop
End Sub

... but when I ran it I got the message:

Run-time error 76
Path not found

with the line:
MkDir Chr$(34) & "C:\New Folder\" & rsCurr![prac name] & Chr$(34)
highlighted in yellow.

I definitely have a folder called New Folder directly under the C
drive -
I
even copied and pasted the path (C:\New Folder) from the address bar
at
the
top of the windows explorer screen.

I'm sure it's something simple (?) but cannot see it myself.
I am very grateful for your continued help.

Les.




message
Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset("SELECT member_name FROM
Members")
Do While rsCurr.EOF = False
MkDir Chr$(34) & "C:\Documents and Settings\Members\" &
rsCurr!member_name & Chr$(34)
rsCurr.MoveNext
Loop

(I've included the Chr$(34) in case member_name contains blanks)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hello Joel

Thanks for your reply.

Sorry about my ignorance: how would I use MkDir "C:\monkey" to
create
directories with names taken from my access table? And can they
all
(around
120 of them) be created at once, or must it be one at a time (in
which
case
I might as well do it manually!)?

Thanks for your continued help.
Les.


Leslie,

The command you need to use is like this:

MkDir "C:\monkey"

-Joel



Hello All

I need to create a new directory on my PC (under My
Documents>Members)
for
each [member_name] in table Members.

Is there any way of doing this automatically, with code? I'm
sure
there
is,
but my VBA isn't up to it!

Hope someone can help.

Many thanks.
Leslie Isaacs