Creating Path and FileName



I am tryin to create a filename and path using Input boxes....

Sub MySub()
Dim UserInput1 as string
Dim UserInput2 as string

UserInput1=inputbox("Enter Data")
UserInput2=inputbox("Enter Some Moredata"

activeWorkBook.SaveAs Filename:="C:\Mypath"&UserInput1&"\UserInput1 _

But Im getting "expected End of Statement" on the "\"

What am I doing wrong?


Gary''s Student

Enclose & with spaces:

Sub MySub()
Dim UserInput1 As String
Dim UserInput2 As String

UserInput1 = InputBox("Enter Data")
UserInput2 = InputBox("Enter Some Moredata")

ActiveWorkbook.SaveAs Filename:="C:\Mypath" & UserInput1 & "\UserInput1" _
& "-" & UserInput2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled,
End Sub

Rick Rothstein

As Gary''s Student said, you need spaces around the ampersands
(specifically, in front of those that follow a variable name). Why? It has
to do with VB's backward compatibility with the BASIC languages it was
derived from. In the "old days" (although there are some that still do
this), you were able to declare your variables with a postfix symbol to
indicate the data type. The ampersand symbol was used to declare a variable
as Long. So, you did this to establish a Long variable...

Dim MyVariable&

The worse thing about using the postfix symbol is that you were able to
force the declaration at the time of first use without providing a Dim
statement beforehand. So, if you did this...

X = 10 * MyVariable&

in code without first Dim'ming the variable as a Long, VB would
automatically Dim it as a Long for you the first time it came across the &
attached to the variable name. Even worse, in the old, old days of BASIC
(those prior to VB), you could actually have the same name (names were
limited to 2 characters back then) with different postfix symbols. So you
could have (again, back in the old, old days) AB%, AB!, AB$ (and I don't
remember the other available symbols any more) in one program and they would
all be different. To account for this behavior, BASIC had to allow the
postfix symbol to be used whenever the variable was used. The early VB's,
trying to maintain backward code compatibility (as much as the switch from
procedural BASIC to event driven VB would allow that is), continued to allow
the postfix symbol to be used whenever the variable was used (although, as I
vaguely remember, the ability to have the same name used with different data
types was eliminated when the "As <VarType>" declaration were created).
Anyway, the reason you need the space after the variable name, and before
the ampersand, is because VB isn't able to decide if you are applying a Long
postfix symbol to the variable name or simply trying to concatenate it.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question