Fill 'As Workbook' variable with 'As String' variable

Question

My VBA project has a dynamic name since it contains e.g. the 'version number'. But I would like to refer to 1 of its sheets from another workbook (that workbook has been created by my project to do the work in there).

I have this code and that works, but it is not dynamically:

   Dim ProjectPathName As String
    Dim ProjectPathNameLength As Integer
    Dim ProjectFileName As String
    Dim PosLastBackSlash As Integer
    Dim RDXX As Workbook

    ProjectPathName = Application.VBE.ActiveVBProject.FileName
    ProjectPathNameLength = Len(ProjectPathName)
    PosLastBackSlash = InStrRev(ProjectPathName, "\")
    ProjectFileName = Right(ProjectPathName, (ProjectPathNameLength - PosLastBackSlash))

    Set RDXX = Workbooks("RDXX 2017-10_2_DEV.xlam")

This is what I have tried so far:

'    Set wbDeltaWorks = Workbooks(FileName:=ProjectFileName)
    '   Set wbDeltaWorks = Workbooks(FileName = ProjectFileName)
    '    Set wbDeltaWorks = Workbooks(ProjectFileName)
    '    Set wbDeltaWorks = Workbooks("ProjectFileName")
    '    Set wbDeltaWorks = Workbooks("" & ProjectFileName & "")
    '    Set wbDeltaWorks = Workbooks(""" & ProjectFileName & """)
    '    Set wbDeltaWorks = Workbooks(ProjectFileName.Value)
    '    Set wbDeltaWorks = Workbooks(1)
    '    Set wbDeltaWorks = ProjectFileName

Can you help me on this?

All help is much appreciated!

Set wbDeltaWorks = Workbooks(ProjectFileName) should work assuming the name is correct and it includes the extension. — Rory
– Rory, Commented Nov 10, 2017 at 15:46
You can get workbook name in more straightforward manner like this: ProjectFileName = Dir(ProjectPathName) — Ryszard Jędraszyk
– Ryszard Jędraszyk, Commented Nov 10, 2017 at 15:52

Absinthe · Accepted Answer · 2017-11-10 15:57:50Z

1

I'm assuming the iteration numbers are at the end of the project name. Lets say the current iteration is called "myProject_1234". You can loop through all open workbooks via the Windows collection and look for that keyword:

For Each Window In Application.Windows

    If Left(Window.Caption, 9) = "myProject" Then
        Set myWorkbook = Application.Workbooks(Window.Caption)
        Exit For
    End If

Next Window

If the numbers aren't at the end or you'd prefer not to use the Left function use the Instr function instead, the principle is the same.

answered Nov 10, 2017 at 15:57

Absinthe

3,4218 gold badges37 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

T.M. Over a year ago

I think the method to be OK, but OP wants to parse the VBA project name, so the condition might be If Left(Workbooks(Window.Caption).VBProject.Name, 9) = "myProject" Then.

user8920403 · Accepted Answer · 2017-11-13 09:30:01Z

0

Thanks for the help, it got me on the right track! I went for what Rory said, since that already worked "the Set wbDeltaWorks = Workbooks(ProjectFileName) should work assuming the name is correct and it includes the extension.". I only used it to late in the process at a stage where another workbook was active. Therefore I got the wrong project name.

thanks again!

answered Nov 13, 2017 at 9:30

user8920403

Collectives™ on Stack Overflow

Fill 'As Workbook' variable with 'As String' variable

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related