I need to distribute copies of an Excel workbook to multiple users. On
occassion I may need to update source data for listboxes in their workbook,
so I created a "source data.xls" that I'm putting in a shared directory. I'm
currently trying to write code to check 3 values in their local copy against
"source data.xls", and if any are different, then copy the information from
the related page back to their local workbook to update their source
information.

I'm having some syntax issues when opening my source workbook, and wasn't
able to find anything on google, so here goes;
if you have any suggestions on what I need to do differently to open or
reference the source.xls I'd appreciate any input. Also, I may not be
looking under the right help topics, so I'd also welcome any keywords I
should be using if this information is in the help file somewhere.

Thanks,
Keith

Private Sub GetUpdatedData()
Dim SourceWB As Workbook

'On Error Resume Next
MyVPS = Sheet1.Range("G1").Value
MyVRsons = Sheet1.Range("G2").Value
MyVArds = Sheet1.Range("G3").Value

Set SourceWB = Workbooks.Open("\\Server05\workfiles\Source.xls", False,
True)

CheckVPS = SourceWB.Sheet1.Range("A1").Value 'errors out here
CheckVRsons = SourceWB.Sheet2.Range("A1").Value
CheckVArds = SourceWB.Sheet3.Range("A1").Value

'then use my code to compare them, and eventually to grab any updated data
and copy it over

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

You can't refer to the codename of the worksheet that way.

If this is a workbook where the worksheets would never change name, then I'd
just use the worksheet name:

CheckVPS = SourceWB.worksheets("Sheet1").Range("A1").Value

But if the worksheet name (the one visible to the user on the tab in excel) can
change, you could use something like this to get the worksheet:

Option Explicit
Sub testme()

Dim SourceWb As Workbook
Dim wks As Worksheet

Set SourceWb = Workbooks("Book7.xls")

With SourceWb
Set wks = .Worksheets(.VBProject.VBComponents("Sheet1") _
.Properties("Name").Value)
End With

MsgBox wks.Range("a1").Value

End Sub

But this method can fail if the workbook's project (in the VBE) is protected.

Option Explicit
Sub testme2()

Dim SourceWb As Workbook
Dim wks As Worksheet

Set SourceWb = Workbooks("Book1.xls")

Set wks = Nothing
With SourceWb
For Each wks In .Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Exit For
End If
Next wks
End With

If wks Is Nothing Then
'no match
Else
MsgBox wks.Range("a1").Value
End If

End Sub

KR wrote:

I need to distribute copies of an Excel workbook to multiple users. On
occassion I may need to update source data for listboxes in their workbook,
so I created a "source data.xls" that I'm putting in a shared directory. I'm
currently trying to write code to check 3 values in their local copy against
"source data.xls", and if any are different, then copy the information from
the related page back to their local workbook to update their source
information.

I'm having some syntax issues when opening my source workbook, and wasn't
able to find anything on google, so here goes;
if you have any suggestions on what I need to do differently to open or
reference the source.xls I'd appreciate any input. Also, I may not be
looking under the right help topics, so I'd also welcome any keywords I
should be using if this information is in the help file somewhere.

Thanks,
Keith

Private Sub GetUpdatedData()
Dim SourceWB As Workbook

'On Error Resume Next
MyVPS = Sheet1.Range("G1").Value
MyVRsons = Sheet1.Range("G2").Value
MyVArds = Sheet1.Range("G3").Value

Set SourceWB = Workbooks.Open("\\Server05\workfiles\Source.xls", False,
True)

CheckVPS = SourceWB.Sheet1.Range("A1").Value 'errors out here
CheckVRsons = SourceWB.Sheet2.Range("A1").Value
CheckVArds = SourceWB.Sheet3.Range("A1").Value

'then use my code to compare them, and eventually to grab any updated data
and copy it over

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

--

Dave Peterson

In that case, I think I'll make sure those worksheet names will never change
;-)
Thanks!
Keith

"Dave Peterson" wrote in message
...
You can't refer to the codename of the worksheet that way.

If this is a workbook where the worksheets would never change name, then
I'd
just use the worksheet name:

CheckVPS = SourceWB.worksheets("Sheet1").Range("A1").Value

But if the worksheet name (the one visible to the user on the tab in
excel) can
change, you could use something like this to get the worksheet:

Option Explicit
Sub testme()

Dim SourceWb As Workbook
Dim wks As Worksheet

Set SourceWb = Workbooks("Book7.xls")

With SourceWb
Set wks = .Worksheets(.VBProject.VBComponents("Sheet1") _
.Properties("Name").Value)
End With

MsgBox wks.Range("a1").Value

End Sub

But this method can fail if the workbook's project (in the VBE) is
protected.

Option Explicit
Sub testme2()

Dim SourceWb As Workbook
Dim wks As Worksheet

Set SourceWb = Workbooks("Book1.xls")

Set wks = Nothing
With SourceWb
For Each wks In .Worksheets
If LCase(wks.CodeName) = "sheet1" Then
Exit For
End If
Next wks
End With

If wks Is Nothing Then
'no match
Else
MsgBox wks.Range("a1").Value
End If

End Sub

KR wrote:

I need to distribute copies of an Excel workbook to multiple users. On
occassion I may need to update source data for listboxes in their
workbook,
so I created a "source data.xls" that I'm putting in a shared directory.
I'm
currently trying to write code to check 3 values in their local copy
against
"source data.xls", and if any are different, then copy the information
from
the related page back to their local workbook to update their source
information.

I'm having some syntax issues when opening my source workbook, and
wasn't
able to find anything on google, so here goes;
if you have any suggestions on what I need to do differently to open or
reference the source.xls I'd appreciate any input. Also, I may not be
looking under the right help topics, so I'd also welcome any keywords I
should be using if this information is in the help file somewhere.

Thanks,
Keith

Private Sub GetUpdatedData()
Dim SourceWB As Workbook

'On Error Resume Next
MyVPS = Sheet1.Range("G1").Value
MyVRsons = Sheet1.Range("G2").Value
MyVArds = Sheet1.Range("G3").Value

Set SourceWB = Workbooks.Open("\\Server05\workfiles\Source.xls",
False,
True)

CheckVPS = SourceWB.Sheet1.Range("A1").Value 'errors out here
CheckVRsons = SourceWB.Sheet2.Range("A1").Value
CheckVArds = SourceWB.Sheet3.Range("A1").Value

'then use my code to compare them, and eventually to grab any updated
data
and copy it over

--
The enclosed questions or comments are entirely mine and don't represent
the
thoughts, views, or policy of my employer. Any errors or omissions are
my
own.

--

Dave Peterson