Reputation: 19
I am a VBA novice trying to create a couple of macros that will feature the use of the inputbox function.
I found a piece of code online that works, but I am trying to modify it so sPath is instead the string entered into an inputbox
Dim sPath As String
Dim sName As String
Dim wkbk As Workbook
Dim sName1 As String
**sPath** = "C:\Data\Test\" '<== change to your path
On Error GoTo Errhandler
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
sName = Dir(sPath & "*.xls")
Application.EnableEvents = False
Do While sName <> ""
sName1 = Replace(LCase(sName), ".xls", "")
Application.DisplayAlerts = False
Set wkbk = Workbooks.Open(sPath & sName)
wkbk.SaveAs Filename:=sPath & sName1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
wkbk.Close SaveChanges:=False
Application.DisplayAlerts = True
sName = Dir()
Loop
'Kill sPath & "*.xls"
Errhandler:
Application.EnableEvents = True
End Sub
To modify this macro i simply declared a variable called filepath and set the inputbox result to filepath.
Dim filepath as string
filepath = inputbox("Please enter the file path")
If filepath = "" Then
msgbox("No file entered, macro will terminate")
Exit sub
End if
filepath = sPath
The rest of the above code goes here.
When i run this the macro terminates with the Errhandler.
I am assuming the result stored in the inputbox is somehow incompatible with the rest of the macro.
Upvotes: 1
Views: 68