BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Looping Through Documents in a Directory Using VBA


 
Search this topic... | Search SDK (VBA/ASP/JSP)... | Search Box
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Author Message
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Wed Sep 01, 2004 5:47 am 
Post subject: Looping Through Documents in a Directory Using VBA

Sorry if this is covered in another post, but i couldn't find anything specifically.

I have a simple macro that I'm going to use to send reports to BCA. At the moment, it just uses Application.Documents.Open and I select the document I want to schedule.

Ideally I would like it to loop through all of the documents in a specified folder and schedule the lot.

I know this can be done as I've looked at some of Dave's and Dwayne's code, but I'm not sure which are the relevant bits.

Thanks in advance. icon_confused.gif
Back to top
shamit
Forum Associate
Forum Associate



Joined: 01 Jul 2004

Posts: 712
Location: Mumbai



PostPosted: Wed Sep 01, 2004 7:03 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Maybe this is what you are looking for

Code:


Function FindAnd SendFiles(Directory, FileSpec)
' Finds all files in "Directory"
' That matches the FileSpec (e.g., "*.rep").
' And sends them to BCA

    Dim NumFound As Long
        With Application.FileSearch
        .NewSearch
        .LookIn = Directory
        .FileName = FileSpec
        if .Execute() > 0 then

            For i = 1 To .FoundFiles.Count
                 code_for_sending_to_bca  (.FoundFiles(i))
            Next i

         end if
       
    End With
End Function




HTH

_________________
Shamit Verma
http://www.shamit.org/dpal
Back to top
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Wed Sep 01, 2004 7:04 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Looks good to me. Thanks very much. I'll give this a try. yesnod.gif
Back to top
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Wed Sep 01, 2004 7:15 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

I also looked at Dave's code more closely and figured out that I could use something like this:

Code:

Sub FindFiles()
Dim strDocPath As String
Dim strCurrentFile As String

strDocPath = "c:\temp\"
strCurrentFile = Dir(strDocPath & "*.*")

Do While strCurrentFile <> ""
MsgBox strCurrentFile
strCurrentFile = Dir
Loop

End Sub


replacing MsgBox with what you really want to do with the document.
Back to top
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Wed Sep 01, 2004 7:30 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Yep I'm talking to myself here but someone else might find my ramblings useful. This works:

Code:

Sub ScheduleDocs()
Dim Doc As Document
Dim Var As Variable
Dim DAOpt As DocAgentOption
Dim strDocPath As String
Dim strCurrentFile As String

strDocPath = "c:\temp\"
strCurrentFile = Dir(strDocPath & "*.rep")

Do While strCurrentFile <> ""
    Set Doc = Application.Documents.Open(strDocPath & strCurrentFile)
    'set the BCA options and send to BCA
    Set DAOpt = Doc.DocAgentOption
    DAOpt.Server = "BCA"            'your BCA Scheduler name
    DAOpt.Refresh = True                    'refresh the report
    DAOpt.ScheduleMode = boOnce             'run only once
    DAOpt.StartDate = Date                  'start now
    DAOpt.EndDate = DAOpt.StartDate + 1     'must be later than start
    'send the report to yourself
    DAOpt.Users(1) = Application.Variables("BOUSER").Value
    DAOpt.Send
    Doc.Close
strCurrentFile = Dir
Loop

End Sub


One last question though. Does Dir() return an array i.e. a list of all the file names? And why does stating strCurrentFile = Dir move me onto the next file in the list? Apart from that, I'm cool.

Thanks.

grouphug.gif
Back to top
Dwayne Hoffpauir
Forum Groupie
Forum Groupie



Joined: 19 Sep 2002
ASUG Icon
medal_gold.gif*2speaker.gif*5medal_bronze.gif
Posts: 8639
Location: Plano, TX USA


flag
PostPosted: Wed Sep 01, 2004 8:17 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Grantie wrote:
One last question though. Does Dir() return an array i.e. a list of all the file names? And why does stating strCurrentFile = Dir move me onto the next file in the list? Apart from that, I'm cool.

That's just the way it works.
from the VBA help wrote:
Dir(pathname) returns the first file name that matches pathname. To get any additional file names that match pathname, call Dir again with no arguments.

_________________
Dwayne Hoffpauir
Image link
Back to top
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Wed Sep 01, 2004 9:43 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

OK thanks. Do you know if I can force it to look in sub-directories as well? icon_question.gif
Back to top
Dwayne Hoffpauir
Forum Groupie
Forum Groupie



Joined: 19 Sep 2002
ASUG Icon
medal_gold.gif*2speaker.gif*5medal_bronze.gif
Posts: 8639
Location: Plano, TX USA


flag
PostPosted: Wed Sep 01, 2004 11:14 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Grantie wrote:
OK thanks. Do you know if I can force it to look in sub-directories as well? icon_question.gif

Hmmm ... not generically (meaning one Dir statement on it's own), but you could create a recursive loop (meaning a procedure that calls itself) to traverse the directory structure.

_________________
Dwayne Hoffpauir
Image link
Back to top
joepeters
Forum Addict
Forum Addict



Joined: 29 Aug 2002

Posts: 4743
Location: Connecticut, USA


flag
PostPosted: Thu Sep 02, 2004 11:07 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Grantie wrote:
OK thanks. Do you know if I can force it to look in sub-directories as well? icon_question.gif


Here's a function I wrote that does directory recursion:

Code:
Sub Recurse_Dir(mypath As String, Why As String)
    Dim DirList() As Variant
    Dim counter As Integer
    Dim MyName As String
    counter = 0
    ReDim DirList(1)
   
    MyName = Dir(mypath, vbDirectory)

    Do While MyName <> ""   ' Start the loop.
    If MyName <> "." And MyName <> ".." And InStr(1, MyName, "%") = 0 Then

        If (GetAttr(mypath & MyName) And vbDirectory) = vbDirectory Then
            ReDim Preserve DirList(counter)
            DirList(counter) = mypath & MyName
            counter = counter + 1

        Else
            If UCase(ActiveDocument.Name & ".rep") <> UCase(MyName) Then
                If Why = "Report" Then
                    Call CheckTheFile(mypath, MyName)
                Else
                    CheckUNV (mypath & MyName)
                End If
           End If
        End If
    End If
    MyName = Dir    ' Get next entry.
    Loop
    If counter = 0 Then Exit Sub
        Call Recurse_Dir((DirList(counter)) & "\", Why)
    Next

End Sub


(code is derived from a snippet I found on ExpertsExchange)

This is part of a util I wrote to search and record all reports, universes used in those reports, and all objects used. This is stored in Oracle for doing impact analysis. If anyone's interested I'll post the rest of it.

Joe
Back to top
Grantie
Principal Member
Principal Member



Joined: 01 Jul 2004

Posts: 177
Location: Bonnie Scotland


flag
PostPosted: Fri Sep 03, 2004 3:42 am 
Post subject: Re: Looping Through Documents in a Directory Using VBA

Thanks a lot Joe. This will keep my brain cells busy for a while.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> SDK (VBA/ASP/JSP)  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0489 seconds using 17 queries. (SQL 0.0027 Parse 0.0357 Other 0.0105)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo