Defining Word doc in Excel to be opened with VBA

VBAstudent1977

New Member
Joined
May 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello!

I would like to be able to open an existing Word document using Excel but name the word document in the Excel document and not the VBA code itself.

I have figured out how to open an existing Word document if I paste the path directly into the VBA code - but I'd like to define the path in the Excel document itself and have the VBA grab the path to be used when the macro is run. I am hoping to use this workbook for multiple reports and as a result I want the user to be able to enter the path of their document in Excel rather than have to understand VBA.

Right now my code (which is pretty basic as I'm new to writing VBA Code) to open an existing file is:

Sub OpenReport()

'Declare Word Variables

Dim WordApp As Word.Application
Set WordApp = CreateObject("Word.Application")
Dim strFile As String

strFile = "C:\Documents\Automated Reports\Report.docx"
WordApp.Documents.Open strFile
WordApp.Visible = True

End Sub

Any thoughts? Thank you!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
486
Office Version
  1. 2013
Platform
  1. Windows
Hi VBAstudent1977,

putting the data on a worksheet in 2 cells (Path in one, Name and Extension in another)?
VBA Code:
Sub OpenReport_120521()
'https://www.mrexcel.com/board/threads/defining-word-doc-in-excel-to-be-opened-with-vba.1170711/

Dim WordApp As Object
Dim strFile As String

'If Word is already opened
On Error Resume Next
Set WordApp = GetObject("Word.Aspplication")
If WordApp Is Nothing Then
  'Word wasn´t running, so open Application
  Set WordApp = CreateObject("Word.Application")
End If
On Error GoTo 0

'get the information from the cells
With ThisWorkbook.Sheets("Wordpathdoc")     'changeName as well as ranges to suit
  strFile = .Range("B1").Value & IIf(Right(.Range("B1").Value, 1) <> "\", "\", "") & .Range("B2").Value
End With

'Check for the existence of teh file
If Len(Dir(strFile)) > 0 Then
  WordApp.Documents.Open strFile
  WordApp.Visible = True
Else
  MsgBox "Couldn´t open '" & strFile & "'!", vbInformation, "Error loading Document"
End If

End Sub
HTH,
Hiolger
 

HaHoBe

Active Member
Joined
Jan 24, 2003
Messages
486
Office Version
  1. 2013
Platform
  1. Windows
Hi VBAstudent1977,

the example above may be just as restrictive as putting the filepath and name inside the code. If you want the users to choose any word document you could use either of the following sniplets:
VBA Code:
Sub testGetOpenFileName()
Dim varAnswer As Variant
Dim strDir As String
Dim strFilePath As String

strFilePath = "E:\Temp\200205 BU\HaHoBe privat"

strDir = CurDir
ChDrive Left(strFilePath, 1)
ChDir strFilePath

varAnswer = Application.GetOpenFilename(("Word Docs (*.doc*), *.doc*"))
If varAnswer = False Then Exit Sub

MsgBox "This should open file " & varAnswer
ChDrive Left(strDir, 1)
ChDir strDir
End Sub
VBA Code:
Sub testFileDialog()
  Dim strFile As String
 
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    If .Show = -1 Then
      strFile = .SelectedItems(1)
    Else
      Exit Sub
    End If
  End With
  MsgBox strFile

End Sub
If you want to list all files of a certain folder you may use code like this (and maybe the Worksheet_BeforeDoubleClick-event behind the sheet to start the procedure). This sniplet will list all docs from the mentioned directory in Column A of the activesheet:
VBA Code:
 Sub ListFilesDirectory()

  Dim strFile As String
  Dim lngCounter As Long

  Const cstrPathFolder = "E:\Temp\Word"

  ChDrive Left(cstrPathFolder, 3)
  ChDir cstrPathFolder

  strFile = Dir("*.doc*", vbNormal)

  Columns("A:A").ClearContents

  Application.ScreenUpdating = False
  Do While strFile <> ""
    lngCounter = lngCounter + 1
    Cells(lngCounter, 1).Value = cstrPathFolder & "\" & strFile
    strFile = Dir()
  Loop
  Application.ScreenUpdating = False

End Sub
Ciao,
Holger
 

VBAstudent1977

New Member
Joined
May 12, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hi VBAstudent1977,

the example above may be just as restrictive as putting the filepath and name inside the code. If you want the users to choose any word document you could use either of the following sniplets:
VBA Code:
Sub testGetOpenFileName()
Dim varAnswer As Variant
Dim strDir As String
Dim strFilePath As String

strFilePath = "E:\Temp\200205 BU\HaHoBe privat"

strDir = CurDir
ChDrive Left(strFilePath, 1)
ChDir strFilePath

varAnswer = Application.GetOpenFilename(("Word Docs (*.doc*), *.doc*"))
If varAnswer = False Then Exit Sub

MsgBox "This should open file " & varAnswer
ChDrive Left(strDir, 1)
ChDir strDir
End Sub
VBA Code:
Sub testFileDialog()
  Dim strFile As String
 
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    If .Show = -1 Then
      strFile = .SelectedItems(1)
    Else
      Exit Sub
    End If
  End With
  MsgBox strFile

End Sub
If you want to list all files of a certain folder you may use code like this (and maybe the Worksheet_BeforeDoubleClick-event behind the sheet to start the procedure). This sniplet will list all docs from the mentioned directory in Column A of the activesheet:
VBA Code:
 Sub ListFilesDirectory()

  Dim strFile As String
  Dim lngCounter As Long

  Const cstrPathFolder = "E:\Temp\Word"

  ChDrive Left(cstrPathFolder, 3)
  ChDir cstrPathFolder

  strFile = Dir("*.doc*", vbNormal)

  Columns("A:A").ClearContents

  Application.ScreenUpdating = False
  Do While strFile <> ""
    lngCounter = lngCounter + 1
    Cells(lngCounter, 1).Value = cstrPathFolder & "\" & strFile
    strFile = Dir()
  Loop
  Application.ScreenUpdating = False

End Sub
Ciao,
Holger
Thank you Holger! I was able to make the first one you sent me work but I had to strip out some of the testing items such as looking to see if Word was open and checking the length. The macro kept getting hung up on those lines. But if I just use the definition of the Word variables and the "With" function to open the file path then it is working. Although I've used and debugged VBA in my last job, I'm fairly new at writing my own code so I appreciate your help and I'll play around with these other newer options you provided in your latter posts to see if I land on something I like. THANK YOU SO MUCH!
 

Forum statistics

Threads
1,141,073
Messages
5,704,135
Members
421,328
Latest member
mippy

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