Open file dynamically in Visual Basic-CodePudding

My goal is to open a excel file inside a Visual Basic Macro in a Word file.

The two files (the word file with the macro and the excel file I want to open) are in the same folder, named like this:

C:/.../123 - 345823847/123 - OTE.xlsx

As you can see, the file have a name composed by a number and " - OTE.xlsx", and that number is the same as the first number in the name of the folder that contains the two files.

Sub SuperMacroFV()
Dim Excel
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True

Set wb_datos = Excel.Workbooks.Open(ActiveDocument.Path & "\"{the number here}" - OTE.xlsx")

My goal is to try to open the file dynamically by knowing the number of the file by obtaining it from the name of the folder.

Another option could be to open the file knowing that it is always ended with OTE.xlsx.

CodePudding user response：

If your Excel file is in the same folder than your Word file, try this:

Sub SuperMacroFV()
Dim Excel As Object
Dim vPath As String
Set Excel = CreateObject("Excel.Application")
Excel.Visible = True

vPath = Split(ActiveDocument.Path, "\")(UBound(Split(ActiveDocument.Path, "\"))) 'last folder of path
vPath = Split(vPath, " - ")(0) 'pattern is 123 - 345823847 and we need everything before dash (123), first position of array


Set wb_datos = Excel.Workbooks.Open(ActiveDocument.Path & "\" & vPath & " - OTE.xlsx")
End Sub

CodePudding user response：

One way:

Dim pos  As Long
Dim file As String

'// get position of the last \
pos = InStrRev(ActiveDocument.Path, "\")

'// extract from the last \ to the first space
file = Mid$(ActiveDocument.Path, pos   1, InStr(pos, ActiveDocument.Path, " ") - pos - 1)

'// final
file = ActiveDocument.Path & "\" & file & " - OTE.xlsx"