Solved: Saving Word same as Excel File

[jazzyt2u]

Hi I'm trying to Save a word document by the same name as an excel document. I don't know the name in advanced to put it into the code.
My code is in excel. I'm getting an object error when trying to save the Word document by the name I get from the excel file and don't know Word enough to know what to do.
Please help: pray2:

Sub ExcelToWord()
Const MyFolder As String = "S:\TashaA\Test2\"
Dim oWord As Object
Dim oDoc As Object
Dim NextFile As String
Dim MyFile As String

NextFile = Dir(MyFolder & "*.xls", vbNormal)
Do While NextFile <> ""

Workbooks.Open MyFolder & NextFile
CurrentFile = ActiveWorkbook.Name
Sheets("PriorityItems").Activate
Range("F14").Select
ActiveCell = CurrentFile
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula = "=LEFT(F14, LEN(F14)-4)" 'Removes the .xls
MyFile = ActiveCell & ".doc" 'Same File name but with .doc

Set oWord = CreateObject("Word.Application")
oWord.Visible = True
Set oDoc = oWord.Documents.Open("S:\TashaA\Test2\TestDocument.doc")

ActiveDocument.SaveAs Filename:=(MyFile)
Windows(CurrentFile).Activate
NextFile = Dir
Loop
End Sub

[jazzyt2u]

I solved the above issue.



oDoc.SaveAs Filename:=(MyFile)


But now how do I save it in the same folder that I origianlly got it from? It saved it on my C drive.

[CreganTur]

But now how do I save it in the same folder that I origianlly got it from? It saved it on my C drive.

You have to specify the entire filepath where you want the file to be created.

[jazzyt2u]

Then how do I add the (MyFile) at the end? Within quotes?

"S:\TashaA\Test2\" is the plath.

In excel you can change the Drive
Chdrive "S"
then the directory
ChDir "S:\TashaA\Test2\

When I used the record macro in Word it gave me

ChangeFileOpenDirectory "S:\TashaA\Test2\"

I keept getting a compile erro expected Sub, Function and Property
and I don't know what to do.

[CreganTur]

ActiveDocument.SaveAs Filename:=("S:\TashaA\Test2\" & MyFile)

Using the '&' symbol means that you're combining multiple pieces into a single whole. For example: C&P this code, paste it into your immediate window and press 'Enter' to see the results:
MsgBox "How" & " " & "are " & "you" & "?"

The technical term for what's happening is concatenation- to link things together. It allows you to also join variables to a string. C&P this code into a new module, put your cursor in it somewhere and press F5:
Sub Test()

Dim strName As String

strName = InputBox("Please enter your name.","Name Please")

MsgBox "Hello, " & strName & "! Welcome to VBA Express!"

End Sub


Within quotes?

You only wrap double-quotes around Strings in VBA. A string is just something that is evaluated as text. As you can see from the above example, the variable strName is not wrapped in quotes because I want the value of that variable to appear in the MsgBox. If I wrapped it in quotes then its value would not be used; instead the MsgBox would produce: "Hello, strName! Welcome to VBA Express" instead of adding your name in where the variable is.

HTH :thumb

[jazzyt2u]

Thank you so much. Don't know why I didn't think about that...