open xls macro

  • Thread starter Thread starter PaulW
  • Start date Start date
P

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
 
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
 
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
 
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
 
Back
Top