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]

Ok, but once again, could you please show us the RUNTIME VALUE of sFolder?
If the sFolder IS empty then DIR *.txt would return all txt files in the ACTIVE directory, whatever that is at the moment of running your macro.

This thread suggests a bug concerning wildcards used with the DIR function in Windows 7, but I'm not sure that's the same as your the problem you're facing.
http://www.xtremevbtalk.com/showthread.php?t=321266

 

[perco754]

From MSDN http://msdn.microsoft.com/en-us/library/t4a2w9xx.aspx it says clearly that a valid PathName <> NULL must be provided at FIRST call using DIR:

You must supply a PathName the first time that you call the Dir function. To retrieve the next item, you can make subsequent calls to the Dir function without parameters.

http://msdn.microsoft.com/en-us/library/t4a2w9xx.aspx

 

[dantheman9]

Hi Perco,
ok so thing this is what you mean.
I have added debug prints for the locations used for the Start process if issue seems to be that sFolder value is not passing into the Dectectnewfiles sub (which is in another Module).

SFolder is defines as Public sFolder String in the headers of both modules.

..so debug print starts ok ok (both setting the location and starting the mointoring in module one) both return "C:\Users\Dan\Documents\c2\AZ\results\"

then in module two - to set the Now Time of Detectnew file aonther sub is called, but debug printing on this returns a blank value.

so the issues is that sFolder String is not been passed from one module to the other. Both modules declare sFolder as a public String in the header.

Where am i going worng?
would be be useful to add the other bits of code to see?

Sorry for my stupidness with all this!

 

[Hermanito]

you say both modules declare sFolder as public in the header...
Do you mean that it is a form-level variable?

Is the Sub DetectNewFiles in the code of the userform, or in another module? Is the sFolder variable also declared in that other module?

 

[dantheman9]

full code in module one for process;

Public sFolder As String
 
Private Sub CommandButton17_Click()
Fwatch = " "     'Fwatch is a Textbox on the userform
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Title = "Please select a Folder to watch for new RESULTS.."
If .Show Then sFolder = .SelectedItems(1) & Application.PathSeparator Else Path = Null
End With
Application.ScreenUpdating = False
Fwatch = sFolder
Debug.Print sFolder
Stop
If Fwatch = " " Then
CommandButton15.Enabled = False
CommandButton16.Enabled = False
Else
CommandButton15.Enabled = True
End If
End Sub

next to start the mointoing in mod one still is this code;

Private Sub CommandButton15_Click()
CommandButton15.Enabled = False
CommandButton16.Enabled = True
CommandButton3.Enabled = False
MultiPage1.Page1.Enabled = False
MultiPage1.Page2.Enabled = False
MultiPage1.Page3.Enabled = False
MultiPage1.Page4.Enabled = False
MultiPage1.Page6.Enabled = False
CommandButton17.Enabled = False
CommandButton19.Enabled = False
Call Watchon
State = ">STATUS: ACTIVATED @: (" & Format(Now(), "hh:mm:ss") & ")      OTHER OPTIONS NOW DISABLED..." & vbCrLf & ">" & vbCrLf & ">Result Watch is Active and will update set periods. Now Mointoring the following folder:" & vbCrLf & "> " & sFolder & vbCrLf & ">"
End Sub

then is issues start here for Watchon (debug print of Sfolder returns a blank value here first) this is in the second module

Public sFolder As String
 
Public Sub Watchon()
' Active via starting result watch
Dim WebPageDirectory As String
Dim ppt As Object
State = "Mointoring Folder..."
If Userform1.htmloption = True Then
WebPageDirectory = Environ("appdata") & "\Race Creator\Temp.htm"
OpenBrowser WebPageDirectory
End If
If Userform1.CheckBox1 = True Then
Timerun = Now() + TimeValue("00:00:20")
Application.OnTime Timerun, "DetectNewSplitFile"
Else
Timerun = Now() + TimeValue("00:00:20")
Application.OnTime Timerun, "DetectNewFiles"
End If
End Sub

then detectfiles runs will the same issue (debug.print of sfolder is blank)

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

sFileName = Dir(sFolder & sFileSpec)

 

[perco754]

In that case you need to update the sFolder in module 2 with the value set by module 1 since these variables have the same NAME but they are two completely different variables.

You simple set the value in any Sub() in module 1 like this

Module2.sFolder = value from Module 1

Replace the to the correct name of your corresponding Module 2.

BR,
perco

Hi Perco,
ok so thing this is what you mean.
I have added debug prints for the locations used for the Start process if issue seems to be that sFolder value is not passing into the Dectectnewfiles sub (which is in another Module).

SFolder is defines as Public sFolder String in the headers of both modules.

..so debug print starts ok ok (both setting the location and starting the mointoring in module one) both return "C:\Users\Dan\Documents\c2\AZ\results\"

then in module two - to set the Now Time of Detectnew file aonther sub is called, but debug printing on this returns a blank value.

so the issues is that sFolder String is not been passed from one module to the other. Both modules declare sFolder as a public String in the header.

Where am i going worng?
would be be useful to add the other bits of code to see?

Sorry for my stupidness with all this!

 

[Hermanito]

Your code is a bit messy for my taste, but let's not go into that right now

I suspect the issue is this: you declare sFolder (at least) twice: once in a Userform, thus only visible in that scope, and once in a normal module, set as public, thus visible everywhere BUT in the userform it will be shadowed by the local instance...

comment out the

Public sFolder As String

in the normal module, and replace all references in the code (only in the normal module, NOT in the userform) with UserForm1.sFolder instead of just sFolder. Adapt the userform name to what you have in your file.
That way you are referring to the same variable instead of two different ones...

 

[dantheman9]

thanks both of you for your time with me on this.

i'll work through your points a make changes!!

Hermanito, yes my coding is very sloppy (good job you haven't seen other bits at the moment, it would be enough to give anyone a headache )

im sort of learning on the fly with it, more a hobby than anything else i guess - but i will improve on that (i hope!!)....i think i have tried to run before i can even crawl with it, but thats a lession i'll need to work on.

thanks again

 

[Hermanito]

don't worry too much about sloppy code: you have already mastered Step 1 in 'How to beat sloppy code', and that is: knowing when it is sloppy

If you run into more problems when making changes to your code, just continue here and we'll try to help you...

 

[perco754]

Ok, good luck!

I'm still very interested in whether there IS a difference between XP and 7 in how wild-cards (*.txt) are treated in the dir function.
Could please provide the results after you've finished with our recommended code changes?

My company will soon start to migrate from XP Office 2003 into W7 Office 2010 and I know there WILL be issues

BR,
perco