Windows 7 issues

[dantheman9]

Hi Im running into issues with vba code using excel 2007 on Windows 7 64bit.
Testing the code on Windows XP it works fine, however when running on Windows 7 doesn't work. (using the DIR function)

Public sFolder As String  ' this is a textbox on a userform
 
Public Sub DetectNewFiles()
' Opens result only (result watcher)
' Const sFolder As String = Fwatcherpath     ' folder to watch
Const sFileSpec As String = "*.txt"      ' type of file to watch
Const sAgeSelect As String = "00:00:30"  ' ignore files newer than this
'Dim sFileName As String
Dim dFileStamp As Date
Dim iFiles As Integer
Dim iNewFiles As Integer
Dim dLastFileProcessed As Date
Dim dLatestFileDetected As Date
myWorkbook.Activate
Dim sh As Worksheet, sPath As String, sName As String
Dim r As Range, Fname As String
Dim ShtName1 As String
Dim ShtName As String
Dim NewSht As Worksheet
Dim str As String
Userform1.Fname1.Caption = LText
Userform1.NFiles.Caption = "Looking in Folder Please wait...."
Application.ScreenUpdating = False
ShtName1 = "FULL RESULTS"
On Error Resume Next
Set sh = Sheets(ShtName1)
On Error GoTo 0
If sh Is Nothing Then
Set NewSht1 = Worksheets.Add
NewSht1.name = ShtName1
Set sh = NewSht1
End If
ShtName = "TEMPS"
On Error Resume Next
Set NewSht = Sheets(ShtName)
On Error GoTo 0
If NewSht Is Nothing Then
Set NewSht = Worksheets.Add
NewSht.name = ShtName
End If
dLastFileProcessed = lastDate
[COLOR=red]sFileName = Dir(sFolder & sFileSpec) ' this bit works fine in XP but not in  [/COLOR]
[COLOR=red]' Win 7, the filename is not found
[/COLOR]Do While sFileName <> ""

 

[perco754]

What's the value of sFolder? It's commented in your example.

 

[dantheman9]

Hi Perco,

thanks for getting back to me.
sFolder is user defined via FileDialog(msoFileDialogFolderPicker)

i just don't get it, all works fine under XP, but not under win 7?

Cheers

Dan

 

[dantheman9]

just to add to that here is the full code for geting sFolder, in case you might be able to see an error here;

Private Sub CommandButton17_Click()
Fwatch = " "
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Please select a Folder to watch for new RESULTS.."
If .Show Then sFolder = .SelectedItems(1) & "\" Else Path = Null
End With
Application.ScreenUpdating = False
Fwatch = sFolder
If Fwatch = " " Then
CommandButton15.Enabled = False
CommandButton16.Enabled = False
Else
CommandButton15.Enabled = True
End If
End Sub

the other sub is only then started when another button us pressed (IE start active mointoring)

 

[Hermanito]

should not be the issue, but I see that here:

If .Show Then sFolder = .SelectedItems(1) & "\" Else Path = Null

you explicitly add a backslash, while it is possible your system uses a normal slash.
It is considered safer to do this:

If .Show Then sFolder = .SelectedItems(1) & Application.PathSeparator Else Path = Null

I would try to add a debug statement to check the string, to make sure it really is valid (the Stop is there to force a breakpoint):

Debug.Print sFolder & sFileSpec
Stop
sFileName = Dir(sFolder & sFileSpec)

 

[dantheman9]

thanks Hermanito,

in the debug i get the value *.txt

interestly i get the same value in XP while in debug, but it seems to be able to get hold of file names fine

i don't understand where Im going wrong?

 

[Hermanito]

so, to be sure I understand correctly: you get "*.txt" for sFolder & sFileSpec ?
So, the value of sFolder is empty?

 

[perco754]

That is what I was asking for, the VALUE of sFolder at RUNTIME.
If it is empty (=NULL) I don't see how DIR function could produce anything but an empty string?

Hermanito is correct in suggestion you to swap the backslash with Application.PathSeparator

BR,
perco

 

[dantheman9]

well, the folder has .txt files in it...when using under XP i can get from DIR(sFolder & sFileSpec) - 001 - splitfile1.txt
under Win7 DIR(sFolder &SFileSpec) comes up blank

both under debug.print return the value " *.txt "

 

[dantheman9]

...does it mean that sFolder is not working correctly, if so how could a change it to pick up on the files?
thanks again for your time on this