Want Word Save As Dialog Box in XL

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Hi all!

I am using the following code to convert and XL spreadsheet to a Word document.

Currently the filename is determined from user-input in an inputbox. I removed the input box code for clarity.

Code:
Sub WordUpEdit()

'saves selected range as Word file
Dim WdObj As Object, fname As String
Dim test As Variant

On Error GoTo Errorchecker

'creates Word object
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = False

'copies data
Selection.Copy
WdObj.documents.Add

'this line specifies to paste special, as Unicode text
WdObj.Selection.PasteSpecial Link:=False, DataType:=20, Placement:=wdInLine, DisplayAsIcon:=False

'I would like to get fname from the save as dialog box

With WdObj
    .ActiveDocument.SaveAs Filename:=fname & ".doc"
    .ActiveDocument.Close
    .Quit
End With

Set WdObj = Nothing

Range("A1").Select

Exit Sub

Errorchecker:
Select Case Err.Number
    Case 4198
        MsgBox ("Please select range to copy!")
        Range("A1").Select
    Case Else
        MsgBox ("Operation Failed...sorry!")
End Select

End Sub

All I want to do is use a save as dialog box to pick what I save the file as, instead of "fname" which gets its value from an input box.

Word's code for this dialog box is:
Code:
Application.Dialogs(wdDialogFileSaveAs).Show

but this won't execute in Excel.

I could really use this macro if I could get it a little more user-friendly, I've got about six different versions I'm playing with, this is the most promising...

Thanks all,
Corticus
 
On 2002-09-13 07:17, dmckinney wrote:
Ivan,

not sure if you tried the code you posted, but wdDialogFileSaveAs is a Word Constant not known by Excel.

You'll have to substitute the value 84 - or so it seems to me.

dave.

Dave your right....assumed he had the word obj lib referenced as I did in my code.
In fact this should have been referenced as his code has Word constant in it!?

Anyway to reference the library by code I used<pre/>
Sub MakeLibrary_Word()
On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
"{00020905-0000-0000-C000-000000000046}", 8, 0
End Sub</pre>

_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font><font size=1> From the City of Sails
image.gif

This message was edited by Ivan F Moala on 2002-09-13 16:37
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I don't claim to be an expert but can the code below not be of help? I got it from the VBA help files,. All I've done is chage the file extension!

Sub MakeTextFile()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:WindowsDesktopLog.Doc", True)
a.WriteLine (Range("A1") & ", " & Range("A2") & ", " & Range("A3") & ", " & Range("A4"))
a.Close

End Sub
Hope this helps

Ryan UK :)
This message was edited by razzandy on 2002-09-13 18:02
 
Upvote 0
On 2002-09-13 17:26, razzandy wrote:
I don't claim to be an expert but can the code below not be of help? I got it from the VBA help files,. All I've done is chage the file extension!

Sub MakeTextFile()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:WindowsDesktopLog.Doc", True)
a.WriteLine (Range("A1") & ", " & Range("A2") & ", " & Range("A3") & ", " & Range("A4"))
a.Close

End Sub
Hope this helps

Ryan UK :)
This message was edited by razzandy on 2002-09-13 18:02

Hi Ryan, yes you are correct, and in general if all you are doing is inputting text to Word then your code is fine. In fact this is what I would do. Using word as above is good when you wish to use the functionality that the Word Object Lib provides eg Formating, Spellchecking, Tables etc.
 
Upvote 0
On 2002-09-13 17:26, razzandy wrote:
I don't claim to be an expert but can the code below not be of help? I got it from the VBA help files,. All I've done is chage the file extension!

Sub MakeTextFile()
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:WindowsDesktopLog.Doc", True)
a.WriteLine (Range("A1") & ", " & Range("A2") & ", " & Range("A3") & ", " & Range("A4"))
a.Close

End Sub
Hope this helps

Ryan UK :)
This message was edited by razzandy on 2002-09-13 18:02

I don't think this will work...though I haven't tested it. Looks to me like it creates a text file - not a word document.

The fact that you've changed the extension to .doc doesn't make it a word document. It's a text document with the wrong extension.

Dave.
 
Upvote 0
I stand corrected...it will work thanks to Word being very generous, but the result is a little 'special'. The style is 'Texte Brut' in my French Word probably something like Raw Text.

But it does work!
 
Upvote 0
Thank you guys VERY MUCH for your assistance,
sorry I haven't posted a reponse yet, I've been out of the office.

Anyways, I'm trying the different codes right now and I'll post back with my success(hopefully)!

Thanks again,
Corticus
 
Upvote 0
Yes Yes YES!!!

It works, thank you all SO much,
I've been trying to make this thing work for about 2 weeks, and its gotten to the point where the functionality of the thing was overshadowed by my desire just to make it work!

Anyways, the following code works and is WAY COOL!:
Code:
Sub ExceltoWord()

'saves selected range as Word file
Dim SaveAsBox As Dialogs
Dim WdObj As Object, fname As String

Call MakeLibrary_Word

On Error GoTo Errorchecker

'creates Word object
Set WdObj = CreateObject("Word.Application")
WdObj.Visible = True

'copies data
Selection.Copy
WdObj.documents.Add

'this line specifies to paste special, as Unicode text
WdObj.Selection.PasteSpecial Link:=False, DataType:=20, Placement:=wdInLine, DisplayAsIcon:=False


With WdObj
    .Dialogs(wdDialogFileSaveAs).Show
End With

Set WdObj = Nothing
Range("A1").Select

Exit Sub

Errorchecker:
Select Case Err.Number
    Case 4198
        MsgBox ("Please select range to copy!")
        Range("A1").Select
    Case Else
        MsgBox ("Operation Failed...sorry!")
End Select

End Sub

Sub MakeLibrary_Word()

On Error Resume Next

ThisWorkbook.VBProject.References.AddFromGuid _
    "{00020905-0000-0000-C000-000000000046}", 8, 0

End Sub

Ivan, your library reference was key. Thanks.

This macro is VERY useful to me because I can now generate correspondence in Word(which is what we use for all correspondance, cause the boss says so) from my automated spreadsheets, thus I don't have to write a bunch of stupid letters based on this information.

Yeah!
I'm so happy it works!
Thanks again,
Corticus
<img src = "http://www.smilies.nl/muziek/music049.gif"
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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