Question How do you set up (create) a directory in Windows using Access VBA?

[catbeasy]

Using: Access 97..

I would like to export data (in Excel format) to a users c:\ drive. I want to be able to create the folder with my own designation (name) and then export the table data as an excel file. I know how to export the table as an excel file, but not how to create the folder..

Also, I ought probably to check to see if that folder name already exists in the users c:\ drive, so if you could provide any help on that I'd appreciate it..

Thanks..

 

[pbaldy]

You can test with Dir(), create with MkDir(). More on both in VBA help.

 

[boblarson]

Also, I just answered this same exact question yesterday:
http://www.access-programmers.co.uk/forums/showthread.php?t=166609

 

[Rabbie]

Also, I just answered this same exact question yesterday:
http://www.access-programmers.co.uk/forums/showthread.php?t=166609

Do you really expect people to search the orum when it's easier to just ask

 

[boblarson]

Do you really expect people to search the orum when it's easier to just ask

Nope, after so many years here I actually get surprised (albeit pleasantly) when someone actually does search and lets us know they found it after searching.

 

[Rabbie]

Nope, after so many years here I actually get surprised (albeit pleasantly) when someone actually does search and lets us know they found it after searching.

Yes, it is nice when people search first and ask later.

 

[catbeasy]

Also, I just answered this same exact question yesterday:
http://www.access-programmers.co.uk/forums/showthread.php?t=166609

Cool, thanks..

One other question. When making the directory, I wrote some code to delete it (see full code below). However, I get an error when trying to delete the directory. The error is: Run Time Error '75' Path/File access error.

Also, if I open IE and try to delete it manually, I get an error: Cannot remove Folder "test". There has been a sharing violation. The source or destination file may be in use.

If, however, I close access I can then remove the folder without error (and also, if I close access and then open it and run only the code that deletes the folder that works)..

So, its almost as if Access is still accessing the folder. Is there any way I can get it to release the folder or commit the transaction or whatever its called that I need to do?

Thanks..

CODE: (fcn_confirm is boolean function msgbox style)

Dim str_dir As String

str_dir = "c:\test\"

If Dir(str_dir, vbDirectory) = "" Then
MkDir (str_dir)
Else
MsgBox "This directory already exists"
End If

If fcn_confirm("do you want to remove the directory?") = True Then
RmDir (str_dir)
Else
MsgBox "Declined to remove directory"
End If

 

[boblarson]

Don't use RmDir, you would use KILL.

Kill str_dir

 

[catbeasy]

Don't use RmDir, you would use KILL.

Kill str_dir

I put in Kill, but this time got a: File Not Found error msg.

I went to debug and hovered my cursor over str_dir and it did show the directory that I just created..

any suggestions?

btw, when i looked up kill, it said:
An error occurs if you try to use RmDir on a directory or folder containing files. Use the Kill statement to delete all files before attempting to remove a directory or folder.

This seems to say that Kill is used to remove files only and the error message I got seems to confirm this(?)

new code:

Dim str_dir As String
str_dir = "c:\test\"
If Dir(str_dir, vbDirectory) = "" Then
MkDir (str_dir)
Else
MsgBox "This directory already exists"
End If

If fcn_confirm("do you want to remove the directory?") = True Then
Kill (str_dir)
Else
MsgBox "Declined to remove directory"
End If

 

[boblarson]

So RmDir should work. I just tested with it (been a while since I have used any of that).

 

[catbeasy]

So RmDir should work. I just tested with it (been a while since I have used any of that).

OK, now it works. What was that all about? I have no idea why the error messages I got were there before. The same code was used. Must have been a bug or something..

Thanks for all your assistance!