0

Good afternoon,

I am wondering how to open the Word file, which has some fixed part of the string in its name.

Prevously I was trying to open in under the fixed name:

VBA Excel problem with opening the Word file

Now, I am going a step further and want to make my file more flexible.

I tried the following code:

  Sub Rams()
  Dim appWD As Word.Application
  Set appWD = New Word.Application
   Dim docWD As Word.Document
  Dim DocName As String
  DocName = "*RAMS*"

  Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\" DocName & ".docx.docm")
  appWD.Visible = True

But the debugger sets, that it's the syntax error (some parenthesis is missing here, but I don't know where.

I also tried:

 Sub Rams()
 Dim appWD As Word.Application
  Set appWD = New Word.Application
  Dim docWD As Word.Document

  Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\*RAMS*.docx.docm")
  appWD.Visible = True

But I am getting pretty much similar error to the previous situation. Theoretically, I know, that these symbols mustn't be there, although I don't know how to write it properly. enter image description here

UPDATE:

With reference to the hints in the comments I tried sth like this:

 Sub Rams2()
 Dim appWD As Word.Application
 Dim iIndex As Integer
 Dim strPath As String
 Dim strFile As String
 strPath = ActiveWorkbook.path
 strFile = Dir(strPath & "*RAMS*.docx.docm")
 Do While strFile <> ""
 Set wb = Workbooks.Open(filename:=strPath & strFile)

  For iIndex = 1 To wb.Worksheets.Count
    Set ws = wb.Worksheets(iIndex)

    'Do something here.

Next Index

strFile = Dir 'This moves the value of strFile to the next file.
 Loop
 Set appWD = New Word.Application
 Dim docWD As Word.Document
 appWD.Visible = True
 End Sub

NEXT UPDATE:

I found some solutions here:

https://www.techonthenet.com/excel/formulas/dir.php

and here

https://www.exceltrick.com/formulas_macros/vba-dir-function/

and finally used the following code:

 Sub Rams3()
 path = ActiveWorkbook.path & "\RAMS*.docm"
 File = Dir(path)
 Dim appWD As Word.Application
 Set appWD = New Word.Application
 Dim docWD As Word.Document
 If Len(File) > 0 Then
 Set docWD = appWD.Documents.Open(File)
 appWD.Visible = True
 Else
  MsgBox ("File Doesn't Exist")
 End If
 End Sub

Basically the DIR function works correctly as well as the file opening command, but I don't know, why the path is redirected to WINDOWS/System32/ whereas I set the path for my workbook?

enter image description here

Only Word application is being opened without any documents...

Geographos
  • 827
  • 2
  • 23
  • 57
  • you cannot use placeholders like `*` in the path of the `Open` method. You need to write the correct path and file name – Pᴇʜ Apr 27 '20 at 15:35
  • 1
    ^^ use [dir](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dir-function) to get the real name using the wildcards, once you have the name pass that to ```open```. Also you probably shouldn't have a double extension. – Warcupine Apr 27 '20 at 15:37
  • A lazy method I used once is to add this function to a module: https://stackoverflow.com/questions/31414106/get-list-of-excel-files-in-a-folder-using-vba Then call it with wildcards to get your filename: ```filename = GetFilesDir(path, "*RAMS*.*")``` – ionizing Apr 27 '20 at 15:44
  • I have updated my query guys. – Geographos Apr 27 '20 at 16:01
  • Change `"*RAMS*.docx.docm"` to `"*RAMS*.doc*"` and then add `Debug.Print strFile` so you can see the filenames it found in the immediate window. – HackSlash Apr 27 '20 at 16:32
  • Set docWD = appWD.Documents.Open(ActiveWorkbook.path & "\*RAMS*.doc*")Debug.Print.strFile_ Now this line looks like this. Debugger says, that is Syntax error – Geographos Apr 27 '20 at 16:38
  • Te option with File = Dir(strPath & "\RAMS*.docm") is also not working, I am getting the msgbox, that file doesn't exist, whilst it exist). – Geographos Apr 28 '20 at 09:07

0 Answers0