Using this VBA code, is there anyway to affect the font style of the Word file produced?

wittonlin

Board Regular
Joined
Jan 30, 2016
Messages
144
For some reason, maybe because it's created from a text file?

The type style is always saved as Courier New with a font size of 10.5, often too small for people to read, especially on a mobile device.

Code:
Sub SaveAsTextFileFixedSilentClose()
       
        Application.DisplayAlerts = False
        
        ActiveWorkbook.SaveAs FileName:=strFile & "_Word" & ".txt", _
            FileFormat:=xlText, CreateBackup:=False
        SaveasDoc
        strSavedFiles = strSavedFiles & ";;" & strFile1 & ".doc"
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        Kill strFile & "_Word" & ".txt"
        
End Sub
 
Unbelievable! I still have an issue here with the formatting of the produced Word doc.

The font and style are perfect, thanks to shknbk2!

But like when you copy from Excel directly into Word, it preserves the columns and data runs off the right edge, and doesn't wrap. We've always copied into Notepad first then into Word and it wraps perfectly, after removing the Excel formatting. Problem is I don't know how to CODE for this. :(

Can anyone help? :confused:

 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Can you be more specific about what formatting it has now and what you want it to be? What is the paragraph formatting?
 
Upvote 0
Can you be more specific about what formatting it has now and what you want it to be? What is the paragraph formatting?


Looking at this now, I'm not sure there's anything we can do!

The file happens to be for a gal using an iPhone, maybe viewing on a MAC too, unsure.

But I opened several Word files produced by our code and the data wraps just fine! At least what I'm seeing.

Here's the image she sent that she sees. This was a screen grab on an iPhone. She complains that the data runs off the right edge and she can't see some data.



IMG_1685.jpg

But when I view several of the files produced, from on my Windows PC I see this. The wrapping is 100% fine!


screenshot.265.jpg




Then it gets weirder. All I do is open her file, literally just click SAVE again and resend the file to her and it then wraps ok! ;) :eek:
 
Last edited:
Upvote 0
Does anyone have any ideas?

I might have to recreate what I've done manually, with code. I simply copy the records from Excel to Notepad first, before putting in Word. In affect, this removes the table formatting so Word has no choice but to display it as text, wrapping perfectly vs. displaying (as seen above) the table formatting that runs off the edge rather than wrapping nicely, as also seen above.

I should mentioned that the code we're using doesn't seem to be as much of an issue on a PC than it does a MAC for some reason. But I could be wrong. I do know that the image shown above with the table formatting preserved is on an iPhone and poss MAC, but when I open those same Word files locally they are already wrapping, again, as shown above.
 
Upvote 0
I notice that the page view of the Word image above is Web layout (third selection in lower right portion of status bar). I think that Word is only wrapping in this view because it is designed to wrap in spite of the text really extending on in one line. For example, in Notepad, without word wrap, there is probably just one line for each of the records, for example, from Eva all the way to Retirement. I would guess that the viewer that the MAC is using preserves the line without default word wrapping.

I don't know what you saving the document and resending the file has to do with fixing the word wrapping, but maybe this is a place to start the troubleshooting. When you open the file to re-save and resend, is it in Print layour or still in Web layout?
 
Upvote 0
I notice that the page view of the Word image above is Web layout (third selection in lower right portion of status bar). I think that Word is only wrapping in this view because it is designed to wrap in spite of the text really extending on in one line. For example, in Notepad, without word wrap, there is probably just one line for each of the records, for example, from Eva all the way to Retirement. I would guess that the viewer that the MAC is using preserves the line without default word wrapping.

I don't know what you saving the document and resending the file has to do with fixing the word wrapping, but maybe this is a place to start the troubleshooting. When you open the file to re-save and resend, is it in Print layour or still in Web layout?

First for the record. When I open any Word file for other clients and change the view to print view it still wraps perfectly; the table formatting is gone.

This leads me to believe that it's 100% related to the same file viewed on an iPhone as shown above and maybe on a MAC. You can see most definitely on her screenshot that she's seeing the table format preserved.

The final answer, as I mentioned, is the weirdest of all! I simply open the Word doc (I see wrapping and NO table formatting) I then just resave it, literally, and resent it to her and it's 100% fine. For the record .docx doesn't come into play as you can see we're saving the original files as .doc. When I open her file, because of my default settings it wants to save it as .docx, so I just change it to .doc and click send. THAT'S IT. I have NO idea what the deal is.
 
Upvote 0
The only thing I can think of is that when the code uses SaveAs2, you only provide the first 2 parameters if you're using the code I gave. Maybe, when you open Word and click Save, there are other parameters it uses that cause the data to show up correctly. I've looked through the SaveAs2 parameters, but I can't figure out which one(s) would need to be further defined different from their defaults.
 
Upvote 0
Here's the new code including your code. This is perplexing. It has to be the darn iPhone!

Code:
Sub SaveasDoc()
    
    Dim WordApp As Object, WordDoc As Object
    Dim fullname As String
    Dim fullnametxt As String
    
    'fullname = Application.ActiveWorkbook.fullname
    'fullnametxt = Left(fullname, Len(fullname) - 4) & ".txt"
    
    Call FastCodeStart
    
    Set WordApp = CreateObject("Word.Application")
    Application.DisplayAlerts = False
    
    WordApp.Visible = True
    
    WordApp.Documents.Open FileName:=strFile & "_WORD" & ".txt", ReadOnly:=True
    
    Application.DisplayAlerts = False
    
    WordApp.ActiveDocument.Range.Font.Name = "Arial"
    WordApp.ActiveDocument.Range.Font.Size = 16
       
    WordApp.ActiveDocument.SaveAs2 strFile1 & ".doc", wdFormatDocument
    
    Application.DisplayAlerts = False
    
    WordApp.ActiveDocument.Close
    WordApp.Quit
    
    Set WordApp = Nothing
    Set WordDoc = Nothing


    Call FastCodeEnd
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,487
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top