Oddity when opening templates from code

[Ian]

I have some code to open 2 templates and copy some data from one to the
other. The problem I have is that one opens (template-x.xlt) whereas the
other creates a new xls based on the template (template-y1.xls).

I've posted the code segments below:

---
Sub CheckVersion(sTPPath, sUpdRoot, sVer)
With Application
.EnableEvents = False
.Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0
---
sTPPath is C:\Documents and Settings\ianc\My Documents\Template Prep
sUpdRoot is II checklist
This one opens the template file.

---
Sub OpenUpdate(sCurPath, sUpdRoot, sUpdFile)
With Application
.EnableEvents = False
sUpdFile = Dir(sCurPath & "\" & sUpdRoot & "*.xlt")
.Workbooks.Open Filename:= _
sCurPath & "\" & sUpdFile, _
UpdateLinks:=0 ' ===== Retrieve filename from sUpdFile =====
---
sCurPath is C:\Documents and Settings\...............\Remote update (long
path cut)
sUpdFile is II checklist 6.0.2 update.xlt
This one opens an xls file.

I've tried hardcoding the path & filename for the second one. I've also
tried removing the code for the first one in case it's something to do with
it being the second one open, but it still does the same.

This is driving me nuts!

Any ideas?

Ian

 

[dan dungan]

I'm not sure what you want, specifically.

It looks as though you have two procedures, one to template x, and one
to open template y.

Which sub is for which template?
How have you dimensioned the variables?
I don't see the end sub command in either procedure. Please show the
rest of the code.
How do you copy some data from one to the other?
What is the data source and destination?

Dan

 

[Ian]

Hi Dan

Sorry, I might have confused things by mentioning the purpose of the WHOLE
code. Perhaps I should have just mentioned the relevant parts and the
problem I have.

The problem is not with copying data, it is with opening the 2 templates.
One opens as a template and the other opens as a file based on the template.
I can't figure out why the second piece of code opens a file instead of the
template.

The code after the bits I've posted are irrelevant to the question.
Effectively you could just End Sub after each section of code I've posted.

I have to admit I hadn't dimensioned all the variables, but now have done.
It doesn't make any difference.

I've posted the full subs below, but it's only the first part of each (up to
the Workbooks.Open line) that's relevant.
Sub CheckVersion(sTPPath, sUpdRoot, sVer)
With Application
.EnableEvents = False
.Workbooks.Open Filename:= _
sTPPath & "\" & sUpdRoot & ".xlt", _
UpdateLinks:=0
sStartCell = ActiveCell.Address
On Error GoTo noSD
.Cells.Find(What:="SD", After:=.ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
lLoc = InStrRev(.ActiveCell.Value, "v")
Debug.Print "lLoc = "; lLoc
sVer = Right(.ActiveCell.Value, Len(.ActiveCell.Value) - lLoc)
If Left(sVer, 1) = "6" Then
sVer = "Current version is " & sVer
Else
sVer = "Failure - Unable to update from this version"
End If
.Visible = True
.EnableEvents = True
End With
GoTo Subend
noSD:
sVer = "Failure - Unable to update from this version"
Subend:
End Sub

Sub OpenUpdate(sCurPath, sUpdRoot, sUpdFile)
With Application
.EnableEvents = False
.Workbooks.Open Filename:= _
sCurPath & "\" & sUpdFile, _
UpdateLinks:=0
.Visible = True
.EnableEvents = True
End With
End Sub

Ian