Refrencing Excel cells on a Excel file on a NAS with VBA

[Jacob]

I am getting Excel file names from a user, and I want to take the array of
file names and get the same range of cells on each file using the file name.
The files are on a NAS. I can do this in Excel by opening both file and in
the cells I want hitting "=" then clicking on the file and the cells I want.
I would like to automate this, but when I parse the string to the right
syntax and it just puts it in the cell as a string. I try the .value and
..formula but that returns an type mismatch error.

I sorry for the long message, I am new at this

 

[JLGWhiz]

Post the code you are trying to use.

 

[Guest]

Hi there

I have written a program which is shareware and which could solve the
problem. You can define names in every single workbook and
the program collects the data over the names in one many workbooks and
creates a list in a new workbook. Then you could record a macro and directly
copy/paste the code in
the program so it will be excecuted every time you start the report. The
program is in German but I can help you with that. Unfortunately the program
is not free of charge but has a 30 day evaluation period.

Download-Link: http://www.excelspezialist.ch/index.php?nav=400&text=430

Kind regards,

Alex

 

[Jacob]

Lets say the files the user selects are from N:\Reports\Daily

Sub button2click()
'This macro gets the file names of the daily reports files
'chage files to same directory and then parce the string to file name
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
Dim t As Integer

t = 0

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

FilterIndex = 5

Title = "Select a File to Import"

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

If Not IsArray(fileName) Then
MsgBox "No files were selcted."
Exit Sub
End If

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

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

'MsgBox (add)
MsgBox ("files selected are -" & t)


End Sub

I would like use the file names to reference values in cells in those files
that the user selected to get the file names. I was thinking of save copying
these files into a worksheet object

Dim wrksht As Worksheet
Dim objList As ListObject

Set wrksht = ActiveWorkbook.Worksheets("fileName(1)")
Set objList = wrksht.ListObjects(1)
objList.Range.Activate

but fileName(1) will not work, I think because it is using a string. Any
suggestions?