Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2005
    Posts
    34

    Unanswered: How to automatically get report pages into seperate files?

    I have an interesting reporting situation. I have a list of 26 countries with clients on them that feeds into a massive report. I would like to have a macro/module take each country and export it into it's own individual Word file instead of having just massive one. It's very time consuming for us to take these 26+ countries each time and manually copy/paste them into their own file. Is it even possible to have it essentially "read" the pagebreak, or look for certain keywords to start and finish copying from, and throw it into a Word document?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Check out this post:
    http://www.dbforums.com/showthread.php?t=1215723

    It has some ideas that will lead you to an easy way of doing this (looping through a recordset to get each contry and use the OutputTo function with acFormatRTF)

    tc

  3. #3
    Join Date
    Oct 2005
    Posts
    34
    one problem that I have with the recordset option is that it always gives me errors... I don't think I've ever gotten it to work. Doing something like this code gives me the following error: Type Mismatch

    Private Sub Output()

    Dim rsDat As Recordset

    Set rsDat = CurrentDb.OpenRecordSet("Select [BranchName] From [Branch] Where [BranchNum] < 5")

    Do
    DoCmd.OpenReport "Branches", acViewNormal, , "BranchNum = " & rsDat(0)
    rsDat.MoveNext
    Loop Until rsDat.EOF
    End Sub

  4. #4
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by wysiwyg6000
    one problem that I have with the recordset option is that it always gives me errors... I don't think I've ever gotten it to work. Doing something like this code gives me the following error: Type Mismatch

    Private Sub Output()

    Dim rsDat As Recordset

    Set rsDat = CurrentDb.OpenRecordSet("Select [BranchName] From [Branch] Where [BranchNum] < 5")

    Do
    DoCmd.OpenReport "Branches", acViewNormal, , "BranchNum = " & rsDat(0)
    rsDat.MoveNext
    Loop Until rsDat.EOF
    End Sub
    Nevermind, I had to uncheck the ADO Reference and use DAO instead...tweaked the recordset statements. I'll have to play with this more tomorrow, thanks for your help!

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    My bad - I didn't follow my own advice: "Always explicitly declare variables"

    Dim rsDat As DAO.Recordset

    That way, you can have ADO and DAO in the same environment and not cause problems. It's also a good way to make Access 97 Apps smoothly convert to Access 2K and newer.

  6. #6
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by wysiwyg6000
    Private Sub Output()

    Dim rsDat As Recordset

    Set rsDat = CurrentDb.OpenRecordSet("Select [BranchName] From [Branch] Where [BranchNum] < 5")

    Do
    DoCmd.OpenReport "Branches", acViewNormal, , "BranchNum = " & rsDat(0)
    rsDat.MoveNext
    Loop Until rsDat.EOF
    End Sub
    Ok, I have this thing almost working. It's reading in the branch locations great and creating a corresponding file for that branch location...BUT, the filter doesn't seem to be working! I'm going nuts trying to get this thing to filter out the forms but it's just not reading in the variable for some reason. It's putting ALL branch locations into each file instead of just the one it's supposed to. Any ideas?

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Create a variable and assign the "Where Clause" to the variable so that you can see what it is applying to the report - that may shed some light.

    Private Sub Output()

    Dim rsDat As Recordset
    DIm strSQL As String

    Set rsDat = CurrentDb.OpenRecordSet("Select [BranchName] From [Branch] Where [BranchNum] < 5")

    Do
    strSQL = "BranchNum = " & rsDat(0)
    MsgBox strSQL

    DoCmd.OpenReport "Branches", acViewNormal, , strSQL
    rsDat.MoveNext
    Loop Until rsDat.EOF
    End Sub

    This will allow you to check and see if the problem is the here or in the report itself.

    tc

  8. #8
    Join Date
    Oct 2005
    Posts
    34
    Quote Originally Posted by tcace
    Create a variable and assign the "Where Clause" to the variable so that you can see what it is applying to the report - that may shed some light.

    Private Sub Output()

    Dim rsDat As Recordset
    DIm strSQL As String

    Set rsDat = CurrentDb.OpenRecordSet("Select [BranchName] From [Branch] Where [BranchNum] < 5")

    Do
    strSQL = "BranchNum = " & rsDat(0)
    MsgBox strSQL

    DoCmd.OpenReport "Branches", acViewNormal, , strSQL
    rsDat.MoveNext
    Loop Until rsDat.EOF
    End Sub

    This will allow you to check and see if the problem is the here or in the report itself.

    tc
    got it finally, it turns out the "BranchNum=" needs to be written like so:
    "BranchNum = '" & rsDat(0) & "'"

    worked like a charm. was driving myself crazy for two days when the answer was right in front of me . thanks again for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •