Anthony's VBA Forum

[AntionesEquation]

I am trying to reference cells in Excel to Excel file on a NAS. I need to take the file names the user enters in and put them cell to have those equal the values. Is this the best way of doing this? I don't know I am pretty new to this.

[code]
Sub button2click()
'This macro gets the file names of the daily reports files
Dim Filt As String
Dim FilterIndex As Integer
Dim fileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String
Dim add As String

Flit = "Text Files (*.txt), *.txt" & _
"Excel Files (.xls), *.xls" & _
"All Files (*.*), *.*"

FilterIndex = 5

Title = "Select a File to Import"

fileName = Application.GetOpenFilename(Filt, FilterIndex, Title, , True)

If Not IsArray(fileName) Then
MsgBox "No file was selcted."
Exit Sub
End If

For i = LBound(fileName) To UBound(fileName)
Msg = Msg & fileName(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg

For j = LBound(fileName) To UBound(fileName)
Worksheets("Sheet1").Cells(j + 1, 2).Value = fileName(j)
Next


End Sub
[/code]

the value the last for loop does is copy string values into cells. If my file was N:\daily\report.xls and I wanted to use the value on D5 of sheet one of this file then in excel I would type, = '=N:\daily\[report.xls]'!D5. How can I reference this with user selected files?

I am new to forums, and VBA

[AntionesEquation]

I can reference the files if I keep the files in the same directory as the excel file that is running the macro. I just wish there was some way of getting references to the file on the NAS