CODEWITHSUNDEEP
vbaexcelreference
a.t.
a.t.

Reputation: 2779

Reference name-changing workbook in VBA

I was wondering whether there is a (built in/simple) option to reference/connect/link to a workbook that has a variable name?

My xy-problem is, I have workbook b v45.xlsm and wish to export data to workbook a v34.xlsm where the version numbers vary. So I was wondering if there is a sub-ID for each workbook, to which excel can refence independent of the name, automatically picking the most recent version in that folder.

Of course the simple solution is to pick the most recently modified excel file in the folderpath containing the string "a v", assuming an identical folderpath, but I was curious if there was a more convential/integrated option for this.

Kind regards.

(For future people looking at this issue, here is my manual solution:)

Sub find_planner_name()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim string_object(0 To 2) As String 'saving the filenames as strings
Dim count As Integer 'counting nr of files encountered
Dim save_version_number(0 To 1) As Long

'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Get the folder object
Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
    'print file name
    'Cells(i + 1, 1) = objFile.name
    count = count + 1
    ReDim version_number(0 To count) As Long

    string_object(0) = ""
    string_object(1) = ""
    string_object(2) = ""


    string_object(0) = objFile.name
    If Right(string_object(0), 5) = ".xlsm" Or Right(string_object(0), 5) = ".xlsb" Then
        If Left(string_object(0), 10) = " planner v" Or Left(string_object(0), 10) = " planner v" Then
            string_object(1) = Right(string_object(0), Len(string_object(0)) - 10)
            MsgBox (string_object(1))
            Do While IsNumeric(Left(string_object(1), 1)) = True
                If IsNumeric(Left(string_object(1), 1)) = True Then
                    string_object(2) = string_object(2) & Left(string_object(1), 1)
                    string_object(1) = Right(string_object(1), Len(string_object(1)) - 1)
                End If
            Loop
            If version_number(count) < string_object(2) And string_object(2) > 0 Then
                version_number(count) = string_object(2)
                MsgBox (version_number(count))
                save_version_number(0) = version_number(count)
                save_version_number(1) = count

            End If
        End If
    End If
    i = i + 1
Next objFile

count = save_version_number(1) 'rewrite maxima back
version_number(count) = save_version_number(0) 'rewrite maxima back
'MsgBox ("done " & version_number(count))

Dim myMax As Long
Dim count_results As Long

For count_results = LBound(version_number, 1) To UBound(version_number, 1)
    If version_number(count_results) > myMax Then
        myMax = version_number(count_results)
        Findmax = count_results
        'MsgBox (version_number(count_results))
    End If
    'MsgBox (version_number(count_results) & " and count_results = " & count_results)

Next count_results

'the name of the planner =
name_planner = " planner v" & version_number(Findmax) & ".xlsm"
' check if xlsm or xlsb

'MsgBox (name_planner)

If Dir(ThisWorkbook.Path & "\" & name_planner) <> "" Then
    MsgBox ("File exists. and name is " & name_planner)
Else
    name_planner = " planner v" & version_number(Findmax) & ".xlsb"
End If

End Sub

Upvotes: 1

Views: 1314

Answers (1)

Michael
Michael

Reputation: 4883

It should be more reliable to parse filenames looking at the version numbers rather than looking at the most recently modified file. Loop through all of them checking the filename with something like:

strFile = Dir(DirectoryPath)
Do while strFile <> ""
    'Code here to parse strFile for intNewVersionNumber
    if intNewVersionNumber > intVersionNumber then intVersionNumber = intNewVersionNumber
    strFile = Dir
Loop
strFile = 'Code here to reconstruct filename from intVersionNumber

From your question, I think this might actually be necessary, even though there may be a couple of ways of adding/checking metadata on Excel files.

When you say the workbook name changes, it is literally the exact same file being renamed through Windows Explorer, or do you have multiple versions in the same folder created when you use Save As...? The issue of "automatically picking the most recent version" suggests that there are new versions being created in the same folder. If so, it means that you're actually changing which workbook you're linking to, so any kind of link to a file isn't going to work anyway. Also, even if you put in a sub-ID, each version will still have that same sub-ID. While this can still identify the files that are different versions of the same file, you still have to loop through all of those files looking for the latest version. A sub-ID would help if the filename is changing entirely, but doesn't remove the need to search through the different versions. So, if you can keep a consistent filename with only the version number changing, you'll be able to implement the simplest solution possible.

Upvotes: 3

Related Questions