Conditional file SaveAs

[foamfollower]

Hi,

i have a file named 'Template_Homogeneity.xlt' that will be the
template file.

When first opened, i will have a userform appear that asks for a
number. the file will then be saved as that number.xls.

I can figure that much out, at least. However, when the user opens
the newly named file in the future, i don't want anything to happen.

i'm trying some variations like this, but without any luck.

in private sub workbook_open:

If workbooks.filename = "Template_Homogeneity.xlt" then
frm.StartLot.show
'then do the save as stuff'
goto continue
else: goto continue
end if

continue:

'continue with other at-file-open stuff'

Thanks a Bunch for any Help!

SF

 

[Frank Kabel]

Hi
not tested but try

Dim wbk_name
on error resume next
wbk_name = activeworkbook.name
on error goto 0
If wbk_name <>"" then
frm.StartLot.show
'then do the save as stuff'
goto continue
else: goto continue
end if

 

[Dave Peterson]

If the workbook has been saved at least once, it has a folder/path where it was
located.

If the workbook hasn't been saved (like book1--when you start a new workbook),
the path is blank.

So something like this should get you started.

Option Explicit
Sub workbook_open()

If Me.Path = "" Then
'the workbook has never been saved,
'so it was opened from the template
'do your form stuff
frm.StartLot.Show
Else
'do nothing--it's already been run once
End If

End Sub

 

[foamfollower]

This worked great, Thanks a lot.
Private Sub Workbook_Open()

Dim wbk_name As Variant

On Error Resume Next
wbk_name = ActiveWorkbook.Name
On Error GoTo 0
If wbk_name <> "Template_HOMOGENEITY.XLT" Then
GoTo Continue
Else: frmStartLot.Show
End If



Continue:
'other stuff

Take Care,
SF

 

[Tom Ogilvy]

If that works, then you aren't using the workbook as a true template.
(file=>New, choose this workbook as the template).

there is no need to save it as a template file then.

 

[foamfollower]

Thanks a lot for the help. Please let me ask a couple junior
questions:
What's the advantage of using an .xlt file vs a .xls (read-only). For
example,
i'd like to use the 'template' designation just because it sounds
better for the use of the file at that stage. I really don't see any
difference in using
a readonly .xls file and immediately saving as a workbook vs. using a
..xlt readonly file and immediately saving as a workbook. Is there a
diffence in that usage? Or, as Tom brought up, what are the
intentions of use/benefits of a true template file?
I had things working pretty seemlessly, with just an initial textbox
entry from the user, to save the file(well almost, but i'll get to
that) by naming the original file with the .xlt designation. This is
going to sound crazy, but
it would only work when the file was opened 'from excel'. it wouldn't
work if the file was, say, double clicked from windows explorer. if
not opened with excel, the 'file name test' would fail, even though i
know the filename is the same; it works perfectly when opened within
excel. I was just curious as to
what's going on there? when i switched the original file back to a
..xls file, opens from anywhere and works fine, as would be expected.

the other question i have is; Can a new folder also be created at the
file SaveAs point? I have tried the following, using the same single
textbox entry
from the user, but it won't create the new folder. It only works if i
'pre-create' the folder ahead of time.

Private Sub cmdOK_Click()
On Error GoTo errorhandler
Dim req As Variant
Dim req2 As Variant

Cancelled = False

Me.Hide
Application.ScreenUpdating = True

req = txtStartLotNumber.Value
req2 = req & " H"


ActiveWorkbook.SaveAs Filename:= _
"c:\Testing\" & req & "\" & req2, FileFormat _
'rest of code

Thanks Again!

I will try Dave's method now. I'll post back later.

C ya

 

[Dave Peterson]

I don't see too much of a difference between a readonly .xls and a .xlt file.

A template is nice when users start it via File|new, but if you're doing it in
code, I don't think you could notice the difference.

The .xls file will have a .path and the workbook based on the .xlt won't--but
that doesn't sound important to your situtation (well, to me anyway).

dim newwkbk as workbook
set newwkbk = workbooks.add(template:="C:\myworkbook.xls")
or
set newwkbk = workbooks.add(template:="C:\mytemplate.xlt")

Looks pretty much the same to me in the long run.



Take a look at the mkdir in vba's help:

on error resume next
mkdir c:\testit1
mkdir c:\testit1\testit2
mkdir c:\testit1\testit2\testit3
on error goto 0

If the folder existed, then the "on error resume next" would cause your code to
ignore the error. If it/they didn't exist, it/they would be created.

Sometimes when your filename contains spaces, excel/windows gets confused. You
can sometimes un-confuse them with:

Tools|Options|General|Ignore other applications (uncheck it)

--- or ---

Close Excel and
Windows Start Button|Run
excel /unregserver
then
Windows Start Button|Run
excel /regserver

The /unregserver & /regserver stuff resets the windows registry to excel's
factory defaults.