1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access / Word VBA problem

Discussion in 'Software Development' started by RGregory, Mar 31, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    I have an Access application that downloads a text file from a mainframe computer, opens it in MS Word, and then saves the document in word format. All is working as expected with one exception <G>. Here is a snippet of the offending code...

    With oApp.Selection.Font
    .Size = 8
    End With

    oApp.ActiveDocument.SaveAs strNewFileName

    The document is saved at this time (and if I switch to Word and look at the current document all looks correct). However, when I open the saved document it is in 10 point font which causes the report to be skewed. I have tried putting a 10-second pause between the statements. Last attempt I had was to close the document, re-open it, resize the font, and save it again. Still, no dice <G>. Any ideas would be greatly appreciated.
     
  2. s1dev

    s1dev

    Joined:
    Jun 25, 2004
    Messages:
    56
    I think your problem may be with the 'SaveAs' command. You are instructing Word to create a new file. If strNewFileName is the same name as the original download file name, Word may not be saving it, since the file already exists. (In interactive mode, Word word normally popup a warning saying file already exists, and wait for instructions from the user, etc).

    Anyway, try changing to oApp.ActiveDocument.Save

    hth

    John
     
  3. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    The code opens up a word document and shows the final report it has generated. This report is nicely formatted, exactly what the boss wanted <G>. The remaining code in the procedure just creates a new filename based on current date (ie. UserFile31Mar06, UserFile01Apr06, etc) and saves the file.

    The font must be 8 point in order for the formatting to work, so after I have opened the file and done all spacing and column headers I do...

    oApp.Selection.WholeStory

    With oApp.Selection.Font
    .Size = 8
    .Bold = True
    End With

    and the report looks fine (in the MS Word instance that the program is working with). When I open the saved report though, it is in a 10-point font. I have verified that the file is being saved, and is being saved after the font change is supposed to occur. Will reply more as I can...
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you got some default Word Font setting that is over riding the changes?
     
  5. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    Not that I know of, though it is discarding a LOT of the formatting changes I am making to the document. It is removing the margins that I specify and changing them back to defaults, changing from landscape to portrait, etc. I expect that it is saving the document in the "Default" style.
    If I put a breakpoint in the program prior to the saveas and switch over to MS Word and save it manually, all of the formatting is saved along with the document. If I wait and allow the vba program to save the document, then all formatting is lost.
     
  6. s1dev

    s1dev

    Joined:
    Jun 25, 2004
    Messages:
    56
    What method are you using to save it manually?
     
  7. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    I take the mouse and click over to the instance of Word that was started and is being used by the program. Then I click File... Save As... Save... Then a dialog box comes up asking about File Conversion with options of "Windows Default" (which I choose), "MS-DOS", and "Other encoding". I click on OK and the file is saved properly. In order for this to occur though, I have to put a break point on the code prior to where Word is closed and I want to make this totally automated. Thanks in advance for any suggestions... (HELP!) :)
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    What does your VBA do as a save ie. what is the code you are using?
     
  9. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    strNewFileName = "FreddyNew.doc"
    strOldFileName = "FreddyOld.doc"
    oApp.ActiveDocument.SaveAs "\\server_name\root\p1\p2\p3\p4\" & strNewFileName

    I have verified that strNewFileName is not in use in the directory in question. Obviously, FreddyNew.doc is not going to be the final file name, just being used in testing. The program builds a name from various daily features to ensure that files don't overlay one another.
     
  10. RGregory

    RGregory Thread Starter

    Joined:
    Jul 27, 2005
    Messages:
    65
    Found a solution. Not exactly why this works, but if I save the file as follows it saves correctly:

    oApp.ActiveDocument.SaveAs "\\Base_Directory\p1\p2\p3\p4\p5" & _
    "\p6\" & strNewFileName, FileFormat:=wdFormatRTF

    If I save the file specifying a file format of rtf everything looks good. I just don't understand why the file formatting I specified wouldn't save correctly in MS Word format. I would like to know what the original problem was, but its pretty much a moot point now <G>. Thanks to everybody for their input and assistance. :D
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/454698