Want to create a variable that references a cell

[bubby68]

Greetings, I am looking to create a macro variable that references cell N:2 from sheet 'data update options' in my workbook. In this cell will be a directory path that has been pasted in by different users, since differentr users will sore their data in different locations..I need this macro variable to be available to all my sub routines in this module. I have many. Below is my code with the 'workbooks.open Filename= ' in bold. It has a hardcoded path now..Ideally..I would like it to be "workbooks.open Filename=mypath(the nam,e of the macro variable)


Sub RawPrevYr()
'
' RawPrevYr Macro
' Gets data from Prev_Yr.xls and store it in tab RawPrevYr.
'
Application.ScreenUpdating = False

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("RawPrevYr").Select
Cells.Select
Selection.Clear
Range("A1").Select
Workbooks.Open Filename:="X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls" ' ******* Need to change to proper directory
Selection.End(xlDown).Select
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("graphs_directly_from_ccdss_2010.xlsm").Activate
Application.WindowState = xlMinimized
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste

Range("B2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.NumberFormat = "#,##0"

Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("prev_yr").Select
Windows("SI_prev_yrD.xls").Activate
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.Close

Call GraphPrevYr
Application.ScreenUpdating = True

End Sub

[mikerickson]

I'm not clear about what you want the variable to be. Do you want it to be:
the cell 'data update options'!N2
the contents of that cell
(i.e. a filepath like "X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls")
or the workbook that is stored at that file location.


In any of those cases, the most robust approach would be to write a UDF rather than assign a public vairable.

Depending on whether you want the cell, the path or the workbook, you could use one of these UDF's
Function myCell() as Range
Set myCell = ThisWorkbook.Sheets("data update options").Range("N2")
End Function

Function myPath() as String
Set myPath = CStr(ThisWorkbook.Sheets("data update options").Range("N2").Value)
End Function

Function myBook() As Workbook
On Error Resume Next
Set myBook = Workbooks.Open CStr(ThisWorkbook.Sheets("data update options").Range("N2").Value)
On Error Goto 0
End Function

[bubby68]

Hello Mike,
Thanks for the reply. I guess I wasnt as clear as I intened..sorry..I tried but this language is new to me. Anyhow..the following line of code
Workbooks.Open Filename:="X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls" '

points to the directory where the sheet si_prev_yrD.xls is. The issue I have is I want to avoid changing this directory path several times..and as I am going to send this workbook out to different people they as well will have to change this directory path several times. I was hoping that I could create a macro variable with the value of the directory and the datafile attached to it. Something like

Workbooks.Open Filename:="MYdir\SI_prev_yrD.xls" '

the variable mydir would reference cell N:2, sheet 'dataupdateoptions' of my master file (where they would paste the directory path of their choice)

I hope this helps...please bear with me as I learn this language, thanks..oh..I need thnis variable available to all my sub routines within this module

[mikerickson]

Try this line of code from the third UDF, which opens the file found in the folder, whose path is stored in cell N2 of the sheet 'data update options'.
Workbooks.Open CStr(ThisWorkbook.Sheets("data update options").Range("N2").Value) & Application.PathSeparator & "SI_prev_yrD.xls"

[bubby68]

Thanks ..that is exactly what I am looking for. Just a couple of quick questions...
1) Does that line of code replace- Workbooks.Open Filename:="X:\Common\NDSS\CCDSSv2012\Bob\HYP\BC\SI_prev_yrD.xls"
or is it written somehwere else and ref by a macro var name?

How would I do the same thing in a title statement? As the titles of all my graphs stay the same except for the area ...could cell O2 have the value for area..BC AB ON ....
Thanks..this has been a tremendous help

[mikerickson]

I'm not sure about what kind of data you have in what cells. You would be better able to know if it does what you want.

What happened when you tried it?

[bubby68]

Hi ,
Below is my code for my title. The thing I would like to change is the 'AB' part of the tile. It stands for Alberta...but when I ship this code to BC they would have to go through all my code and change each title to BC. Is there any way I could reference a cell on my 'Data Update Options' sheet where they could type BC and all the titles would reflect that change by referencing a macro variable of some sort?

With ActiveChart
.HasTitle = True
.ChartTitle.Text = "Hypertension - Crude prevalence counts - AB 1998/99-2008/09"
End With

[bubby68]

Thanks Mike...the code you sent for the title statement worked perfectley...thanks again....no glitches at all.