VBA, and SaveAs Function

[Amber_D_Laws]

Hello,

I am new to the forum, and new to VBA. It is nice to have a place full
of such knowledgeable people!

So, right to it...

I have been creating a quote module for the project managers at my lab,
and none of them are very computer literate, and do not care to become
so. Needless to say making the module user friendly for them has been a
challenge. VBA so far has helped me by leaps and bounds, but I have hit
a snag.

I want them to save each new quote as a unique file once the quote
number has been updated and saved to the master file.
(pardon if my terminology is a little off, as I said, I am not a
professional programmer)

The code I have so far has been running great. It is for a command
button, that increases the quote number by one, and then saves the
file. This afternoon I started trying to figure out how to get it to
save to a unique file, and I am up against a wall. I have looked in my
VBA book, but they don't even mention "SaveAs", and looking online has
been like pulling teeth. I can't find consistant answers that tell me
how VBA is reading the information, and what I'm not giving it.

This is the code so far:

Private Sub CommandButton1_Click()
'Declares Variables of the file name for "SaveAs" function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String

'Defines the variable names from Quote Form
QNum = "W10"
CNam = "N19"
CrDt = Format(Now, "mmddyy")
VNum = "AA10"

'Changes the Quote Number to Increase by 1
Range("W10").Value = Range("W10").Value + 1
Range("W10").Copy
Range("W10").PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Saves the New Quote Number to the Template
ActiveWorkbook.Save
'Saves the New Quote as a unique file
ActiveWorkbook.SaveAs "X:\_FEE SCHEDULE & QUOTE MODULE\" &
Str(QNum) & Str(CNam) & (CrDt) & " Ver" & Str(VNum)
End Sub

The debugger is coming up with a run time error, type mismatch.

Someone PLEASE help!

Thank you in advance.

 

[bpeltzer]

The type mismatch comes about in the Str functions in your SaveAs line;
you're passing a string to a function that expects a number. My guess is
that the variables QNum, QNam and VNum are intended to take on the contents
of the cells rather than the cell addresses as you've got them assigned now.
For instance, instead of QNum = "W10", use QNum = Range("W10").Value.
Likewise with QNam and VNum. See if that gets you past the type mismatch.
--Bruce

 

[Amber_D_Laws]

Thanks! I never expected such a quick response.
I have a little less than an hour an a half to finish this up before we
all go home for new years.
I will let you know how it goes.
Your explination was very clear on something that has had me puzzled
for a while.
Thanks again!

 

[Amber_D_Laws]

Ok. That took care of the run time / type mismatch, but now I have a
slightly different issue.

The first varible is the quote number, which if formated in the cell to
appear as "MOB0000062", of course the value of the cell is only 62.

How do I get the file name to return the text and not the value?

Sorry to be so picky about it.

Thanks for finding my problem for the type mismatch, that alone was a
lifesaver!

 

[Dave Peterson]

Instead of:
QNum = Range("W10").Value
you could use:
QNum = Range("W10").Text

..text will return the formatted number.

Or if W10 actually contained 62, you could do the formatting yourself in code:

QNum = "MOB" & format(range("w10").value, "0000000")

===
Did I use the right variable names? I was kind of confused.

 

[Amber_D_Laws]

Yes, you did use the right variable names, and your solution worked.

However, I just noticed it is saving the file with the number prior t
the change. Any ideas how to fix?

Thanks again! and have a Happy New Year!

Dave Patterson wrote:

Instead of:
QNum = Range("W10").Value
you could use:
QNum = Range("W10").Text

..text will return the formatted number.

Or if W10 actually contained 62, you could do the formatting yoursel
in code:

QNum = "MOB" & format(range("w10").value, "0000000")

===
Did I use the right variable names? I was kind of confused.

Ok. That took care of the run time / type mismatch, but now I have a
slightly different issue.

The first varible is the quote number, which if formated in the cel to
appear as "MOB0000062", of course the value of the cell is only 62.

How do I get the file name to return the text and not the value?

Sorry to be so picky about it.

Thanks for finding my problem for the type mismatch, that alone wa a

http://www.excelforum.com/showthread...hreadid=497045

 

[Dave Peterson]

You could add one to the cell first, then pick it up:

with range("w10")
.value = .value + 1
QNum = .Text
end with

or you could add one after you pick it up:

QNum = "MOB" & format(range("w10").value + 1, "0000000")

 

[Amber_D_Laws]

Thank you so much Dave, I really don't think I would have ever gotten
all of this together without your help.

Thanks to your last comments I had a lightbulb go off, and eventhough I
did not use your solution directly, it mad me realize something I was
missing.
Ultimately I ended up delaying the definition of the QNum varible until
after the template file was saved. Hey Presto Bango! It worked like a
charm, and now the file saves exactly as it should everytime.

Here is the code I ended up using:


Code:
--------------------
Option Explicit

Private Sub CommandButton1_Click()
'
'Declares Variables of the file name for saved as function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String
'
'Defines the variable names from Quote Form
CNam = Range("N19").Text
CrDt = Format(Now, "mmddyy")
VNum = Range("AA10").Text
'
'Changes the Quote Number to Increase by 1
Range("W10").Value = Range("W10").Value + 1
Range("W10").Copy
Range("W10").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'
'Saves the New Quote in the template
ActiveWorkbook.SaveAs "X:\_FEE SCHEDULE & QUOTE MODULE\Quote Module 1.1"
'
'Defines the QNum varible to the new quote number after the template is saved
'to insure they are saved with the same number
QNum = Range("W10").Text
'
'Saves the New Quote as a unique file
ActiveWorkbook.SaveAs "X:\_FEE SCHEDULE & QUOTE MODULE\" & QNum & "-" & CNam & "-" & CrDt & "-" & " Ver" & VNum
End Sub
--------------------


So, here is the last problem. Is there a way to make it so that a user
can not reuse the button. (ie Once the file has been saved with a
unique name, it can not have the quote number changed.) Any ideas? Is
this even possible?

Thanks again in advance, I hope you had a glorious new year!
Amber D.

You could add one to the cell first, then pick it up:

with range("w10")
.value = .value + 1
QNum = .Text
end with

or you could add one after you pick it up:

QNum = "MOB" & format(range("w10").value + 1, "0000000")

 

[Dave Peterson]

You could build that string that refers to the file name:

dim myFileName as string
dim testStr as string

myfilename = "X:\_FEE SCHEDULE & QUOTE MODULE\" & QNum & "-" & CNam & "-" _
& CrDt & "-" & " Ver" & VNum

'myfilename = myfilename & ".xls" '?????

teststr = ""
on error resume next
teststr = dir(myfilename)
on error goto 0

if teststr <> "" then
msgbox "already exists
else
'do the save
end if

 

[Amber_D_Laws]

That did the trick. I can not say how much I appreciate all your effor
on this. Everything else I have done was a breeze. This one littl
button was giving me fits, and with your help the nightmare is over!

Thank you, thank you, a thousand times, thank you!
Amber D.
:

 

[Dave Peterson]

Glad you got it working!

That did the trick. I can not say how much I appreciate all your effort
on this. Everything else I have done was a breeze. This one little
button was giving me fits, and with your help the nightmare is over!

Thank you, thank you, a thousand times, thank you!
Amber D.