Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / October 2007

Tip: Looking for answers? Try searching our database.

Why does file diaolog box open in this code?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rick S. - 26 Oct 2007 20:31 GMT
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???

Signature

Regards

Rick
XP Pro
Office 2007

Jean-Guy Marcil - 26 Oct 2007 21:56 GMT
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.
[quoted text clipped - 30 lines]
> '========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.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Rick S. - 26 Oct 2007 22:10 GMT
I have very poor French ;)
Merci pour votre aide! (Thank you for your help!)
I will study this to understand it better.

Signature

Regards

Rick
XP Pro
Office 2007

> Rick S. was telling us:
> Rick S. nous racontait que :
[quoted text clipped - 103 lines]
>
> With Words 2003, this simply opens all the files in the specified folder.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.