Variables in Variables

[Carlos]

Hi,

I'm new to VBA programming and have been working on a generic input sheet
which will save details in different folders relevant to variables in a few
cells (questions via a userform completes these cells).

I've got most of the varibles to work when searching for the correct file
but I can't seem to get the below to work. Is this because it's a variale
itself and therefore not possible?


sectionfolder = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("C1") 'in this case Export
sectiontotalname = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("D1") ' IN this case Export Total
advisor = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("E1") ' In this case
Export 1
sectiontotalworksheet = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("stats").Range("F1") ' In this case Total Export

Application.ScreenUpdating = False

' This one works fine.

Workbooks.Open Filename:="S:\Credit Management\Shared\KPI's\Section\" &
sectionfolder & "\Current\" & sectiontotalname & ".xls"

' but I can't get this variable to follow the correct path. I've tried with
and without the &'s and xls.

monthopen =
Workbooks(sectiontotalname).Worksheets(sectiontotalworksheet).Range("M1").Value


Any help would be much appricated.

Thanks
carl

 

[Wesslan]

Hi,

I'm new to VBA programming and have been working on a generic input sheet
which will save details in different folders relevant to variables in a few
cells (questions via a userform completes these cells).

I've got most of the varibles to work when searching for the correct file
but I can't seem to get the below to work. Is this because it's a variale
itself and therefore not possible?

sectionfolder = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("C1")         'in this case Export
sectiontotalname = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("D1")       ' IN this case Export Total
advisor = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("Stats").Range("E1")               ' In this case
Export 1
sectiontotalworksheet = Workbooks("KPI Advisor stat
sheet.xls").Worksheets("stats").Range("F1") ' In this case Total Export

Application.ScreenUpdating = False

' This one works fine.

Workbooks.Open Filename:="S:\Credit Management\Shared\KPI's\Section\" &
sectionfolder & "\Current\" & sectiontotalname & ".xls"

' but I can't get this variable to follow the correct path. I've tried with
and without the &'s and xls.

monthopen =
Workbooks(sectiontotalname).Worksheets(sectiontotalworksheet).Range("M1").V­alue

Any help would be much appricated.

Thanks
carl

Carl,

I am no expert. But my guess is that you have to check how you have
defined the variables. For intance are both "sectiontotalname" and
"sectiontotalworksheet" defined as strings? If they are it should
work.

If you want a workaround (probably get slaughtered by everyone in the
forum for this) I suggest you try this:

monthopen =
Workbooks("" & sectiontotalname).Worksheets("" &
sectiontotalworksheet).Range("M1").V­alue

Lastly are have you defined the monthopen in a proper way for the
value (I am guessing it is a number so integer, or long should work).

i.e "Dim monthopen as integer"

If you copy out the error message that appears maybe I could be more
helpful.

Best regards,

 

[Jon Peltier]

Does this provide a string that matches the path and filename of an existing
file:

"S:\Credit Management\Shared\KPI's\Section\" & sectionfolder & "\Current\" &
sectiontotalname & ".xls"

- Jon

 

[Carlos]

hi Both,

Thanks for the help,

I manged to get round it in the end by creating another variable which
referance the sheet i was looking for

monthtotal = "" & monthopen & ".xls"


Worksheets("Master").Select
Sheets("Master").Copy After:=Workbooks(monthtotal).Sheets(1)

I'm still learning, in fact this time last week I hadn't even looked a VBA
coding so it's be a fast learning curve. I've read a little about the "DIM"
function but don't really understand that side of it yet, so it's possibly
that this would have worked but I'm yet to cover that ground. can you
recomend any books or websites which help with explaining everything? I use
the F1 key for definitions but sometimes these are just as complicated to
understand.

Thanks for your help on this!!

Carl

 

[Jon Peltier]

Don't use this awkward construction:

"" &

Define your variables as strings or use CStr() so they don't need to be
implicitly coerced.

- Jon