4am_Grind
4am_Grind

Reputation: 19

Inputbox result stored as string does not seem to be generating the expected result

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

Answers (0)

Related Questions