623Views6Replies

Author Options:

How can i make the code for an excel macro be universal and not only work for one file? Answered


So i am writing a macro to graph a large set of data. After finishing writing the macro it worked fine on its original fie in this case 234
here is one of the original sets of key data.

ActiveChart.SeriesCollection(1).XValues = "='234'!$A$27:$A$1490"

It specifies the 234 as the file to retrieve the data.

I assumed that after replacing the 234 in the line of code above with a command like sheetname. like so below.

ActiveChart.SeriesCollection(1).XValues = "='sheetname'!$A$27:$A$1490"

Who ever can successfully answer will receive a patch. Feel free to ask questions because what i wrote above is a slight bit vauge.

Discussions


Don't start paragraphs with "So", it makes no sense.

Do you want to use "sheetname" as a variable, or to reference a sheet within a specific workbook?

L

Thats exactly what i have written in the code above, thanks for the writing advice, And putting parenthesis around the command sheet name would literally make the macro call up the word sheetname.


For use as a variable, you would have the macro look it up from a specific cell, into which you put the variable I'd guess. - is that the sort of thing you want?
I've not fiddled with macros much...

L

WHY do people insist on Excel for data reduction when there are vastly better packages for handling science data ? MathCad, Octave, TeeChart......

You may need to specify at least a partial path (eg, \mydatafiles\sheetname). Or a full path (eg, c:\excelsheets\sheetname. Just a guess.