Why does file diaolog box open in this code?

R

Rick S.

I have nearly identical code for Excel that functions properly, use some of
this in Word and it behaves differently.
Why? It's VBA!?

'==========Ecel
sNewItem = InputBox(prompt:="Enter Entire Path for Folder Location!")
On Error Resume Next
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sNewItem) 'folder location

For Each file In Folder.Files 'iterate thru all files in folder that
have "xls" suffix
sFileName = file
If Right(sFileName, 3) = "xls" Then 'check for "xls" suffix
Workbooks.Open FileName:=file.Path, Password:="xxxxx",
WriteResPassword:="xxxxx" 'pass workbook.open password
'========End Excel

'========Word
On Error Resume Next
sNewItem = InputBox(prompt:="Enter Entire Path for Documents you want
printed!") 'using User text instead of fixed text
Application.DisplayAlerts = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sNewItem) 'folder location
For Each file In Folder.Files 'iterate thru all files in folder that
have "doc" suffix
sFileName = file
If Right(sFileName, 3) = "doc" Then 'check for "doc" suffix
'open files progmatically
Documents.Open FileName:=file.Path
'========End Word
In Word, the program opens a FileDialog box???

--
Regards

Rick
XP Pro
Office 2007
 
J

Jean-Guy Marcil

Rick S. was telling us:
Rick S. nous racontait que :
I have nearly identical code for Excel that functions properly, use
some of this in Word and it behaves differently.
Why? It's VBA!?

'==========Ecel
sNewItem = InputBox(prompt:="Enter Entire Path for Folder Location!")
On Error Resume Next
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sNewItem) 'folder location

For Each file In Folder.Files 'iterate thru all files in folder
that have "xls" suffix
sFileName = file
If Right(sFileName, 3) = "xls" Then 'check for "xls" suffix
Workbooks.Open FileName:=file.Path, Password:="xxxxx",
WriteResPassword:="xxxxx" 'pass workbook.open password
'========End Excel

'========Word
On Error Resume Next
sNewItem = InputBox(prompt:="Enter Entire Path for Documents you want
printed!") 'using User text instead of fixed text
Application.DisplayAlerts = False

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sNewItem) 'folder location
For Each file In Folder.Files 'iterate thru all files in folder
that have "doc" suffix
sFileName = file
If Right(sFileName, 3) = "doc" Then 'check for "doc" suffix
'open files progmatically
Documents.Open FileName:=file.Path
'========End Word
In Word, the program opens a FileDialog box???

Word VBA, Excel VBA, PowerPoint VBA, Outlook VBA, Visio VBA, etc. are all
different beasts. Some objects, methods, properties in the object libraries
may look the same, but they may be entirely different.

Now, in your code, have you declared a reference to the "Microsoft Scripting
Runtime" library in the VBA editor menus (Tools > References)?
If not, I suggest you do. Then, using "Option Explicit," you can see that
many variables are not declared, and you are letting the compiler make type
conversion... (As in "sFileName = file" where "sFileName" is a string, but
"file" is a file object.) A risky business in some cases. Also, you are
using object/method names as variable names, this can create problems...
"Set Folder", "= file", etc.). I also noticed that you use the all too
generic "On Error Resume Next"... It may lead to other problems. See my
suggestion in the sample code below. Finally, I also provided so error
trapping in case user makes an error in providing the path in the InputBox,
as users are prone to do...

'_______________________________________
Option Explicit
'_______________________________________
Sub Test()

Dim sNewItem As String
Dim oFSO As Object
Dim fldTarget As Scripting.Folder
Dim fleCheck As Scripting.File
Dim sFileName As String

Set oFSO = CreateObject("Scripting.FileSystemObject")

Do
sNewItem = InputBox(prompt:="Enter Entire Path for Documents you want
printed! ")
'User probably hit Cancel on the Input Box
If sNewItem = "" Then Exit Sub
On Error GoTo QuitSub
Set fldTarget = oFSO.GetFolder(sNewItem)
Loop While fldTarget Is Nothing

'Reset error trapping to normal behaviour
On Error GoTo 0

'Iterate thru all files in folder that have "doc" suffix
For Each fleCheck In fldTarget.Files
sFileName = fleCheck.Name
'Check for "doc" suffix
If Right(sFileName, 3) = "doc" Then
'open files progmatically
Documents.Open FileName:=fleCheck.Path
End If
Next

Exit Sub

QuitSub:
If Err.Number = 76 Then
MsgBox "The path you provided is invalid.", vbCritical, "Wrong Path"
Err.Clear
Resume Next
Else
MsgBox Err.Description, vbCritical, "Error " & Err.Number
Err.Clear
Exit Sub
End If

End Sub
'_______________________________________


With Words 2003, this simply opens all the files in the specified folder.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org
 
R

Rick S.

I have very poor French ;)
Merci pour votre aide! (Thank you for your help!)
I will study this to understand it better.

--
Regards

Rick
XP Pro
Office 2007
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top