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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Refrencing Excel cells on a Excel file on a NAS with VBA (https://www.excelbanter.com/excel-programming/431183-refrencing-excel-cells-excel-file-nas-vba.html)

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

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

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

Post the code you are trying to use.

"Jacob" wrote in message
...
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

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

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....v=400&text=430

Kind regards,

Alex
------------------------------------
Excel-Spezialist
www.excelspezialist.ch
------------------------------------

"Jacob" schrieb im Newsbeitrag
...
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

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

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

Code:



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

Code:



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?

"JLGWhiz" wrote:

Post the code you are trying to use.

"Jacob" wrote in message
...
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