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 <> ""

 

[Hermanito]

Right, I totally forgot about that... I find it hard to imagine that exactly the same code works under XP but not under Win7.

Dantheman9: can you confirm that you tested this with exactly the same workbook? Not having moved code between modules at all?

I recall having had strange issues with Dir once, because I was using another function while looping through the results from Dir. That other function also happened to use Dir, which effectively reset the pathvalue that Dir was maintaining in the original function. It took me a while to nail that one down

 

[dantheman9]

Hi both,

yes it is the same code (via the same excel addin). both on office 2007 (which is what was used to make the addin), and only change is windows XP vs Win7 64bit.

all has worked fine on differnet pc's running XP and office 07, just on Win7 that its been having issues.

I used perco's idea to pass the sFolder String over to the other module which did the trick fine, and now i get the path name. interesting enough, i had to change other bits of the code later now too, each file is imported and proceeded, in XP, it seems ok with just the file name (strange it would find the location!!) in Win 7 i had to add the path and file name.

could a pick your brains for another area thats not working?

at some point in the same process, users can run a browser, which is set to refresh based on age of data etc.
it all loads ok using the following; (i've had to add the error coding as it seems that ie9 doesn't have a menubar, and starts issues!!)

Function OpenBrowser(tmpURL)
Set Browser = CreateObject("InternetExplorer.Application")
On Error Resume Next
Browser.Navigate (tmpURL)
Browser.Addressbar = False
Browser.StatusBar = False
Browser.Toolbar = False
Browser.MenuBar = False
Browser.Resizable = True
Browser.Visible = True
On Error GoTo 0
End Function

so that seems ok to that point, the file is opened. Also Browser is Delared as a Public Object for the module (everything is in the same module for this one.

Later in the Dectectnew files sub,
im using

With Browser
.refresh
End With

but i get the error:
Run time error ' -2147417848 (80010108)'
Automation error:
The objects invoked has dissconnected from its clients

Debug print (see im learning!! ) just brings up the same error and the value 0.000381944444444444