VBA macro for inserting values in spreadsheet via concatenated range name

[Oscar B]

I have a series of workbooks for the stocks I follow, each named with the stock symbol, e.g. Abbot Labs covered by ABT.xlsm. Each workbook has a number of indicators that it calculates and puts in a range named for the indicator, e.g. the indicator "Trend" (whose value is either "Up" or "Down") is in a single cell named "Trend", which I can then refer to elsewhere as "ABT.xlsm!Trend"

I have a summary workbook (shown in simplified form below) in which I'd like to insert the current value, for each stock, of these indicators.

TrendValueInsert workbook.png

The macro I've written aims to go to each line in this summary, capture the symbol in column A, and concatenate it with the rest of the file name (i.e. ".xlsm!Trend"), retrieve that value, and post it in column B. It hangs at the line noted:


Sub TrendValueInsert()


Dim LineNum As Integer
Dim CurrentSymbol As String

Sheets("Main").Select
Range("A3").Select

For LineNum = 3 To 500
If ActiveCell.Value = "end" Then
Range("A2").Select
Exit Sub
End If

Let CurrentSymbol = Range("a" & LineNum).Value
ActiveCell.Offset(0, 1).Select
Let Selection.Value = Range(CurrentSymbol & ".xlsm!Trend").Value 'HANGS HERE
Range("a" & LineNum).Select

ActiveCell.Offset(1, 0).Select

Next LineNum


End Sub

And returns this error:

Run-time error "1004':

Method 'Range' of object '_Global' failed

The problem seems to be about the concatenation of CurrentSymbol and ".xlsm!Trend", which I'd like to sum to, e.g., ABT.xlsm!Trend, to be able to insert the value of that range in cell B5.

Any help would be greatly appreciated.

Oscar

[aydeegee]

Hi Oscar

The bit missing from your macro is opening the data files. Once you do this, you macro works fine!

In the Mainfile workbook I set up the path where the files are located (makes it easier to update)

Then I amended the macro:

set the path
open the file
close the file

Let me know if this works for you.

There is a way where you can get the data from closed files. (http://www.rondebruin.nl/win/s3/win024.htm) This certainly works faster, but is a bit involved to set up, so I have never got round to doing it.

Regards
Alastair

[Oscar B]

Hi Oscar

The bit missing from your macro is opening the data files. Once you do this, you macro works fine!

In the Mainfile workbook I set up the path where the files are located (makes it easier to update)

Then I amended the macro:

set the path
open the file
close the file

Let me know if this works for you.

There is a way where you can get the data from closed files. (http://www.rondebruin.nl/win/s3/win024.htm) This certainly works faster, but is a bit involved to set up, so I have never got round to doing it.

Regards
Alastair

Thanks, Alastair, for having a look.

I'd really like to get the data without opening each individual file, and I think ADO is OMH (Over My Head).

I should add to my original description of the problem that the macro works perfectly if

Let Selection.Value = Range(CurrentSymbol & ".xlsm!Trend").Value 'HANGS HERE

is replaced with

Selection.Value = Range("ABT.xlsm!Trend").Value

but then I'd need a separate line of code for each of the 200+ files I'm trying to retrieve data from. The problem that the loop I'm using to avoid this seems to be having is with the concatenation of CurrentSymbol and ".xlsm!Trend".

Regards,
Oscar

[aydeegee]

Hi Oscar

This is outside my experience !

I do not understand from where the system is getting the path for eg "ABT!Trend" (unless it is using the mainfile path?)

I cannot get "Selection.Value = Range("ABT.xlsm!Trend").Value" to work (which does not surprise me!), but my original offering will work,(but maybe too slowly for you)

To check things, you might try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

(I'll be amazed if this does actually work)

Regards
Alastair

[Oscar B]

Hi Oscar

This is outside my experience !

I do not understand from where the system is getting the path for eg "ABT!Trend" (unless it is using the mainfile path?)

I cannot get "Selection.Value = Range("ABT.xlsm!Trend").Value" to work (which does not surprise me!), but my original offering will work,(but maybe too slowly for you)

To check things, you might try replacing

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

(I'll be amazed if this does actually work)

Regards
Alastair

I do not understand from where the system is getting the path for eg "ABT!Trend" (unless it is using the mainfile path?)

I'm not sure, but I guess it uses the same path as the active workbook uses. In any case, it works

I cannot get "Selection.Value = Range("ABT.xlsm!Trend").Value" to work

That's because you don't have the source file, ABT.xlsm

I tried

Please Login or Register  to view this content.

But it gives the same error message

Thanks for trying, though.
Oscar