Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Path and FileName
Hello,
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 _ &"-"UserInput2&".xlsm",FileFormat:=xlOpenXMLWorkbookM acroEnabled, CreateBackup:=False But Im getting "expected End of Statement" on the "\" What am I doing wrong? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Path and FileName
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, CreateBackup:=False End Sub -- Gary''s Student - gsnu200856 "Steve" wrote: Hello, 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 _ &"-"UserInput2&".xlsm",FileFormat:=xlOpenXMLWorkbookM acroEnabled, CreateBackup:=False But Im getting "expected End of Statement" on the "\" What am I doing wrong? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Path and FileName
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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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, CreateBackup:=False End Sub -- Gary''s Student - gsnu200856 "Steve" wrote: Hello, 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 _ &"-"UserInput2&".xlsm",FileFormat:=xlOpenXMLWorkbookM acroEnabled, CreateBackup:=False But Im getting "expected End of Statement" on the "\" What am I doing wrong? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Path and FileName
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. -- Rick (MVP - Excel) "Gary''s Student" wrote in message ... 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, CreateBackup:=False End Sub -- Gary''s Student - gsnu200856 "Steve" wrote: Hello, 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 _ &"-"UserInput2&".xlsm",FileFormat:=xlOpenXMLWorkbookM acroEnabled, CreateBackup:=False But Im getting "expected End of Statement" on the "\" What am I doing wrong? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
How do i display the Filename without the path? | Excel Discussion (Misc queries) | |||
Pulling in the path to a filename | Excel Worksheet Functions | |||
Filename and path | Excel Programming | |||
filename and path as constant | Excel Programming |