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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Where would I put this in the code to save the file as a Word file?

Everywhere I've tried, the best I can get is the option to save as Unicode text, which is saving as a .txt file.

When "fname" is defined, the file saves automatically as a Word object.

Maybe I don't need the save as dialog box, just a way to return a path from my hard drive to assign to fname...

Maybe a dialog box thats just a directory tree, that allows me to retrieve a value from it once a file has been selected...

Sorry to ramble, I'm really interested in doing this.

Thanks a lot, I'm almost there...
Corticus
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
There are this two methods, try them to see if they suit your needs:

Application.GetOpenFilename
Application.GetSaveAsFilename
 

larai

New Member
Joined
Aug 28, 2002
Messages
20

ADVERTISEMENT

If you want the Word SaveAs dialog box to pop up, you need to make your Word application visible. If visible is set to FALSE, you won't be able to see any of the objects and your code hangs up.
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Aaargh,

This is so close. The code:
Code:
Application.GetSaveAsFilename
Works, but opens the dialog box in Excel, so the Excel file is getting saved, not the Word doc.

As you see, I tried activating the Word doc first, and thats happening, but then I see my little Excel button on the bottom of the screen flashing and when I switch back to Excel, the save as box is open.

The code:
Code:
WdObj.Selection.PasteSpecial Link:=False, DataType:=20, Placement:=wdInLine, DisplayAsIcon:=False
is Word VBA (from macro recorder), and runs correctly in the Excel module.
Which makes me think I should use:
Code:
'Application.Dialogs(wdDialogFileSaveAs).Show
which is Word VBA for show dialog box but doesn't seem to work...

Here's what I've got:
Code:
Sub WordUp()

'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 = 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

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

WdObj.Activate
'Application.GetSaveAsFilename
'Application.Dialogs(wdDialogFileSaveAs).Show

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

Set WdObj = Nothing

Range("A1").Select

this is soooo close, if I could just get the Word save as dialog box to pop up after the Word doc is activated, it would be perfect!

Thanks for the assistance, as always,
Blessings,
Corticus
 

Corticus

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

ADVERTISEMENT

I noticed that any help I've gotten with this problem has been in the afternoon(here in the US), so I though I'd dare a repost.

I would really like to accomplish this and I think I'm only 1 line of code away...

Sorry for the repost, I'll keep 'em to a bare minimum...

Massive Bloated Thanks,
Corticus
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
<pre/>
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 = True

'copies data
Selection.Copy
WdObj.documents.Add

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

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

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


</pre>
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
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.
 

Corticus

Well-known Member
Joined
Apr 30, 2002
Messages
1,579
Thanks for the help everybody,

You're right dmckinney,

I was hoping that with a Word object I could execute Word VBA script, but 'the requested member of the collection does not exist'.
I'm thinking that if I could just get a file tree in a dialog box, I could return that value to 'fname' and then I could save fname as a Word.doc.
I appreciate all the suggestions, the code is already very useful for me, I'm just being picky.

Peace,
Corticus
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

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
Top