Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Software Development Software Development
Search Search
Search for:
Tech Support Guy > > >

Solved: Access / Word VBA problem


(!)

RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
31-Mar-2006, 04:52 PM #1
Solved: Access / Word VBA problem
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.
s1dev's Avatar
s1dev s1dev is offline
Member with 56 posts.
 
Join Date: Jun 2004
Experience: Einstein
01-Apr-2006, 01:06 AM #2
Quote:
Originally Posted by RGregory
oApp.ActiveDocument.SaveAs strNewFileName
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
RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
03-Apr-2006, 08:42 AM #3
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...

Last edited by RGregory; 03-Apr-2006 at 09:41 AM..
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
04-Apr-2006, 05:28 AM #4
Have you got some default Word Font setting that is over riding the changes?
RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
04-Apr-2006, 02:53 PM #5
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.
s1dev's Avatar
s1dev s1dev is offline
Member with 56 posts.
 
Join Date: Jun 2004
Experience: Einstein
05-Apr-2006, 01:39 AM #6
What method are you using to save it manually?
RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
05-Apr-2006, 08:35 AM #7
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!)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,651 posts.
 
Join Date: Mar 2005
Location: UK
05-Apr-2006, 09:22 AM #8
What does your VBA do as a save ie. what is the code you are using?
RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
05-Apr-2006, 09:39 AM #9
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.
RGregory's Avatar
RGregory RGregory is offline
Member with 65 posts.
THREAD STARTER
 
Join Date: Jul 2005
Experience: Advanced
06-Apr-2006, 08:55 AM #10
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.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑