Hi again both. Don't bother with looking at the code...it was my own stupid
mistake...I didn't have the right sheet name. Now I do have another question
though. This is on a template and when the user double clicks the template
it automatically names it a .xls. How do I go about saving this file with no
code and no formulas...do I put code in the save as function or where.

Thanks again guys.

Cathy

"Cathy W" wrote:

Hi both. Thank you guys so much for your help...but I still get the #REF
error when I use the following code:

=INDIRECT("'[" & A1 & "]Sheet1'!G26")

I am going to paste all my code here that I have put under the This Workbook
object. Should that maybe be somewhere else. I am starting to loose my mind
I think! Anyway here is the code I have in total. This code doesn't reflect
the new filename without the path.

Private Sub Workbook_Open()

Cells(1, 1).ClearContents

Application.DisplayAlerts = True
UpdateLinks = xlUpdateLinksAlways

'If ThisWorkbook.Path = "" Then

Call openfile
UpdateLinks = xlUpdateLinksAlways


End Sub

Private Sub openfile()

Dim sDailyReport As String
Dim vFileName As Variant

ChDrive "K"
ChDir "K:\Individual Directories\Woodford, Cathy\Daily Report"
filetoopen = Application.GetOpenFileName("Excel Files (*.xls), *.xls")

If filetoopen < False Then
Workbooks.Open FileName:=filetoopen
Else
MsgBox "User Clicked Cancel, Exiting"
Exit Sub
End If


ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized

Sheets(1).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(2).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(3).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(4).Cells(1, 1).Value = BuildString(filetoopen)
Sheets(5).Cells(1, 1).Value = BuildString(filetoopen)


End Sub

Public Function BuildString(vStr As Variant)
sStr = vStr
If InStr(sStr, "\") = 0 Then
BuildString = "[" & sStr & "]"
Else
sStr1 = sStr
Do While InStr(sStr1, "\") 0
sStr1 = Right(sStr1, Len(sStr1) - InStr(sStr1, "\"))
Loop
BuildString = Left(sStr, Len(sStr) - Len(sStr1)) _
& "[" & sStr1 & "]"
End If
End Function

Thanks again guys...you are being very kind to keep looking at this.

Cathy

"JE McGimpsey" wrote:

Since the file will be open, there's no need for the path (and
INDIRECT() doesn't work with closed workbooks), so I'd suggest modifying
the Workbook_Open code to just put the workbook name in.

If you can't do that, then you can use something like:

=INDIRECT("'[" & MID(A1, FIND("$$", SUBSTITUTE(A1, "\", "$$", LEN(A1)
- LEN(SUBSTITUTE(A1, "\", "")))) + 1, 255) & "]Sheet1'!G26")


In article ,
"Cathy W" wrote:

Hi. Thanks again for the response. I am going to paste what is put in cell
A1 so that you get an idea of what's happening. The formula still returns a
#REF! error.

In cell A1, the path of the file the user selects in the open dialog box
appears as the following:

K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily Report\dec 12.05.xls

So then in cell E12 I want it to say =[a1]dos1!g26 which should return the
following formula: =[K:\INDIVIDUAL DIRECTORIES\Woodford, Cathy\Daily
Report\dec 12.05.xls]dos1!g26 but it's like it's not picking up the value in
cell A1.

HELP! :-)