open xls macro

[PaulW]

Hello Gurus and newsgroup users.

Your assistance please.

I am trying to create a macro that will open a .xls at
specific location on the server.


I have part of the file name within a cell on a separate
spreadsheet.


I.e. cell A12 value equals QQ3E. The file I wish to open
is called QQ3E.xls.


Any help you can provide would be appreciated.


My apologise if I have posted the message in the wrong
newsgroup.


PW

 

[Tom Ogilvy]

Assume you want to use a UNC path to get to the file and you know what the
UNC path is.

Dim sName as String
sName = Thisworkbook.worksheets(1).Range("A12").Value
if instr(sName,".xls") = 0 then
sName = sName & ".xls"
End if
sPath = "\\LOGFS03\OGILVTW\Docs"
workbooks.Open sPath & "\" & sName

 

[PaulW]

Tom that works a treat.

Thank you.

Could you tell me Tom, if it would be possible to have a
wild card in the file search. This is because some cells
dont have the complete filename.
ie A12 = "12234" and the file name is "12234bob"

Once again thank you for your time.


Regards
PW

 

[Tom Ogilvy]

This will get the first file that starts with the string in A12

Sub PartialName()
Dim sName As String
Dim sName1 As String
sName = ThisWorkbook.Worksheets(1).Range("A12").Value
sPath = "\\LOGFS03\OGILVTW\Docs"
sName1 = Dir(sPath & "\" & sName & "*.xls")
If sName1 <> "" Then
Workbooks.Open sPath & "\" & sName1
Else
MsgBox "Nothing found for " & sPath & "\" & _
sName & "*.xls"
End If
End Sub

 

[Tom Ogilvy]

No, but there are loads of them.

go to
http://www.mvps.org/links.html

and look in the Excel section.