I can't figure out the correct syntax for referencing a workbook.
 

I have decided to incorporate this change into all upcoming versions of
my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to use
the same idea but for some reason it always gives me a "runtime error 9"
"Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously going
to be already running. Any other way of referencing this or am I missing
something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " & wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbook.
 

this is a personal preference, but, if you're not an employee of microsoft, i
really wish you didn't use @microsoft.com in your email address. use
.


--


Gary


"Dave Marden" wrote in message
...
I have decided to incorporate this change into all upcoming versions of my
program. This has brought up a new problem. "Set bk = Workbooks(wbName)"
works fine for referencing the filename so I tried to use the same idea but
for some reason it always gives me a "runtime error 9" "Subscript out of
range" error. I simply used bk2 for doing this. I figured I didn't need the
error checking since this file is obviously going to be already running. Any
other way of referencing this or am I missing something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " & wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbook.
 

I guess I didn't realize this would be a problem, they simply say ue an
email address like so that is exactly what I used. I
have posted on here several times in the past and your the first person I've
ever had say that. I guess I would like to know waht others think about it.
I assume that microsoft doesn't actually use that email address.

Dave Marden

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this is a personal preference, but, if you're not an employee of
microsoft, i
really wish you didn't use @microsoft.com in your email address. use
.


--


Gary


"Dave Marden" wrote in message
...
I have decided to incorporate this change into all upcoming versions
of my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to
use the same idea but for some reason it always gives me a "runtime error
9" "Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously
going to be already running. Any other way of referencing this or am I
missing something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " &
wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName")
=
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbook.
 

Thanks JMB, I will try this out later tonight.

Dave Marden


"JMB" wrote in message
...
Instead of
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)

I think you just need
set bk2 = ActiveWorkbook

However, it really is not necessary to set a variable for the workbook
that
is running the code. You can always reference the workbook containing the
code that is running with ThisWorkbook. Also, if you need to refer to the
same object many times, you can use a With statement

With Thisworkbook
.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")

'etc

End With



"Dave Marden" wrote:

I have decided to incorporate this change into all upcoming versions
of
my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to
use
the same idea but for some reason it always gives me a "runtime error 9"
"Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously
going
to be already running. Any other way of referencing this or am I missing
something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " &
wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName")
=
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another
Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbook.
 

but a lot of us that use newsgroups, especially those of us in ms betas, use
rules to distinguish posts from actual microsoft employees.

--


Gary


"Dave Marden" wrote in message
...
I guess I didn't realize this would be a problem, they simply say ue an email
address like so that is exactly what I used. I have
posted on here several times in the past and your the first person I've ever
had say that. I guess I would like to know waht others think about it. I
assume that microsoft doesn't actually use that email address.

Dave Marden

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this is a personal preference, but, if you're not an employee of microsoft, i
really wish you didn't use @microsoft.com in your email address. use
.


--


Gary


"Dave Marden" wrote in message
...
I have decided to incorporate this change into all upcoming versions of
my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to use
the same idea but for some reason it always gives me a "runtime error 9"
"Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously going
to be already running. Any other way of referencing this or am I missing
something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " & wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbook.
 

Dave Marden wrote:
I guess I didn't realize this would be a problem, they simply say ue an
email address like so that is exactly what I used. I
have posted on here several times in the past and your the first person I've
ever had say that. I guess I would like to know waht others think about it.
I assume that microsoft doesn't actually use that email address.


You have no business assuming *anything* about *any* email address other
than those for which you've been granted authority.



Bob
--

I can't figure out the correct syntax for referencing a workbook.
 

I was under the impression that "invalid.com" was designated as signifying a
deliberately bogus name, useful for these purposes.
I now see that there is a URL "invalid.com", so it appears I was wrong all
these years.

NickHK

"Bob O`Bob" wrote in message
...
Dave Marden wrote:
I guess I didn't realize this would be a problem, they simply say ue an
email address like so that is exactly what I used.
I
have posted on here several times in the past and your the first person
I've
ever had say that. I guess I would like to know waht others think about
it.
I assume that microsoft doesn't actually use that email address.


You have no business assuming *anything* about *any* email address other
than those for which you've been granted authority.



Bob
--

I can't figure out the correct syntax for referencing a workbook.
 

Thank you kindly sir, I greatly appreciate it.

Dave Marden

"Dave Marden" wrote in message
...
Thanks JMB, I will try this out later tonight.

Dave Marden


"JMB" wrote in message
...
Instead of
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)

I think you just need
set bk2 = ActiveWorkbook

However, it really is not necessary to set a variable for the workbook
that
is running the code. You can always reference the workbook containing
the
code that is running with ThisWorkbook. Also, if you need to refer to
the
same object many times, you can use a With statement

With Thisworkbook
.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")

'etc

End With



"Dave Marden" wrote:

I have decided to incorporate this change into all upcoming versions
of
my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to
use
the same idea but for some reason it always gives me a "runtime error 9"
"Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously
going
to be already running. Any other way of referencing this or am I
missing
something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " &
wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score
Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another
Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden

I can't figure out the correct syntax for referencing a workbo
 

You are most welcome.

"Dave Marden" wrote:

Thank you kindly sir, I greatly appreciate it.

Dave Marden

"Dave Marden" wrote in message
...
Thanks JMB, I will try this out later tonight.

Dave Marden


"JMB" wrote in message
...
Instead of
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)

I think you just need
set bk2 = ActiveWorkbook

However, it really is not necessary to set a variable for the workbook
that
is running the code. You can always reference the workbook containing
the
code that is running with ThisWorkbook. Also, if you need to refer to
the
same object many times, you can use a With statement

With Thisworkbook
.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
.Worksheets("League's Score Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")

'etc

End With



"Dave Marden" wrote:

I have decided to incorporate this change into all upcoming versions
of
my program. This has brought up a new problem. "Set bk =
Workbooks(wbName)" works fine for referencing the filename so I tried to
use
the same idea but for some reason it always gives me a "runtime error 9"
"Subscript out of range" error. I simply used bk2 for doing this. I
figured I didn't need the error checking since this file is obviously
going
to be already running. Any other way of referencing this or am I
missing
something here?

Public Sub cmdPullDataFromOldFile_Click()
Dim wbName As String, bk As Workbook
Dim bk2 As Workbook
wbName = Application.GetOpenFilename
wbName2 = ActiveWorkbook.FullName
set bk2 = Workbooks(wbName2)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Are you sure you want to copy all user input data from " &
wbName
& " to this file?" ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Confirm Data Update" ' Define title.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
Dim bClosed As Boolean
On Error Resume Next
Set bk = Workbooks(wbName)
On Error GoTo 0
If bk Is Nothing Then
bClosed = True
Set bk = Workbooks.Open(wbName)
End If
'Getting Data From Old Version
'Handicap
bk2.Worksheets("Competitors A-Z").Range("D29") =
bk.Worksheets("Competitors A-Z").Range("D29")
'Archery League Name
bk2.Worksheets("League's Score
Board").Range("ArcheryLeagueName") =
bk.Worksheets("League's Score Board").Range("ArcheryLeagueName")
'Max Make-Up Scores
bk2.Worksheets("Competitors A-Z").Range("MaxMakeupScores") =
bk.Worksheets("Competitors A-Z").Range("MaxMakeupScores")
'Names
bk2.Worksheets("Competitors A-Z").Range("X4:X27").Value =
bk.Worksheets("Competitors A-Z").Range("X4:X27").Value
'Scores, X-Counts, Make-Up, and Blind Data
bk2.Worksheets("Competitors A-Z").Range("AB4:BK27").Value =
bk.Worksheets("Competitors A-Z").Range("AB4:BK27").Value
If bClosed Then bk.Close Savechanges:=False
Else ' User chose No.
MsgBox "You Have Chosen Not To Update This File With Another
Files
Data"
End If
End Sub

Any help appreciated,
Dave Marden