Dynamic File Loading

[Rob Edwards]

Hi,
From within an excel macro, I need to open another existing .xls
file. This file is one of many contained in a folder, which one that is
required will be dependent on a value assigned to a variable (vSpec).
The file will need to be selected because it contains the data held
within the variable - e.g.;

XXX 123.xls
XXX 234.xls
XXX 345.xls
XXY 456.xls

vSpec = 234 - Therefore XXX 234.xls needs to be opened

Can you please advise what code I need to be able to do this?

Thanks in advance.

Rob Edwards

Always look on the bright side of life!

 

[Jim Cone]

Rob,

Sub OpenTest()
Dim vSpec As String
vSpec = "234"
Workbooks.Open "XXX" & vSpec & ".xls"
End Sub

Regards,
Jim Cone
San Francisco, USA


"Rob Edwards" <[email protected]>
wrote in message
Hi,
From within an excel macro, I need to open another existing .xls
file. This file is one of many contained in a folder, which one that is
required will be dependent on a value assigned to a variable (vSpec).
The file will need to be selected because it contains the data held
within the variable - e.g.;
XXX 123.xls
XXX 234.xls
XXX 345.xls
XXY 456.xls
vSpec = 234 - Therefore XXX 234.xls needs to be opened
Can you please advise what code I need to be able to do this?
Thanks in advance.
Rob Edwards
Always look on the bright side of life!

 

[Rob Edwards]

Jim,
Thanks for your reply, but I guess I did not make things clear
enough - Sorry!

The XXX part could be anything & there is no way to know in advance.
This is the reason why I need to load the file based on it containing
the variable - this is the only thing I will know for sure.

Plese advise.

Rob Edwards

Always look on the bright side of life!

 

[Jim Cone]

Rob,

Clarity is a rarity in these newsgroups - from both the poster
and the person answering. <g>

Please read the directions...
'--------------------------------
Sub SurveyAndOpenFile()
'Jim Cone - San Francisco, USA - Sept. 2005
'Opens a workbook file meeting specified criteria.
'Uses the "like" operator to identify the file name.

'Requires a project reference to "Microsoft Scripting Runtime" library.
'Change "strPath" to the appropriate folder path.
'Change vSpec to the appropriate value.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim vSpec As String

'Specify the file number
vSpec = "234"
'Specify the folder...
strPath = "C:\Documents and Settings\user\My Documents\Excel Files"
'Specify the file to look for...
strName = "*" & vSpec & ".xls"
'Use Microsoft Scripting Runtime code.
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
Workbooks.Open objFile.Name
Exit For
End If
Next 'objFile

Set objFile = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'-----------------------------


Jim,
Thanks for your reply, but I guess I did not make things clear
enough - Sorry!
The XXX part could be anything & there is no way to know in advance.
This is the reason why I need to load the file based on it containing
the variable - this is the only thing I will know for sure.
Plese advise.
Rob Edwards
Always look on the bright side of life!

 

[Rob Edwards]

Thanks!

Rob Edwards

Always look on the bright side of life!