macro works in .xlt but not .xls

[BrianG]

I've created a purchase order template (purchord.xlt) whose number
comes from a sheet in a seperate workbook (newponum.xls). The sole
purpose of this sheet is to increment the number. When I test the
macro while the template is open it works fine but when I start Excel
with the template, the macro fails. The problem is that the
newponum.xls is being opened as newponum2. Here's the portion of my
code that creates the purchase order number and prepares for data
entry (don't laugh, I admit to not being a VB programmer):

Dim wpath As String ' wPath = Working Path
Dim tpath As String ' tPath = Temporary file Path
Dim tFilename As String ' tFilename = Temporary Filename
wpath = "c:\temp\po project" 'Set working path
tpath = Environ("temp") 'Set temp path
If tpath = "" Then
tpath = Environ("tmp")
End If

Dim TB As Worksheet
PO = MsgBox("Start a new Purchase Order?", vbYesNo, "New Purchase
Order?")
If PO = vbNo Then GoTo C 'Close worksheet and exit on cancel

tFilename = Range("AB1") & "_tmp"
ChDrive tpath 'Change drive to location of temp folder
ChDir tpath 'Change directory to path of temp folder

ThisWorkbook.SaveAs filename:=tFilename, FileFormat:=xlNormal

ChDir wpath 'Change directory to working path

Sheets(1).ScrollArea = "b1:ac135" 'Sets the scroll area

Set TB = ThisWorkbook.Worksheets(1)

On Error GoTo E
Application.ScreenUpdating = False
Workbooks.Open "nextponum.xls"
Range("A1") = Range("A1") + 1
TB.Range("AB1") = "C" & Range("A1")
ActiveWorkbook.Close SaveChanges:=True
ThisWorkbook.Activate
TB.Select
TB.Range("AB1") = "C" & NewNum
Application.ScreenUpdating = True
Exit Sub

E:
MsgBox "Excel could not assign a new number to this purchase
order." & Chr(13) & _
"The NEXTPONUM.XLS file could not be found." & Chr(13) & _
"Contact your network admin for assistance.", , "Error - Procedure
Failed!"
C:
ThisWorkbook.Close SaveChanges:=False

End Sub

 

[Tom Ogilvy]

You said the template is purchord.xlt - how is newponum.xls being opened as
newponum2.xls. Have you marked is as an addin as well -

Open the newponum.xls from file open and then in the vbe, look at the
properties for the workbook. Is the IsAddin property set to True. If so,
set it to false.

 

[BrianG]

Regarding: "You said the template is purchord.xlt - how is
newponum.xls being opened as newponum2.xls. Have you marked is as an
addin as well"

I wish I new how newponum.xls was being opened as newponum2. When the
macro opens newponum.xls, the new window is titled "newponum2" and the
Save attempts to save it as "newponum2". Notice that it is "newponum2"
not "newponum2.xls" (no .xls).
If I File->Open newponum.xls, the window title appears as it should,
"newponum.xls".

I opened newponum.xls as you suggested and checked properties in the
vbe. The only properties I could find were Sheet1 and No IsAddin
property was listed.

BrianG

 

[J.E. McGimpsey]

I suspect you saved your nextponum.xls file as a template - XL will
open it as a template even with the xls extension.

I'd suggest not using an XL file at all, just use a text file - it's
fast and very small and doesn't require switching active workbooks:

On Error GoTo E
Open "nextponum.txt" For Random Access Read Write _
Lock Read Write As #1
Get #1, 1, newNum
newNum = newNum + 1
Put #1, 1, newNum
Close #1
With ThisWorkbook.Worksheets(1)
.Range("AB1") = "C" & newNum
.ScrollArea = "b1:ac135"
End With
Exit Sub

 

[Tom Ogilvy]

You have to look at the properties the ThisWorkbook object in the project
explorer.

 

[BrianG]

Found it...IsAddin is set to False!

BrianG

 

[Tom Ogilvy]

Sorry, sent you on a wild goose chase. Somehow I meant to say template and
said addin instead.

Everything you describe would say the file is a template eventhough you
named it as an xls. I would open it with file=>Open, then do a saveas -
making sure you are saving it as a normal workbook. then I would delete the
original and rename the copy.

Sorry for the misdirection. Were in the middle of the hurricane here, so
maybe I was distracted <g>.