Wildcard/Variable in a macro (to ignore Version #)

[DariaL]

I have a workbook that currently updates from other documents (owned by other people) in the following way:

1. Click hyperlink to open the other workbook.
2. Macro (on a button) copies contents of that workbook to my document.
3. Macro closes other workbook.

(The documents are stored remotely so no file path links are possible between them.)

This works fine except for when the version ID of the file changes. The document server automatically adds 'V2' etc to the file name, and unfortunately this causes the macro to fail because I have an absolute file name reference in it.

Is there a way to throw a variable in there so that it ignores '_V2' etc in the file name? Or am I simply going to have to modify the macro every time it fails to run? (I can live with that, I'd just prefer not to have to.)

Any suggestions will be appreciated.

With thanks,
Daria.

 

[daverunt]

Can you post at least the file opening code from your macro for people to get some idea.

 

[DariaL]

Current version is below.

1. The centralised document server adds the CO-# and number sequence at the front, which then becomes part of the permanent record name.
2. It also adds the version information (eg: V2, V2a) and this bit is fluid depending on how many variations have gone through since its creation.
3. In this macro the source document is currently on version 6 and the one it's being copied to is version 4.

=========================================

Sub Update_AE2()
'
' Update AE2 Macro
' Record AE2 file name - Windows("CO-#10046681_Record_No_AE2.XLS")
'<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
'
' Delete the existing record information

Sheets(" AE2").Select
Range("A4:I100").Select
Selection.Clear
Range("A4").Select

' Go to the updated Record and copy<o></o>
Windows("CO-#10046681-v6-_Record_No_AE2_(Stakeholders).XLS").Activate
Range("A1:N100").Select
Selection.Copy

' Paste special (Values & Formats) into document

Windows("CO-#10039785-v4-Key_Business(consolidated).XLS") _
.Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A4").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1").Select

' Change the date to let the user know that the record has been updated<o></o>
Range("J2").Select
Selection.Copy
Range("J3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select

' Close the updated Record & go back to the consolidated Records doc<o></o>
Windows("CO-#10046681-v6-_Record_No_AE2_(Stakeholders).XLS").Activate
Windows("CO-#10046681-v6-_Record_No_AE2_(Stakeholders).XLS").Close
Windows ("CO-#10039785-v4-Key_Business(consolidated).XLS").Activate
End Sub

 

[DariaL]

*hopeful bump*