FileSystemObject causing refresh malfunction

[Rick]

I have a problem in Windows XP since I created a FileSystemObject to
manipulate folders and files in an Access procedure. In XP (from the Access
File menu), when I try to rename a file, copy/paste a file, or delete a file,
the directory screen doesn't immediately refresh. I have to close and reopen
the XP File directory to see the changes. This began occurring since I
implemented the following code using the Scripting.FileSystemObject. Any
ideas? Thanks.

Public Sub cmdTransferToExcel_Click()

Dim fso As Scripting.FileSystemObject
Dim strFileName As String
Dim strFilePath As String
Dim strFileAndPath As String
Dim strDataSource As String
Dim i As Integer

On Error GoTo ErrorHandler

strFileName = "NoodleReview.xls"
strFilePath = "C:\Noodle!\"
strFileAndPath = strFilePath & strFileName
strDataSource = "qryDataTableReview"
Dim varReturn As Variant

Set fso = CreateObject("Scripting.FileSystemObject")

'test for existence of NoodleReview folder and create if is not found
If Not fso.folderexists(strFilePath) Then
fso.createfolder strFilePath
End If

'Initialize the progress bar (using an arbitrary division of 4 units)
varReturn = SysCmd(acSysCmdInitMeter, _
"Creating output file ...", 4)

'Update the progress bar
varReturn = SysCmd(acSysCmdUpdateMeter, 1)

'Delete old file, if there is one
If fso.FileExists(strFileAndPath) = True Then
fso.DeleteFile strFileAndPath
End If

'Update the progress bar
varReturn = SysCmd(acSysCmdUpdateMeter, 2)

'Create new worksheet file in Noodle! folder
DoCmd.TransferSpreadsheet _
transfertype:=acExport, _
tablename:=strDataSource, _
FileName:=strFileAndPath, _
hasfieldnames:=True

'Update the progress bar
varReturn = SysCmd(acSysCmdUpdateMeter, 3)

'Test for existence of worksheet file with loop to allow some time to
create the file
For i = 1 To 100
If fso.FileExists(strFileAndPath) = False Then
i = i + 1
GoTo TryAgain
End If
TryAgain:
Next i

'Update the progress bar
varReturn = SysCmd(acSysCmdUpdateMeter, 4)

'message to user
MsgBox "Worksheet created as " & strFileAndPath, vbOKOnly +
vbInformation, "Done"

ErrorHandlerExit:
'Remove the progress bar
varReturn = SysCmd(acSysCmdRemoveMeter)
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

 

[Rick]

After a reboot, the problem no longer exists. If anyone knows why this
happened, feel free to inform. Thanks.

 

[Ralph]

Seems like a lot of work just to use TransferSpreadsheet to export a table to
Excel.
FileSystemObject uses an awful lot of overhead. Basically it looks like you
are testing for the existence of the folder and the file.

When you use TransferSpreadsheet it will overwrite the existing workbook
without prompting you so there is reall no need to test for it's existence.

A simple function to test for the directories existence using the Dir
function would be simpler. Something like:

Public Function fldrExists(strFolder as string) as boolean
if Dir(strFolder) >1 then
fldrExists=True
else
fldrExists=False
end if
End Funciton

Sub cmdTransferToExcel_Click()

if fldrExists("C:\Noodle!\")=False then
mkdir "C:\Noodle!\"
end if

DoCmd.TransferSpreadsheet _
transfertype:=acExport, _
tablename:=strDataSource, _
FileName:=strFileAndPath, _
hasfieldnames:=True

MsgBox "Worksheet created as " & strFileAndPath, vbOKOnly + vbInformation,
"Done"
End Sub

 

[Klatuu]

It may or may not overwrite the existing file. I have seen instances where
it adds an additional sheet to the existing workbook. As I recall, it has to
do with when you specify a range, but I can't be sure. Your idea to use the
Dir function is much better than the posted code byt the OP.

I would do it this way:

strFileName = "NoodleReview.xls"
strFilePath = "C:\Noodle!\"
strFileAndPath = strFilePath & strFileName

If Dir(strFileAndPath) = vbNullString then
Kill strFileAndPath
End If

If Dir(strFilePath,vbDirectory) = vbNullString Then
MkDir strFilePath
End If

Docmd.TransferSpreadsheet.....

 

[Rick]

Thanks, I got the code out of Fedemma's book. I've noticed she does some
things strangely. Hopefully my XP directory behavior will return to normal
after I quit using the FSO method. The problems returned and it is a real
nuisance.

 

[Klatuu]

I am not familiar with that book.
The FSO can be useful for more complex things, but in your case, you don't
really need it.