Defining Word doc in Excel to be opened with VBA

[VBAstudent1977]

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!

 

[HaHoBe]

Hi VBAstudent1977,

putting the data on a worksheet in 2 cells (Path in one, Name and Extension in another)?

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]

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:

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

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:

 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]

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:

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

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:

 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!