Results 1 to 15 of 15
  1. #1
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11

    Unanswered: Access report exporting issue

    As it stands I'm current exporting 2 summary access reports as .doc files.

    The issue I seem to be having is that when exporting the report some fields of text are being cut short (generally by the last line of text). For instance is I had the memo field in the database as:

    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aenean fermentum, dolor ut posuere hendrerit, quam purus rutrum nisi, ut elementum nibh est sed ante. Vivamus sed faucibus felis. Pellentesque vitae turpis ac augue sagittis dictum id sed ipsum. In hac habitasse platea dictumst.

    I would generally see (in the exported word doc):

    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aenean
    fermentum, dolor ut posuere hendrerit, quam purus rutrum nisi, ut
    elementum nibh est sed ante. Vivamus sed faucibus felis.
    Pellentesque vitae turpis ac augue sagittis dictum id sed ipsum. In

    What seems to be occurring is that during the export, the margins of the information seem to be differing based off of screen resolution. When I export the document in 1680 by 1050 the entire text is shown and the text fills the word document margins. On the other hand when someone exports a report in say, 1440 by 900, the text is cut off and isn’t in the full margins of the word document.

    Anyone have any clue as to why this may be occurring? The issue only occurs when the file is exported from a computer with a different screen resolution then my own.

    Thanks.M

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I can't say I've ever tried to export a report into a .doc file, but I have seen similar things with the resolution. Words may wrap differently based on the screen resolution, it's just the way Windows processes variable-width fonts.

    One solution is to use monospaced fonts, but they tend to be hard to read, and since this is for a memo field this may not be the best option.

    The only other solution I can suggest is to simply make the field bigger than it needs to be such that it can handle different resolutions. This is easily testable just by changing your monitor's resolution and seeing if it works, but it requires a little bit more work to test every form/report.

    HTH, Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I've never done that either. How are you doing it?? Are you using the "Publish it With MS Word" option in Access?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    Pretty much what I've been doing is macyver'ing to make the VBA outputReport .rtf option rename into a .doc file on output(pretty basic code):

    Code:
        templateLocation = directory & "\" & filename & " - Input Summary.doc"
        stDocName = "InputSummary"
        'Change drive to U:
        ChDrive "U"
        
        If Dir(directory, vbDirectory) = "" Then
            'Make the directory
            MkDir directory
            'Change to specified directory
            ChDir directory
            DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, templateLocation, 1
        Else
        'Change to specified directory
        ChDir directory
            DoCmd.OutputTo acOutputReport, stDocName, acFormatRTF, templateLocation, 1
        End If
    I've tried playing around with the actual textbox sizes in access to make a "best-fit" for the outputted margins in word, but a number of people are using the programs and there are quite a few different resolutions being used. Especially when you have old timers using ridiculous resolutions like 800by600, it blows my mind when I walk up to there computer to help them with an issue and I see that reso.

    As for the monospaced formatting, I’ll look into it, thanks for the idea!

    -Thanks.M

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, it's because you are using the inbuilt acOutputReport feature that you get auto-created tables.

    You either need to generate the .doc by using DDE or you need to just delete the error tables after you run your code.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    I need some clarification on what exactly you mean StarTrek.

    When you say: “the inbuilt acOutputReport feature that you get auto-created tables.”

    I don't know what you mean when you reference to auto-created tables.

    As well the error tables I’m unsure if exactly what you mean.

    And for the DDE, I thought you used it in conjunction with excel cells. I’m unaware of how it’s used (or even available) when used with access -> word data exchanging.

    Sorry for all the questions.

    -Thanks.M

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    be wary of how you manipulate memo fields in queries, forget the details but I think what you are experiencing is a common issue that the JET engine truncates memo fields to 256 characters (or thereabouts).

    as to the workaround.....
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    No the memo fields are working for me, its all about the formatting and the last line of a text field getting cut off do to the format.

    For instance some people who are using my database are exporting these reports with memo fields over 1000 characters, and yet the only thing being cut off is the last line or maybe 2.

    When I use the Tools>Office Links>Publish it with Microsoft Word, I have no issues whatsoever in different resolutions.

    But, I've been macgyvering around that because the only things the users have access to is one form where they input and export the data(Users………God I hate them).

    If it was possible to access that office link function and be able to specify its location like the “If” statement I have above, this would all be so much less complex. Unfortunately I’m left with the acReport Do.Cmd, which definitely is one heck of a hassle.

  9. #9
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by rashoku
    (Users………God I hate them).
    I think you may be in the wrong line of business as a database developer then
    Me.Geek = True

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by rashoku
    I don't know what you mean when you reference to auto-created tables.

    As well the error tables I’m unsure if exactly what you mean.
    Well, I am assuming it's those tables that get generated when you import something with some bad data in it that you are asking about how to avoid getting... those tables.

    Quote Originally Posted by rashoku
    And for the DDE, I thought you used it in conjunction with excel cells. I’m unaware of how it’s used (or even available) when used with access -> word data exchanging.
    It is used for Excel workbooks/cells.... Or Outlook addresses/messages, or Word paragraphs/documents or....
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    I figured it out. I went about it by outputting the report with the built in "Publish it with Microsoft Word" (through code) and then renaming the file after it was created.

    Left me with a "perfect" output on every report with whatever computer being used.

    The code if anyone cares...

    Code:
    Private Sub ExportListing_Click()
    On Error GoTo Err_ExportListing_Click
    
    Dim OldName As String
    Dim NewName As String
    Dim objWord As Word.Application
    Dim doc As Word.Document
    
        Dim stDocName As String
        Dim directory As String
        Dim filename As String
        
        RunCommand acCmdSaveRecord
        
        directory = "U...\" & Me.INTKTrackingID
        filename = Me.INTKTrackingID
        templateLocation = directory & "\" & filename & " - Input Summary.doc"
        stDocName = "OPS-InputSummary"
        ChDrive "U"
    
    templateLocationOrig = directory & "\" & "OPS-InputSummary.rtf"
    OldName = templateLocationOrig: NewName = templateLocation
    
    If Dir(directory, vbDirectory) = "" Then
        MkDir directory
        ChDir directory
    Else
    Kill templateLocation
    End If
    
    ChDir directory
    
    DoCmd.OpenReport "OPS-InputSummary", acViewPreview
    
    'Application.CommandBars("Menu Bar").Controls("Tools").Controls("Office Links").Controls("Publish It with Microsoft Word").accDoDefaultAction
    Call Publish
    
    'Sleep 1 Second(s)
    Call Sleep(1)
    
    DoCmd.Close acReport, "OPS-InputSummary", acSaveNo
    
    'Get last "Word" document created
    Set objWord = GetObject(, "Word.Application")
    Set doc = objWord.Documents(1)
    'Close last document created
    doc.Close True
    
    'Rename Access report name to desired name ([Tracking ID] - Input Summary.doc)
    Name OldName As NewName
    
    'Open newly named document
    Set doc = objWord.Documents.Open(templateLocation)
    
    Set doc = Nothing
    Set objWord = Nothing
    
    Exit_ExportListing_Click:
        Exit Sub
    
    Err_ExportListing_Click:
        MsgBox Err.Description
        Resume Exit_ExportListing_Click
        
    End Sub

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for the followup
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Feb 2004
    Location
    Irving, TX (Dallas, Fort Worth)
    Posts
    379

    Word Problem

    rashoku,

    I think what you are facing is a Word Problem so I would suggest:
    1. Search and find the Normal.dot file on your machine,
    2. Copy it to another name like AccReports.dot,
    3. Click on this file and edit the margins, etc. you need for your report,
    4. SaveAS (F12) back to .dot template file,
    5. Check in the MS-Office HOWTOs on adding this template into your code when opening your .doc file.
    I think that will fix it.

    DBS4M

  14. #14
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    I had already tryed replacing the "Normal.dot" file on multiple computers, and it didn't seem to help me much.

    Although, I've already created a solution to my problem through the "Publish it in Microsoft Word" Office Link in access (code above).

    Thanks for the input anyway!

  15. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by nckdryr
    I think you may be in the wrong line of business as a database developer then
    I dunno, sounds about right to me...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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