Afffecting Directories and their Files with VBA (1 Viewer)

[catbeasy]

I have code that is supposed to make a file path (on the local drive) and put a file there. The code checks to see if the file path exists and if it doesn't, it creates it and then dumps the file there (excel format via transfer spreadsheet)..

The next time they run the program, the part of the code that checks to see if the file is there will run and show that it exists. So, I would like to blow it away and then make the directory again and then dump the file. To do this, the ELSE, kills all (old) files there and then is supposed to remove the directory and then re-create it and then dump the new file there.

1. The program runs the first bit, saving the file to the drive. But when run a second time, it does delete the file (via KILL), but balks at the next bit of code where it is supposed to delete the just created file path. I get a run time error 75: path/file access error.

I do not have the file path open in explorer and to make things more strange, if I do open explorer and try to delete the folder, it tells there is a sharing error. BUT! if I close Access, THEN I can delete the folder through Explorer. Its almost as if Access is making the PC think that the folder is open and so cannot do any modificatgions until Access is closed..

Is this an issue with Access or with my OS? Does anyone know any workarounds? It doesn't seem to be an issue with the code..

2. What code can i use to check to see if a file exists already (see below - code would need to go right after the ELSE statement as an IF to check to see if file exists before performing the KILL)?

Here is the code:

Dim str_fp As String
Dim str_fp_kill As String
Dim str_fn As String
str_fp = "c:\temp\pr\"
str_fp_kill = "c:\temp\pr\*"
str_fn = str_obj_name_tbl & "_pr.xls"
If Dir(str_fp, vbDirectory) = "" Then
MkDir (str_fp)
Else
Kill (str_fp_kill) 'need to check to see if file exists..
RmDir (str_fp)
'MkDir (str_fp)
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, str_obj_name_tbl, str_fp & str_fn, True

 

[dkinley]

You are already using it ... the Dir command. Per Access Help files ...

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

A quick example ....

If Len(Dir(strFileName)) > 0 Then 'file exists

Hope that helps,
-dK

 

[KeithG]

Check out the FileSysteObject class. You have much more flexiablilty creating and working with directories using this class.

 

[catbeasy]

You are already using it ... the Dir command. Per Access Help files ...



A quick example ....

If Len(Dir(strFileName)) > 0 Then 'file exists

Hope that helps,
-dK

Thanks, any idea about the other runtime error issue?

Note, that I set up some more code as a test and it worked. This is really confounding me..

other code that works:

MkDir ("c:\temp\testdir\")
Kill ("c:\temp\testdir\*")
RmDir ("c:\temp\testdir")

what's the difference?

 

[dkinley]

I would think that because of the error - not all the files are deleted. Perhaps there are some hidden files in the directory?

Halt the code before you remake the directory and manually check to see before continuing the code.

Just guessing here - but a DoEvents might be called for in order for windows to process each command before executing the next one.


-dK

 

[catbeasy]

Check out the FileSysteObject class. You have much more flexiablilty creating and working with directories using this class.

I tried this using what you suggested as per the below code..

It does create the directory, but when it tries to execute the transfersopreadsheet command, I still get a runtime error (3051) which says: the ms jet db engine cannot open the file 'c:\temp\maaps'. it is already opened exclusively by another user or you need permissions to view its data."

I have full permissions on my c drive. Again, its almost as if Access is keeping that directory open and there needs to be an added command to close it. I tried fso = nothing as you can see in the below code to no avail. Any ideas?


Dim fso, f
Dim str_folder As String

str_folder = "c:\temp\maaps"

Set fso = CreateObject("Scripting.FileSystemObject")
fso.CreateFolder (str_folder)

Set fso = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tbl_pr_template", str_folder, True

 

[catbeasy]

ok, never mind I figured it out..using the Scripting.FileSystemObject is much better..

As to my first issue, the file name in the transferspreadsheet was out of whack and also when i corrected it found out that..

It appears you have to set two different variables for the file path.

One for the creation and deletion of the file path (this is in order to accomodate the delete command, you cannot include a slash "/" at the end of the path, although you can use the create directory command both with and without a slash)

The other variable, used for the transferspreadsheet command must have the slash in the variable since otherwise it will assume the folder is part of the excel file name (I know I could avoid using 2 variables by including a slash string in the transferspreadsheet filename argument, but i think its cleaner to just use 2 variables)..

This now works and doesn't seem to have the same issues plaguing the MkDir, KILL and RmDir commands. In fact using the Scripting.FileSystemObject commands, you don't have to write code to delete the files in the directory first, just using the Deletefolder method destroys the folder and as a result, any files in it..Here is my code for any interested:

Dim fso, f
Dim str_folder_mod As String 'for creating/deleting folder
Dim str_folder_exp As String 'for export per the transferspreadsheet command, delete won't work with the slash ("/")

str_folder_mod = "c:\temp\maaps"
str_folder_exp = "c:\temp\maaps\"


Set fso = CreateObject("Scripting.FileSystemObject")
If fso.folderexists(str_folder_mod) Then
fso.deleteFolder (str_folder_mod)
End If

fso.CreateFolder (str_folder_mod)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, str_obj_name_tbl, str_folder_exp & "pr_" & str_obj_name_tbl, True

MsgBox "the folder should be created and file should be in it"

fso.deleteFolder (str_folder_mod)

MsgBox "folder and any files are deleted"

end sub

 

[dkinley]

Awesome!

Thanks for posting back your final solution for the forum. Also, be sure to hook Kieth up with the info by clicking on the scales in the top corner of his post.

Good luck.

-dK

 

[LadyDi]

I would like to use this code in my database as well. However, when I make modifications to it to fit my project, I get the error message that I don't "have premissions". The folder that I want to delete is on my C:\ drive, for which I have administrative rights. Below is the code that I am trying to use. What am I doing wrong?

Dim fso, f
Dim str_folder_mod As String 'for creating/deleting folder
str_folder_mod = "C:\Report_Data\Log Parser " & Month(Me.FileDate) & "-" & Day(Me.FileDate) & ""
Set fso = CreateObject("Scripting.FileSystemObject")
fso.deleteFolder (str_folder_mod)
MsgBox "folder and any files are deleted"