Names can belong to either a worksheet or a workbook. They can be local or
global.



Joel wrote:

Names is a worksheet object not a workbook object. try adding the sheet name.

LocationsDCWName = Range(ThisWorkbook.Sheets("Sheet1").Names
("CoreLoc").RefersTo).Value
"Khurram" wrote:

Hi all,
I really hope you guys can help as I have nowhere else to check. In a
new job, I have inherited a Excel document that contains a large
amount of VBA code. One of the Worksheets contains a button that
imports data from another Excel document used by another part of the
business. The data in the target Excel document is always in the same
place. I am listing two pieces of VBA code below. One is the Sub
assigned to the button on the Excel document that is importing the
data. This is in a module called "ImportCore". I have manually added
line numbers to the code for ease of reference. The second is the
declarations from another module called "StartProgram". These contain
the variables that are being utilised in the ImportCore module.

The importing is done by me opening both the destination and target
Excel documents. The name of the target document from which we are
importing is placed in a specific cell in the destination doc. This
cell is a named range called "CoreLoc" and is defined as ='DCW
Reference Data'!$B$4. In the destination Excel doc, I click on the
button that triggers Sub CoreLocLink(). The code here seems to fail
at line 22. While investigating, I noticed that if I replace the
variables with the actual values, the code progresses. I have made
sure that all variables are declared as Public and that both modules
have an Option Explicit declaration at the beginging however still not
working. Please help!!!!


Import Core Module

Option Explicit
Sub CoreLocLink()

1 Dim wbCoreDCW As Workbook
2 Dim wbESMDCW As Workbook
3 Dim ArrCoreArray() As Variant
4 Dim MyPath As String
5 Dim MyCorePath As String
6 Dim LocationsDCWName As String
7 Dim LastLn As Integer
8 Dim ArrayCounter As Integer
9 Dim InnerLoop As Integer
10 Dim UnitType As String
11 Dim K As String
12
13 'Call StartProgram
14
15 Application.ScreenUpdating = False
16 On Error GoTo ErrorHandler
17 'Get the path of this worksheet
18 MyPath = ThisWorkbook.FullName
19 Set wbESMDCW = ThisWorkbook
20 MyCorePath = Left(MyPath, Len(StrReverse(MyPath)) - InStr(1,
StrReverse(MyPath), "\"))
21 'Get the locations DCW Name from the named range CoreLoc
22 LocationsDCWName = Range(ThisWorkbook.Names
("CoreLoc").RefersTo).Value
23 'Get the last line of location Display on the DCW
24 Application.ScreenUpdating = False
25 Windows(LocationsDCWName).Activate
26 Sheets(glCoreLocSheet).Select
27 LastLn = Cells(Rows.Count, glCoreLocDescCol).End(xlUp).Row
28 'Set the Array to be 2 dimensional, with 3 columns and as many
29 'rows as there are lines on the Core DCW.
30 ReDim ArrCoreArray(LastLn, 2)
31 'Loop through each row on the locations DCW if
32 'there is data there
33 If LastLn glCoreLocStRow Then
34 ArrayCounter = 0
35 InnerLoop = glCoreLocStRow + 1
36 Do
37 'Populate the Facility Code
38 ArrCoreArray(ArrayCounter, 0) = Cells(InnerLoop,
glCoreLocNACSCol).Value
39 Do
40 UnitType = Cells(InnerLoop, glCoreLocAmbCol).Value
41 'If the location for this line is Ambulatory, then
populate the array
42 If Trim(UnitType) = "Ambulatory Care Area" Or Trim
(UnitType) = "Nurse Ward" Then
43 ArrCoreArray(ArrayCounter, 1) = Cells(InnerLoop,
glCoreLocDescCol).Value
44 ArrCoreArray(ArrayCounter, 2) = Cells(InnerLoop,
glCoreLocDispCol).Value
45 ArrayCounter = ArrayCounter + 1
46 End If
47 InnerLoop = InnerLoop + 1
48 'Check if we've changed NACS code
49 Range("A" & InnerLoop).Select
50 K = Cells(InnerLoop, glCoreLocNACSCol).Value
51 Loop Until K < "" Or InnerLoop = LastLn + 1
52 Loop Until InnerLoop = LastLn + 1
53 If ArrCoreArray(ArrayCounter, 2) = "" Then
54 ArrCoreArray(ArrayCounter, 0) = ""
55 End If
56
57 'The Target Data is now in the array, ready to be written to
the destination document
58
59 wbESMDCW.Sheets(glSchLocSheet).Activate
60 Range(Cells(glSchLocStRow + 1, glSchLocFacCodeCol), Cells
(LastLn + 1, glSchLocAmbDispCol)).Value 61= ArrCoreArray()
62 End If
63 Application.ScreenUpdating = True
64 Application.EnableEvents = True
65 Exit Sub
66ErrorHandler:
67 MsgBox ("There has been an error. Please check that a valid Core
DCW name is entered on the Reference 68Data Tab, and that this
document is open on your computer")
69 Application.ScreenUpdating = True
70 Application.EnableEvents = True
71End Sub

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

StartProgram Module

Option Explicit

'Need to declare a "Design Mode" flag to identify if we're trying to
'insert columns to the sheet.
Public glDesignMode As Boolean

'Now need to declare Sheets
Public glRefDataSheet As String
Public glPersonnelSheet As String
Public glSchLocSheet As String
Public glBooksSheet As String
Public glSlotSheet As String
Public glTemplateSheet As String
Public glApptTypeSheet As String
Public glPrepsSheet As String
Public glGuideSheet As String
Public glApptSetSheet As String
Public glgroupSheet As String
Public glCABSheet As String
Public glPickLstSheet As String

'Declare globals to store sheet start Rows
Public glRefDataStRow As Integer
Public glPersonnelStRow As Integer
Public glSchLocStRow As Integer
Public glBooksStRow As Integer
Public glSlotStRow As Integer
Public glTemplateStRow As Integer
Public glApptTypeStRow As Integer
Public glPrepsStRow As Integer
Public glGuideStRow As Integer
Public glApptSetStRow As Integer
Public glgroupStRow As Integer
Public glCABStRow As Integer
Public glPickLstStRow As Integer

'Declare globals for the columns that we need to reference in the code
Public glRefDataValCol As Integer

Public glPersonnelLNCol As Integer
Public glPersonnelFNCol As Integer
Public glPersonnelMNCol As Integer
Public glPersonnelRoleCol As Integer
Public glPersonnelFullCol As Integer

Public glSchLocSchFacIdCol As Integer
Public glSchLocFacCodeCol As Integer
Public glSchLocAmbDispCol As Integer
Public glSchLocAmbDescCol As Integer

Public glBooksResCol As Integer
Public glBooksModCol As Integer
Public glSlotsModCol As Integer

Public glTemplateResCol As Integer
Public glTemplateNmCol As Integer
Public glTemplateSlotCol As Integer
Public glTemplateDayBCol As Integer
Public glTemplateDayECol As Integer
Public glTemplateSlotSCol As Integer
Public glTemplateSlotECol As Integer
Public glTemplateModCol As Integer
Public glTemplateSlotResCol As Integer

Public glApptTypeNmCol As Integer
Public glApptTypeModCol As Integer

Public glPrepsModCol As Integer

Public glGuideModCol As Integer

Public glApptSetPrepCol As Integer
Public glApptSetPostCol As Integer
Public glApptSetModCol As Integer

Public glGroupModCol As Integer

Public glCABModCol As Integer

Public glPickLstFacIdCol As Integer
Public glPickLstSiteCdCol As Integer
Public glPickLstNACSCol As Integer
Public glPickLstApptCol As Integer
Public glPickLstTrtFnCol As Integer
Public glPickLstMnSpecCol As Integer


'Declare Globals to represent the Core Locations DCW information
Public glCoreLocNACSCol As Integer
Public glCoreLocDispCol As Integer
Public glCoreLocDescCol As Integer
Public glCoreLocAmbCol As Integer
Public glCoreLocStRow As Integer
Public glCoreLocSheet As String


'Declare Globals to represent the Core Personnel DCW information
Public glCorePerSheet As String
Public glCorePerMenSheet As String
Public glCorePerExIdCol As Integer
Public glCorePerPosCol As Integer
Public glCorePerLNCol As Integer
Public glCorePerFNCol As Integer
Public glCorePerMNCol As Integer
Public glCorePerActiveIndCol As Integer ' The column in
the personnel DCW that holds the active indicator: 0 or 1
Public glCorePerModificationCol As Integer ' The column in
the personnel DCW that holds the modification flag: Addition,
Modification, Deletion
Public glCorePerStRow As Integer




Sub StartSetColumns()
''================================================ =======
'' Program: SetColumns
'' Desc: This routine runs upon opening the spreadsheet, it
stores the
'' the startrows and columns that are needed for the other
macros to
'' run robustly.
''================================================ =======

'Please set this flag to true if you are adding columns to the DCW.
glDesignMode = False

'In case this has been called from a worksheetChange we need to turn
off screen flicker and events
'and store the sheet to return to.
Dim ReturnSheet As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

Set ReturnSheet = ThisWorkbook.ActiveSheet


'Set the names of the sheets
glRefDataSheet = "DCW Reference Data"
glPersonnelSheet = "Personnel For Scheduling"
glSchLocSheet = "Locations for Scheduling"
glBooksSheet = "Bookshelf and Resources"
glSlotSheet = "Appointment Slots"
glTemplateSheet = "Default Schedules - Templates"
glApptTypeSheet = "Appt Types & Resources"
glPrepsSheet = "Person Preparations or Post "
glGuideSheet = "Scheduling Guidelines"
glApptSetSheet = "Appointment Type Settings"
glgroupSheet = "Group Appts"
glCABSheet = "Choose and Book"
glPickLstSheet = "Pick Lists"

'set the names of the title row columns
glRefDataStRow = 3
glPersonnelStRow = 3
glSchLocStRow = 3
glBooksStRow = 3
glSlotStRow = 3
glTemplateStRow = 3
glApptTypeStRow = 3
glPrepsStRow = 3
glGuideStRow = 3
glApptSetStRow = 3
glgroupStRow = 3
glCABStRow = 3
glPickLstStRow = 1

'Declare strings to represent the titles of these columns
'These will be used to find the correct column
Dim glRefDataVal As String
Dim glPersonnelLN As String
Dim glPersonnelFN As String
Dim glPersonnelMN As String
Dim glPersonnelRole As String
Dim glPersonnelFull As String
Dim glPersonnelModification As String
Dim glPersonnelActive As String
Dim glSchLocSchFacId As String
Dim glSchLocFacCode As String
Dim glSchLocAmbDisp As String
Dim glSchLocAmbDesc As String
Dim glBooksRes As String

--

Dave Peterson