Want Word Save As Dialog Box in XL

[Corticus]

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.

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:

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

 

[Juan Pablo González]

the constant for Excel is named different:

Application.Dialogs(xlDialogSaveAs).Show

 

[Corticus]

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]

There are this two methods, try them to see if they suit your needs:

Application.GetOpenFilename
Application.GetSaveAsFilename

 

[larai]

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]

Aaargh,

This is so close. The 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:

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:

'Application.Dialogs(wdDialogFileSaveAs).Show

which is Word VBA for show dialog box but doesn't seem to work...

Here's what I've got:

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]

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]

<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]

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]

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