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

[Dave Marden]

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

 

[Gary Keramidas]

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
(e-mail address removed).

 

[Dave Marden]

I guess I didn't realize this would be a problem, they simply say ue an
email address like (e-mail address removed) 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

 

[Dave Marden]

Thanks JMB, I will try this out later tonight.

Dave Marden

 

[Gary Keramidas]

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

 

[Bob O`Bob]

I guess I didn't realize this would be a problem, they simply say ue an
email address like (e-mail address removed) 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
--

 

[NickHK]

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

 

[Dave Marden]

Thank you kindly sir, I greatly appreciate it.

Dave Marden

 

[Guest]

You are most welcome.

Thank you kindly sir, I greatly appreciate it.

Dave Marden